Tech Problem Aggregator

Microsoft Access - Appending Table To Memo Field

Q: Microsoft Access - Appending Table To Memo Field

I am currently using Microsoft Access 2002. I wanted to know if there was a way for me to append an entire column from a table into a single memo field.

I am trying to make a distribution list memo field in an access table. I wanted to grab an "email address" column to upload into this memo field. I could reference the memo field later on when I send emails from Access.

Is there any way to do this?

A: Microsoft Access - Appending Table To Memo Field

First, you don't really give enough info: is this a one time append or is this for continuing functionality in a form?

If it is the latter, what you would probably need to to is create a list box that populates all the email addresses in your database. Then, based on which items in the box you chose, you could loop through, using vba, and create a string value. Once you have the string with the email addresses, you would commit that to your memo field.

Hope this helps,


2 more replies
Answer Match 92.4%

I have an access main form that contains a subform. The subform is setup to display in datasheet view.

On the subform there is a memo type field. Is there a way to have the memo field to word wrap?

A:Solved: Access Subform - Memo Field

I don't think you can in Datasheet view. You could try setting the Can Grow property to yes.

2 more replies
Answer Match 91.56%

Can someone explain how you can put tabs in a text that you are putting in a memo field? I would like to align the text in different columns so it appears that way in a merged document.
Help is much appreciated,

A:Access 2010: putting tabs in memo field

16 more replies
Answer Match 90.72%

I'm working on a database where a user can perform searches by dates and when they click my command button it saves the form's data into a table then runs an SQL sequence which creates a table. I need that table to delete the results and append when the next user performs a search. My code is as follows

'CurrentDb.Execute "Update tblFilter set InspectStart=#"
cboStartDate.Value = Null
cboEndDate.Value = Null
RunCommand acCmdSaveRecord
sql = "Select * into tblInspectionDateFilter from qryInspectionDateFilter"
DoCmd.RunSQL sql
'DoCmd.OpenQuery qryInspectionDateFilter, acNormal, acEdit'
Call MakeTables
DoCmd.OpenReport "MasterReport", acViewPreview
I am not sure how to go about it. Need help please. The qryInspectionDateFilter is the one I need to delete and append to. Thanks

A:Deleting a table and appending its records before a query in Access 2007

ladyvj, can I ask why you do it this way?
Why create a table to create another table?

To answer your actual question you just need to create a "Delete Query" for the table that you want to clear of records and run that at the appropriate time in your current VBA code

1 more replies
Answer Match 90.3%

And yes, I did set the property to 'can grow' and 'can shrink'. I can't figure what the problem is. Even if I stretch the field out huge, it still is cut off when I view the report. And in one case, the same field prints fully when it is used on one report but on another similar report the same field is cut off. I can't see any differences between the two report settings.

A:Access 2007 Memo field not fully showing on report

8 more replies
Answer Match 89.88%

I have a table titled time_slips used to track the amount of time an attorney spends with a member. In the time_slips table there is a field titled reference_id which is a combo box pulling it's displayed information from another table titled references. Also in the time_slips table is a field named details which is native to this table.

My question is, can I have the details field auto-populate "no charge" when a certain reference id is selected (i.e. 300)?

Thanks for any help you can provide.

A:Solved: Access Auto-Populate a Field using a field from another table

15 more replies
Answer Match 89.88%

I created a new database that contains several different tables with relationships, auto number fields, and all the fun stuff that goes with those things. However, my workplace had been inputting data into an extremely large table for years. This large table contains all the data that I need to be in my new database. I was wondering if there is a way to take the contents of the one large table and append it to the multiple tables that I created in the new database. To further complicate matters, I changed some of the formatting and/or names of fields. Any thoughts or suggestions for the most effective and efficient method to complete this process would be greatly appreciated.

Just a note: I have this week and next week to complete the design of the database and get all these people input into the database (a daunting task, I know). So, manually adding the data is not really a feasible option.

A:Solved: Appending data from one large table into several smaller tables in Access

16 more replies
Answer Match 80.22%

Ok, my problem is...i have a table with 2 field diferent name

ID 1
unit_price 238.56
unit_pricelast ......

It is possible to update "unit_pricelast" with value of "unit_price" but using same ID?

ID 1
unit_price 238.56 (later i must change value of this field cuz i have items with last month price and price with actual)
unit_pricelast 238.56

ty for fast respond

More replies
Answer Match 79.38%


I have a table which is quite large, and is updated every week, so far no issues, but this week I made a mistake when entering the week number and had to delete all the entries from the database , this was a endless task as filtering the week took some good time... question is:

Is there any way that I can "update" a field on the table with the new field, all of them are teh same value, in this particular case is a week number.

Appreciate the help.

Thanks in advance,

A:Access 2010 - Update field in a table

7 more replies
Answer Match 79.38%

Please help

I have 2 tables:-

1 Master Input Table.
1 Job Lookup table - (contains Job Number and Name)

When in-putting job details into the Master Input table i would like to enter the job number only and have the job name appear automatically without running a query, much the same as a 'vLookup' command in excel.

Is this possible?

Can anyone help?

A:Auto Fill In MS Access Table Field

Yes it is, the easiest way is to have the Job Lookup Table as a Sub form on the master form (Master Input Table) linked via the Job Number.
Or you could select it from a combo box, (no typing).

1 more replies
Answer Match 79.38%


I would like to add a field to the end of my table called Timestamp. I want this field to be populated with the current time if a record is updated. I know I can set the default to =Now(), but that only sets the time and date the record is created. If I go in an update this record, that time or date does not change. Is there a way to inset a time and date that would automatically change? If not, is there an easier way then to just type in the time and date manually? Any suggestions would be appreciated. Thanks!


A:Adding a timestamp field in a table in Access

Have you tried =Date() ? That is what is used in the contact management database that comes with Access 2000.

1 more replies
Answer Match 79.38%

I am using a query in Access to clean up some unneeded text from a table, then create a new table with the cleaned up text. In addition, I need to add a column to this new table that is boolean format defaulting to a "no" value. I have successfully created this query except for the new boolean column. I can get the new column to default to a value, either no or 0, but it is in text format. If anyone knows how to do this I would appreciate the help.

A:Access-Adding a field to a table with a query

This query will do it:


3 more replies
Answer Match 79.38%

I'm relatively new to access, only been working with it on and off for the past few months or so and have run into a little problem now.

Basically, a date is entered in a field on a table called "Date of Entry", and another value is entered in a different field called "Serving Period". Now I have another field called "Expiry Date", and basically it's the value (date) of "Serving Period" added to the date in "Enter Date". I suppose that it would possibly have something to do with the Default Value of the "Expiry Date", but that's where I'm confused. Of course, I need not make it automatic and let the user do the math himself, but I'd rather it was done this way if possible.

I'm using Access 2007 on Windows 7.

Any help appreciated.

A:Default Value for table field in Access 2007

9 more replies
Answer Match 79.38%

I'm fairly new to access and have created a database which amongst other things, keeps track of the current status of drawings.

I have a table called "Drawing Register" which contains the information on a list of drawings including drawing number, title, revision and status.
I then have another table called "Review"

In this table I can obtain the title of the drawing from the drawing register, then i manually enter comments on the particular drawing and also it's status (Status being either A, B or C)

I want the "Drawing Register" table to be updated with the status of the drawing (when it is entered into the "Review" table.

The reason for this is I want to print a report from "Drawing Register" showing the current status of each drawing.

Now is the bit I cannot work out.

If I review a drawing which for argument's sake is called "100", I enter the current status (eg Status A)
The "Drawing Register" table updates the "Review Field" to "A"
At a later date, I may want to review drawing "100" again (but create a new record as I don't want to overwrite the previous review record), so if I add a new record in the "Review" table and add new comments for drawing "100", and then I make the status "B", the "Drawing Register" updated the "Review Field" to "B" which is correct, but it also updated the pre... Read more

A:Access 2003 - Update a field in another table (one way)

Hi Phil, welcome to TSG Forums!

If I got the picture right then your approach needs to be altered a little bit.
1) The Status field should be removed from Drawing Register table, beacuse the status is, actually, not a property of the drawing, but that of the review. Additionally, placing the same data in two or more different tables/fields is breaking one of the most basic rules of databases.
2) A query should be created, which contains all the necessary fields from Drawing Register table, and also the newest Status from the Review table. The tables can be joined by the name or ID of the drawing, and the query can be used as source for report printing.


2 more replies
Answer Match 78.96%

I have 2 tables that are linked by a zipcode field. One one table there is a kinda count field. If the zipcode field on the first table is updated, then I want the count on the second table to increase by one.How can I go about doing this? Thanks for all the help.

Additional info left out: The zipcode field on the count table has a list of zipcodes. When a zipcode is entered on the other table, I want it to match the zipcode in the count table and update the count for that zipcode. Thanks.

A:Update one field in a table based on another field in another table.

Not quite sure what you are trying to do, but is linking the zip code fields a good idea? Are you tring to find the number of times that a particular zip code is entered?

2 more replies
Answer Match 78.54%


Over a month ago, I posted this topic here:

However I never had the time to try out the suggestion. I've finally gotten a holiday though, and so gave it a go. Here's the code I'm using in the After Update Procedure, as advised by OBP:

currentyear = Year([DateofEntry])
newyear = currentyear + Me.[PeriodLength]
Me.[ScheduledDateofRelease] = Left(Me.[DateofEntry], 6) & newyear

It does work, so thank you for that

However, it does produce some odd errors. First of all, it only updates the "ScheduledDateofRelease" after I have clicked "Save" on the form, and once I try to close the form access gives me an error saying that:

"You cannot save this record at this time". In the same dialogue box it says "If you close this object now, the data changes you have made will be lost. Do you want to close the object anyway?"

After clicking yes on that box, and navigating to the table which is the source for this form, I find that the new record has indeed been added, contrary to what the error message was saying.

Another odd thing that occurs is that once I enter a new record into the form and click save, I can't go backwards or forwards through records on the form and am instead presented with the error stating "You can't go to the specified record."

Any help appreciated

edit: I've just rea... Read more

A:Solved: Default Value for table field in Access 2007

16 more replies
Answer Match 78.54%

This seems like it would be a common task in Access but I can't find any reference to it.

I have a combo box with three choices, Kamazaoo, Grand Rapids, and Traverse City. The field name is location. The location is entered only once but there is no limit to the amount of records that can be added. When one record is finished Access defaults to the previous location and starts a new record.

I know how to do a make table query but can I do a make table query based on the fields value? For instance if the entry person is finished entering data for Kalamazoo a button could be pushed that would make a Kalamazoo table.

Building the Ultimate site list for PC support.

A:Make table based on form field in Access

16 more replies
Answer Match 78.54%

Hi There

To make a long story short say I have a table called Table1 with only 3 currency fields called Deposit1, Deposit2, Deposit3 and TotalDeposit. I then create an input form based on Table1. On the form, In design view, I use all 4 fields. On the TotalDeposit field on the form, I do a calculated field as follows:


I then populate the 3 fields with currency - $100, $200 and $300

On the form the calculated field shows a total of $600. However, when I go back to the table, the TotalDeposit field has a zero? How come? How do I get the calculated field on the form to appear on the table??


A:Access problem in calculated field on form to appear in Table

Hi Mario

How exactly have you set your TotalDeposit field to be populated with the sum of the other three fields? That is - where have you typed [Deposit1]+[Deposit2]+[Deposit3]?


2 more replies
Answer Match 78.54%

Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.

A:Solved: Access Table: Copy Data from One Field To Another

16 more replies
Answer Match 78.54%

I've been working on a database to save subscriber & ticket information for my community non-profit theater group. After lots of trial and a ton or error, I like what I have, but determined that I need to change the way I store some of my data. This required the addition of a text field which has a list of 3 lookup values. I added this to the table in design view--no problem.

The problem is in the forms I've already generated. I went to design view for my form and put in a combo box for this new field. The wizard asks me what I want to do with the values: remember them for later or enter them into a field. I would like them entered into my new field. That's what this combo box is for. However, to enter them into a field, the wizard lets me select only from a drop down list of the fields available in that table, and it doesn't include my newly added field. I imagine that this is because the form itself was created before I added this field. I could recreate the whole form, but I spent a long time designing it, and all I want is to add the one new field. If push comes to shove, I'll start from scratch and do the whole form over again, but I can't believe there is not a way to do this.

If it's of any consequence, this form is actually a subform, but I'm attempting all the modifications to the subform on it's own, so I don't imagine that's got anything to do with this prpoblem.

If someone could please educate me I'... Read more

A:Solved: Access--new field in table & problem with form

8 more replies
Answer Match 78.54%

I have created a database in Access 2007 with 4 tables, Customers, Products, Orders, and Order Details (the specific items for each order). I know you don't want to dupliate data and "calculated fields belong in reports and queries" but we change the prices frequently and I need the UnitPrice from the Product Database to "update" to the Order Details Table on the day the order is input.

I have a Order Input Form with 2 sub-forms and my relationships are set one-to-many on primary keys. All other queries and forms work great. I have tried looking at the Northwinds database to determine how they do an "AfterUpdate" event procedure but I haven't used VBA since Access 97 and am not able to convert it for use with my form.

Help is appreciated!


A:Access 2007 Update Table Field from Form

Cosla, have a look at the database that I posted here
at post #5.

3 more replies
Answer Match 78.54%

I have a control in a form that calculates the Gross Monthly Income based on amount entered in Annual Income (=IIf([AnnIncome]>0,[AnnIncome]/12,0)). Can the results of this expression be used to update the Gross Monthly Income field in the underlying table?


A:Access-Update field in table from expression in form?

10 more replies
Answer Match 78.54%

Okay I hope this will be easy.

I have a form that links to a Table. We input information into this form it then dumps the information into the table. Lets call this table Table 1. We also have a second table that has all of our facilities information (I.E. Site name, location, phone number, and whatnot). Lets call this table Table 2.

Now on Table 1 there is a field that has a listbox of the Site Names you click on it get the drop down and pick the location you want. What it DOESN'T do is the following.

I have a phone number field in Table 2 that I want to auto-populate the phone number field of Table 1 depending on the site picked in the listbox where this listbox reffences the same table 2 I want the phone number from.

EG Table 2 has Site A in the first column and hone number A in the second column, Site B phone number B, Site C and phone number C and so fourth. I want to pick on Table 1 the Site A and have Phone number A auto populate from Table 2's Phone number A and so fourth.

Can this be done fairly easily?

A:Solved: Access DB -Autopopulate a field from a secondary table.

15 more replies
Answer Match 78.54%

Hi All,
I need an help in creating auto number field in access database. Let me explain you further. I have an access database which 20 million records. When i am trying to add auto number field i am getting error "File sharing lock count exceeded".
Then i did some google search and got some infomation like editing the registry file , in my case its not possible due to security restrictions.
And another option of adding a code in VB immediate window also i tried but this option is also not working.

"DAO.DBEngine.SetOption dbmaxlocksperfile,25000000"
Can anyone help how to solve this issue else can you please help me on how to auto populate the numbers in a specific field using VBA codes.
Thanks in advance.

A:Auto number field in a Access database table

16 more replies
Answer Match 78.54%

Hi! This is probably a pretty basic question, but I can't find an answer anywhere here or on the net. How do I add new fields, using VBA, to an existing table in Access? Without destroying thd data? My process: I initially import, using VBA, an Excel spreadsheet into a new table (this table is replaced each month by a new spreadsheet.) To facilitate my analysis of the data contained in that table, I need to add several fields to that existing table. Those fields will be used to store the results of several automated processes using that data. Because the table is created from the spreadsheet provided by another department each month, I need to add the new fields each time I import the spreadsheet. I am trying to do everything in VB because the final user needs to perform all functions by merely pushing buttons on a "Switchboard" My version is MSAccess2003. Thanks for your help!

A:Solved: Adding a new Field to an existing table in Access using VB

12 more replies
Answer Match 77.7%

Hi All!

I have a form that calculates an employees current age in a Text box (based on today's date and their date of birth). I created the form to act as an 'entry' form for new employees in an HR database.

I have a field called 'age' in a table that I would like to populate with the result of this formula. How can I do so when the 'Control Source' property of the text box on the form is holding the formula!? That's where I would normally link it back to the table!

Thanks in advance for any help!



A:Can an access table field populate from a calculated cell in a form?

You use simple VBA code in the Date of Birth field's After Update Event Procedure and set the Age field's Control Source to the table field to store the data.

1 more replies
Answer Match 77.7%

I need to add an field to an index in a table using VB but I keep getting getting

Run TIme Error 3376 Cannot Append. An object with that name already exists in the collection.

Below is an example to try. What I ultimately want to do is add a field to an existing index but in this example I simply try to create anew index and add fields to it.

In this example, I create a table named "t New Test" then try to create an index that would be appended into the TableDef Indexes collection for that table (if I could get as far as adding Fields into the new Index in question.
Sub TryCreateTableWithIndex()

Dim CDB As DAO.Database
Dim TDFS As DAO.TableDefs
Dim TD As New DAO.TableDef
Dim F As DAO.Field
Dim NewIndex As DAO.Index

Const NewTableName As String = "t New Test"

Set CDB = CurrentDb()
Set TDFS = CDB.TableDefs

'Check to see if there's an existing table with the name we want...
'If already exists then quit
For Each TD In TDFS
If TD.Name = NewTableName Then
Call MsgBox("Cannot proceed.. a table named " & NewTableName & " already exists", vbOKOnly + vbCritical, "Table Already Exists")
Set TD = Nothing
Set TDFS = Nothing
Set CDB = Nothing
Exit Sub
End If
Next TD

TD.Name = "t New Test"
Set F = TD.CreateField("R... Read more

A:Solved: MS Access having trouble adding a field to an table index in VB

This appears to be a case where you have to know the "magic incantation" to make things work.

I was under the mistaken impression that you could just get a field definition from any convenient source (like the TableDef.Fields Collection) and append it to the Index.Fields Collection.

Rather, you MUST apparently use the Index.CreateField method on the Index object
and then append the resulting field to the collection.

In the example above, replace
'This is where the code fails...
'says there's already an object with that name in the collection
NewIndex.Fields.Append F
'This work nicely now
Set F = NewIndex.CreateField("Rec No", dbLong)
NewIndex.Fields.Append F
I don't know what havoc might be wreaked if the field definition appended to the Index.Fields collection is different from the field definition (of the same name) existing in the TableDef.Fields Collection.

1 more replies
Answer Match 77.7%

Good day all,

I'm having some issues getting a combobox to work with a memo field.

Master repository (table) = my data warehouse
Main (form) = drives data to Master Repository
Verbiage (table) = document types list in combobox on form Main. One of the 3 fields retrieved by the combobox, [Body], is a Memo field containing formatted wording to be used to generate a printed letter.

The combobox will return the correct field/data upon selection but it truncates the information in that field. I read that there might be some sorting and like field issues but I haven't been able to fix or work around the issue yet. Suggestions are greatly appreciated

Perhaps there might be other ways to achieve what it is that I would like to do. What I am trying to do is create the ability to generate a formatted letter hub of sorts. These are one-off letters that are produced at the time of data entry that will use information from the current record and from several tables.


A:Memo field

How many Characters are you trying to display in the Combo from the memo field?
There is a 255 character limit for the columns of Combjo box fields.
You can however display those characters but then use the whole of the table field's data by using VBA code to retrieve the data either using dlookup or a recordset.
Or if the user needs to see all the data you can use an unbound memo field to display the same data in full.

1 more replies
Answer Match 77.28%

I am trying to add a sequential number to a date field in an Access form.
The format of the field is PS 18 Jan 11 xx, the PS is fixed, the current date and the xx is my sequential number I want to add.

Is there a way, to do this when the date is entered? And I would need the sequential number to reset to 01 with a new day.

I'm new to this, any help would be great!


A:Help in appending a sequential number to a date field

16 more replies
Answer Match 76.86%

I have a form (parent form) that has more than 1 pages and on 1 page I put a sub-form with text boxes and combo boxes linked to a table say Table 2.
In this Table 2 it contains historical data of a particular field, say previous employers and the current employer and the data on each employer of a person, so there's a field: a flag to mark that a record is the current one and the other records of the same person are not flagged.

On the parent form, there's a list box displaying content of say Table 1, say list of people.
Whenever a row is selected in this list box, the text boxes and combo boxes on the sub-form should display this person's current data (a record with the current flag checked).
So I linked the list box to the sub-form by the person ID in both tables, by populating the child and parent fields on the forms.

What I'm confused about is the first text boxes display the current dummy data, but then the combo boxes are empty.
It's dummy data cos for now in Table 2, there's only 1 record for a person.
I haven't put the criteria to display data that has the current flag checked, cos there's only 1 record per person now.

I linked each combo box to the relevant field of Table 2 on the "control source".
There's nothing in the "row source" of the combo box, even though later on there should be.

Any idea what went wrong?

Thanks in advance.

A:Problem displaying value of a table field in a combo box in a subform in Access 2003

9 more replies
Answer Match 76.86%

I have a form that I'm using to enter orders for inventory. I just ordered 2 phones I'd like to have listed as 2 separate records (they will be physically tagged individually, so they need to be separated in the table).

I'd like to have a Quantity field that when I type a number and save the record from the form, the number in the Quantity field adds that number of identical records (aside from the Autonumber field for the primary key).

Is this possible? If not, how should I go about it?

We have larger orders, sometimes 10-20 items, all with the same everything in the record except for the physical tag number that will be applied to each item when it comes in.

Thanks in advance.

A:Access 2010: Form quantity field that adds records to table

If the form is for entering the actual order details then you shouldn't have a record in the order table for each item in the quantity field.
What you could do is is to add a record for each item in the quantity field in the Inventory table using the Item's ID to identify it.

1 more replies
Answer Match 76.86%

Now that I have the rate information pulling from my other table, the Rate Total won't calculate unless I overtype the rate field with a new entry. What I would like to do is just press enter to confirm the field and move past it and have the total automatically recalculate.

I think that this would be an event procedure on Enter but I don't know what VBA code to use to make it happen?

Can you please tell me if this is correct and what code will make this recalculate?

A:Solved: Access 2007, Recalculate field after data is pulled from table. OBP??

8 more replies
Answer Match 76.86%


I have been struggling with this for a couple of days.

Here's the scenario: I have 2 sets of data, one shows repeat calls for a list of customers, the other shows trouble reports for the same set of customers. I want to count how many times each of these customers called within 30 days of the original trouble report.

I have tried using a nested count Iif expression but I don't have a lot of experience with expressions and apparently am using it incorrectly. What I have tried is something like this:

=Count(IIf([Contact Date/Time],+30,1))

Any suggestions or ideas are welcome at this point. Thank you in advance!



A:Access 2013 - Count only if date falls within 30 days of field from different table

I would create a 'groupby' query using the DateAdd function for criteria. If this is a report you should be able to link this query to the report query by acct number or something.
SQL looks like this example
SELECT Table1.Name2, Count(Table1.ID) AS NameCount
FROM Table1
WHERE (((Table1.Date1)>DateAdd('d',-30,Date())))
GROUP BY Table1.Name2;

1 more replies
Answer Match 76.86%

I have a corrupt FPT file that I cannot repair. The DBF that is associated with it has 52 records of information. Ive pin-pointed where the corrupted fields are. I have a memo field with the name mplacement. There are exactly 4 so called corrupted records in the mplacement field. The corruption starts at record 18. Records 18 21 seem to be the corrupted ones. Records 1 17 seem to display information correctly. When I export the information from this DBF into an XLS, it only goes to record 17 and then quits, leaving the rest of the records out. When I open the FPT with a Hex Editor, the data displayed ends with record 17, like there is no more data.

Here?s the tricky part. When I open the application and go into 4 records (other than the 4 corrupted ones) and enter data into that mplacement memo field, it magically fixes the FPT and I dont get the Error 41, Memo file is missing or invalid anymore. When I open the FPT back up in a Hex Editor, it shows me records 1 17 and my newly added data for the 4 entries at the bottom. I still can?t seem to recover all of the data from the memo field from records 18 52. Ive got backups (at the time of corruption) of the DBF and FPT so I can try anything. Any help is much appreciated.

More replies
Answer Match 76.02%

Good afternoon all!

I am working on writing a Security Incident Report program, and I am running into a problem. I have a Date/Time field, which I am attempting to auto-update from another Date/Time field, based on the result of a Yes/No field input. Is this even possible? To be specific, the user enters the date of the report. On the next form page, it asks the user if the report date is the same as the incident date. If the user answers Yes, I would like it to enter the data from the report date field and grey out the option to enter the new date. If the report date is different from the incident date, the user would then be required to enter the date of the incident. This takes place three different times on the same form, based on the incident date, date responders cleared from the incident, and the date the incident was placed under control. I am running Access 2007. Thank you!

A:Creating Access 2007 Auto-populate field based on different table Yes/No data

GrahamTechnology, welcome to the Forum.
Yes it is using VBA.

3 more replies
Answer Match 76.02%

I am importing a text file that has two fields. The first is a text field and the second is a memo field. When I look at the table, after the import is complete, I can see that the desired information is definitely in the table, but when I run a query the information is not all listed. The field size of the memo field is shortened. Can anyone help? Thanks in advance.

A:Memo field size in queries

Does the imported text go into a memo field? What are the properties of the field that is supposed to be a memo field?

2 more replies
Answer Match 74.34%


I have a problem with an access database report I am working on. how do I get the memo field of the report to show more than 255 characters? The data is there in the table but when I try to run the report, it cuts off or truncates the data after 255 characters. I went into the query and "ungrouped" the fields which worked to display all the data in the table, but now the query report won't display it all.... any ideas on how to remedy this?

---- Business Analyst

More replies
Answer Match 73.5%

Ok - I've searched the forums, but cant find a similar problem and answer.
Program: Access 2000
OS: WinXP Pro SP1

I've got a database, and one of the tables is a supplement report table. The fields are:
SuppID / Autonumber Long Integer / (Primary Key)
OCA / Text field / (Case number)
SuppDate / Date/Time / (date supplement entered)
SuppInfo / Memo / (memo field for entering the text)

This table is associated to my main database by the OCA field, which is working fine in the querys, and bringing the information up in the reports. The problem is that when I enter about 1 full page of text in the memo field, and then print it in the report - the last few lines are not printed out.
I tried breaking the full text down in its paragraphs and making them in several entries under that OCA. That works fine again in the report, except even though the amount of text in the SuppInfo fields are less for each record in that table, the report still will not print the last few lines out.
I thought that maybe I had reached the character limit in the memo field originally, but when I broke it down, it still cuts it off.
Any suggestions and help would be greatly appreciated !

Thank you,
John R.

A:Solved: Urgent - Access2k memo field not completely printing

The problem in a report is that you are by default limited to the size of the box that you create in the report. The good news is that you can change this. Go into the report in design view and go into the properties of the note control. Under the format tab change the "Can Grow" option to yes. You may have to play with your report a little to make this look good. Keep in mind that the growth is always down and not horizontal.

3 more replies
Answer Match 72.66%

Hi there,
I am not that cluey with access vba programming and I need some help please.
I have a Table called (Parts) with columns (PartName), (PartNumber), (PartCost), (Quantity)...
I have (Parts) table linked to (Repairs) table in one to many relationship.
I have a Form linked to the table (Repairs) with different fields to enter different repair details.
Among these fields is: combo box (PartsNameCombo) where you can select the Part Name from values listed in (PartName) column in table (Parts), and I have used the following codes to populate the selected part's cost accordingly:

Private Sub PartNameCombo_Change()
'Auto populate Part cost based on Part Name
Me.PartCost.Value = Me.PartNameCombo.Column(2)
End Sub

I have repeated the above combo box three times (in case different parts used in one repair).

My Question: I would like to add code to automatically subtract (1) from part's Quantity in (Parts) table whenever a particular (PartName) is selected in the combo box.
so for example, if in a repair, I selected: (front case) in the first combo box and (display screen) in second combo box, I want the cases quantity to drop by one and also screens quantity to drop by 1?
Any one can please help?

A:Updating table value when different table form field updated

At least two ways to do this but I wouldn't make it automatic. What if the wrong Part name was inadvertently selected? I would ad a button to the form "Take one from Inventory" and execute an update query that basically does Quantity= Quantity-1 . This query would need to link to PartNumber on the active form.

The other way would be to write the SQL to do the update in VBA and run the EXEC SQL command under the new button.

2 more replies
Answer Match 72.66%

Hi there,
I am not that cluey with access vba programming and I need some help please.
I have a Table called (Parts) with columns (PartName), (PartNumber), (PartCost), (Quantity)...
I have (Parts) table linked to (Repairs) table in one to many relationship.
I have a Form linked to the table (Repairs) with different fields to enter different repair details.
Among these fields is: combo box (PartsNameCombo) where you can select the Part Name from values listed in (PartName) column in table (Parts), and I have used the following codes to populate the selected part's cost accordingly:

Private Sub PartNameCombo_Change()
'Auto populate Part cost based on Part Name
Me.PartCost.Value = Me.PartNameCombo.Column(2)
End Sub

I have repeated the above combo box three times (in case different parts used in one repair).

My Question: I would like to add code to automatically subtract (1) from part's Quantity in (Parts) table whenever a particular (PartName) is selected in the combo box.
so for example, if in a repair, I selected: (front case) in the first combo box and (display screen) in second combo box, I want the cases quantity to drop by one and also screens quantity to drop by 1?
Any one can please help?

A:Updating table value when different table form field updated

16 more replies
Answer Match 70.14%

Basically I have a situation where users are creating data tables in Microsoft Word using the creating table functionality, and are then resquesting that the data will be imported into a single Microsoft Database field.

Please could someone show me the best way of doing this!!!!!!

Cheers !!!

A:Importing Table Data Into Multiple Access Field Data?

Do you mean they're creating, say, a 3-row by 3-column table and then they want to import everything in that one table into a single field? a single record?

Out of curiosity, what happens if you copy and paste, perhaps with an intermediate step through Excel?

Also, is there a reason why your users cannot work directly in Access?

More details, please.

3 more replies
Answer Match 70.14%

Friend of mine is working on making a Bank Database in Microsoft Access.

After trying different validation format, we failed to get exactly what we wanted.

We did the setting for "Account Number" as "AutoNumber" & set the format in the way, that every time the new record gets created, this "Account Number" keeps on increasing by one (which we did by having "New Values as Increment"). However, since account number we want needs to be at least 5 digit long, I wonder how do we go about doing so ???

Furthermore, in another table, we want to set some sort of format for "Credit/Debit Card" in the way, that it has to be 16 digit long with "space" after each of the 4 digit i.e. in the format "0000 0000 0000 0000" & we wish this to be "Auto Number" & New Values as "Random", however, when we tried we kept on getting "-" sign in front of some of these "Credit/Debit Card" number, once we start typing other details. Additionally, what we want is even if we type in say "1000100010001000", some sort of format/validation automatically changing it to "1000 1000 1000 1000" (i.e. space after 4 digits) & we also want validation, which means the digit should NOT go over 16 & should NOT be less than 16.

Can anyone help in getting what we want in both of these cases i.e. the "Account Number" & "Credit/Debit Card".

A:Microsoft Access Field Format ?

Account number - maybe this will help: card - sorry, I can't follow what's going on. Does this apply?: the problem that some of the random numbers are negative?I tried it out with 8 digits -Format = fixedDefault Value= Abs(100000000*Rnd(88888888))- and it seemed to work OK.

1 more replies
Answer Match 70.14%

I have a database with several tables. One of them is for Contacts and another is for Projects that the contacts are associated to. I would like to build a form that would allow me to look for a contact and if they are new add them to the list. I would also like to be able to change the project table from the same form.

Within the project part of the form, I would like to have a commit field for the customer. This will probably be linked to another table, but I would like to have the ability to add a new commit to this table. I would also like to see at least the last commit for that client. I was thinking of adding a button to my form that was linked to a macro that would add the existing commit, date, client name, and who the client spoke to, to the commit table. I think this would only allow me to see on my form only the last commit. I am not exactly sure how to add a button, or how to do a macro in access. I have done them in excel but not access.

I would also like to add a hyperlink on the form to the commit table that already has the customer's commits filtered.

Any help on any of the topics would be greatly appreciated. If you would like any other information let me know. The database is very large so I could not attach.

A:Microsoft Access Commit field.

11 more replies
Answer Match 69.72%

Here is what I'm trying to do.

For lists Equipment in drop down box.
Whatever equipment that is select, the equipment type field needs to be updated from a table.

Is there a way to get a value from SQL statement?

SQL = "SELECT [Equipment Type] FROM OrderDetails Where " _
& " Equipment = '" & Me.Equipment & "'"
[Forms]![OrderDetails]![Equipment Type] = SQL

A:Help with access form (insert table value into form field)

Mhouser, if you are trying to "display" a value related to the Combo selection you can have thta value as an extra column in the combo and refer to it with simple VBA.
You should not populate a Field's actual value with that from the combo as that is duplication.
Can you tell me which one you are trying to do?

3 more replies
Answer Match 69.3%

I am trying to set up a field to increment for each new record. I know about auto increment but the way I have it set up their are two sets of numbers. 1 for the group number and one for the Item # of that group. The group number increments when a button is pushed. The Item number would increment either when a button is pushed or the user tabs through all the fields. Example 1-1,2-1,2-2,3-1, ect . I would also like their to be a way that if their is not data entered in a group or field that it can be either easily removed, or give an error that the form is blank and not go any farther. The code attached is in my database but it does not seem to work.
Thanks for looking,

Private Sub New_Item_Click()
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
MsgBox "You're in a new record."
a = 1 + Item
Item = a
End If
End Sub

A:Solved: Microsoft Access Increment a field

16 more replies
Answer Match 68.46%

Hi guys, I'm very new to access and I'm having some issues. I have made a field for an expiry date, and I have attempted to create a calculated yes/no field that gets ticked when the expiry date is up, so basically I have a box that I want ticked for when the expiry date is before the current date (I hope that makes sense). Thanks guys,


More replies
Answer Match 67.62%

The following is the error message generated i'm getting when i publish preview an asp page created in Drumbeat2000:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

/jt/media/IIS_Gen_3.0_Recordset.js, line 392

This occured when I created an asp (active server) server generated page (created in Drumbeat 2000) which uses the content from a database created with microsoft access 2000.

When previewed inside the Drumbeat2000 program, this error message is displayed...When viewed with explorer 5 or netscape, the data form elements simply are not visible.

Tech Notes:

Software used to create asp page: Drumbeat2000

Software used to create database: Microsoft Access 2000

Server info: Microsoft Personal Web Server 4 being run on my local computer

Somebody please tell me hot to eliminate this?

A:(Microsoft Access Driver) Error Count Field incorrect

Since no one else answered this one yet I guess I'll take a shot.

I would suspect there is a field being mapped to a field/variable of the wrong type. Do you have an aggregate function on a non-numeric field that's mapping into a field of the same type as the original instead of a numeric field that would hold the result of the aggregate?

1 more replies
Answer Match 67.2%

There are expressions to display the time and date in a report. Is there a way to link a text box in a report so that it displays the name of a table? If not, is there any other way to display the name of a table automatically in a report.


A:Microsoft Access - Displaying the name of a table in a report.

Depends what you mean. If it's the underlying table or query on which the report is based, sure. Just set a text box's control source to "=[RecordSource]" (without the quotation marks, of course). Retrieving any other name, however, including names of tables that may be included in a record source, would be a little trickier.

Please let us know if this does what you're looking for.

2 more replies
Answer Match 66.36%

Hi there,
I'm working on my A level coursework and trying to update a table by using a form which allows the "user" to input their information easier. I have tried using an update table query, however, I can't run that off of a form. Anyone got any ideas?

A:Microsoft access: Is it possible to update table fields from a form

You need to create a Select query to do that.

2 more replies
Answer Match 63.42%

Using Access 2003-

I need to use an existing table field value in an If statement- I have found only many different ways to get error messages.

The user selected the table name "Module" and is just a text name.

I want to look up the value of the field "Valid" in the "Modules" table.

If the value is "Yes" [straight text field] then I want to open a form and have that module number be already loaded into the "Module Number" field.

If the value is "No" then I want a pop-up form that tells the user "That number does not presently exist. Would you like to create a new Module now?" with two buttons- one that says "Yes" and opens the new module form and the other to just say "Cancel".

Thanks for your help!

More replies
Answer Match 63.42%

I have a database where I have created a form with 5 places to chose types of errors on an application. Those errors are associated with a Grouping. For example: In the table called Summary of Errors I have an error that reads " Agent Calculated New Rates" in the column beside it the Grouping would be "Premium Differences". Within the form, if I selected the "Agent Calculated New Rates" error I would want the grouping to auto populate in the table. Could someone please explain how to do that. I am sure its something simple.

A:Autopopilating a field in a table

6 more replies
Answer Match 62.58%

I have 2 tables and need to copy all the values from one field of tblB to an existing field in tblA.

I am using the RunSQL command from within an Access Macro and have inserted this SQL Statement.

UPDATE tblA SET fieldA = fieldB from tblB

This syntax does not seem to be correct since I am getting error message:
"Syntax error (missing operator) in query expression 'fieldB from tblB'."

Can you please help.


A:SQL statement to copy field from one table to another

try this:
select * into tblB from tblA

I hope these helps.
good luck.

2 more replies
Answer Match 62.58%

I have a column for Count of Missed rooms, and a count of Total rooms. I would like to use a calculated field to create a % of Missed and Successful.
Every time to try to create a calculated field, I get #DIV/0! and 0
What am I missing here? I've even taken the raw data and moved it to a new workbook to try and re create it... same issue
I'm attaching a print screen

A:Pivot table Calculated Field not doing its job

the fields with Red and Blue are added in... they are not part of the pivot table. In fact, they have the formulas that I want... Only, I want them as part of the pivot table instead of formulas just sitting there

2 more replies
Answer Match 62.58%

Trying to append to an empy SharePoint list from another sharepoint list using an ACCESS append Query. It append 16 records then I get the message "Data cannot be inserted because there is no matching record".

We just upgraded to SP 2010 monday. This is an important list and I was not allowed to investigate. Just imported the results of a select to be the list for now. Was hoping someone could explain this. I'm assumming it has something to do with the key. which is an autonumber field. I tried populating with the existing number and not. same message both times.

TSG rules!

A:Access appending to Sharepoint list problem

I do not know anything about Sharepoint lists, but the error message suggests that there may be a related table where it doesn't have a matching record.
Can you just import the sharepoint list, ie not append it, you may then be able to see what record causes the problem.

2 more replies
Answer Match 62.16%

Dear all,
I don't know how to elaborate my problem. But what i wanted to know is Eg, i got a master sales type table [ ID, orderTYPE , OrderNo] which is related to sale table [id, ordertype, orderno date] in a relationship of 1 to many.
SO, how can i insert 2 fields (ordertype and orderno) into a form in which i would select the ordertype from the Sale Type table by combo box and it will auto-shown the orderno which is in sale type table. Meaning that i would need the sale order no to be like YD (ordertype) 1(orderno) , YB.1 and so forth.The orderno in sale table is duplicated as well as the ordertype.
Hope u help me out.

Thanks a lot.

More replies
Answer Match 62.16%

Hi There

Does anyone know how to convert a merge field (for multiple letters) into a table.

I have a list of assets and values saved in an excel workbook seperated by a | and want to convert the merge field into a table once all records have been merged. Its impossible to split the cell in excel (truth be told theres more than one list and without knowing how many assets there are in each cell I will end up overwriting data and the excel file is already big enough)

For someone reason it converts the merge field into just one cell of a table.

I tried this on a just plain text (same data) and it works fine.

If anyone can thing of a way to do it hopefully as part of a mailmerge before/after event it would be appreciated.



A:Word - Converting merge field to table

You need to click on the "Update all Labels" button when using the Label format. I think it is the 4th step into the wizard at the bottom of the taskpane. It is easily missed by many.

1 more replies
Answer Match 62.16%

My Access 2003 DB is being deisgned to allow 15 sales guys to gather site information and disseminate it in a structured manner to other project members as PDF reports, as attachments to emails. These are sent from the Access database which uses CreateObject("Outlook.Application") to get the process going.

The 15 users of the database all have Outlook 2003 and each has an Outlook Signature (fixed tag line, with their name, title, and phone number, branded, so to speak) that is unique to him/her.

We know that the signature is stored as HTM, RFT and TXT files in the user's own %USERPROFILE%\Application Data\Microsoft\Signatures folder.

I am tasked to ensure that each email that is sent using the Access DB always has the user's own formatted signature appended at the bottom, as if sent from Outlook as a first party client, so to speak.

How on earth do I do that?

Other than this, all is well, and emails are arriving as required. The only problem I cannot grasp a fix for is the ability to append that signature.

Can anyone shed some light on this one?

Cheers buddies


A:Access 2003: Appending a Formatted Outlook Signature..

Hi Chris,

Not my area, but if you post your code dealing with calling the reports and starting Outlook someone (like Zack) can probably help with this. Would also need to know what the signature files are called. Is there a reason they are stored in various formats? Is there a particular format that you want appended to the email?

3 more replies
Answer Match 61.32%

I have a dynamic range set up for a pivot table which works well but I was hoping to also make the Field Names dynamic. At the moment whenever the field name changes (as I have the source data headings dynamic) the fields all drop out of the pivot & have to be added again.

This is time consuming as there are many columns of data to be added, does anybody know of a way to make the Field Headings in the pivot match the source data without dropping out of the pivot when the heading changes? It is always the same columns of data that are needed in the pivot.


More replies
Answer Match 61.32%


I'm trying to set up a pivot table on some data and when I select the data and click on Insert Pivot Table, it opens a new sheet in my workbook for the new Pivot Table, but there is NO Field List to drag fields into the pivot table. And, yes, I do have the Pivot Table box on the left selected, but NO Field List.

Thanks for the help

A:Excel 2010 - No Pivot Table Field List

7 more replies
Answer Match 61.32%

I have several offices that do not have a network. I am creating a Capital Project database that can be distributed to each sattellite office. They can enter their projects, then click one button for an export that combines all the tables into one query, and attaches to an email addressed to the head office. Once the email is received the data has to be imported back into the master databases. I have about 30 departments to send the program to, so I don't want to customize it drastically 30 times.

My problem is that if there are four tables in each database all that have auto numbers for primary keys and they are joined to each other with these primary keys. All 30 departments will have export data that looks the same and will duplicate each other's indexes. I plan to append the data from each email to an import table in the master, then append that data back out to all four tables in the master. But I'm worried about the joins. I want to make sure that office number 1's [ProjID] number gets rejoined with its Funding table and not Office number 2's funding table by mistake. This is holding me up and i'm not quite sure the best way to go from here. I've watched several video tutorials but nothing quite addresses this issue.

A:Solved: Access 2010 - primary keys, autonumbers and appending

I take it that this is related to the cause of the problem in your other "Appending" post?
One approach would be to have the Autonumbers of the databases in the different offices start at different values, maybe 10,000 apart or 1,000 apart depending on the amount of traffic they have.

3 more replies
Answer Match 60.48%

I have followed below link and tried to apply fixes recommended for the missing field list but not successful. After applying these fixes, I managed to see the field list option highlighted but the drag data table did not appear. I can't see it at the bottom or side of the Excel. I appreciated it if anyone would help me, encl copy of my test_pivot file for your review.

A:Excel 2007 Pivot Table Field List missing

Hi Maple_991, welcome to the forum.

Have you tried creating a new pivot table in the same sheet, do you see the fields?

1 more replies
Answer Match 60.48%

Hi, I have a workbook with one main tab and 16 other tabs (mainly charts that are derived from pivots). The source of all pivots is the main tab.In the main tab, one of the fields is a ?Status? field that changes almost daily. "Status" is one of the pivottable field list. I use "select multiple items" within this field list to select the Statuses relevant to the metrics I want. I want to create a macro that would look at the options under the Status field list and select the items I want if they are visible.I have the following macro so far, but the problem is if one of these statuses is not active and thus is not in the main tab the macro runs into a bug because it can?t find it among the options available under Status in the field list.I believe the solution would be a macro based on ?if/then? that says if this reporting filter exists then select it in the report filters options.I would appreciate the help.With ActiveSheet.PivotTables("PivotTable1").PivotFields(" Status") .PivotItems("Phase 1 - Review+").Visible = True .PivotItems("Phase 1 - CWG+").Visible = True .PivotItems("Phase 5 - PIAR Review+").Visible = True .PivotItems("Phase 2 - Kickoff+").Visible = True .PivotItems("Phase 3+").Visible = True .PivotItems("Phase 4+").Visible = True .PivotItems("Phase 5+").Visible = True .PivotItems("Phase 5 - Implementation+").Visible = True

A:VBA to select multiple items in pivot table field list

Unfortunately, I have not coded for (or even used) Pivot Tables, so I can't be much help here. Maybe someone else will step up with some assistance.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Answer Match 60.48%

I have a Make Table query that when I run it and view the table in the query it shows only the data I want. But when I go and open the table that theis query made, there are entries that should not be there.

It is putting in rows with blank fields, I am not sure how to stop it. I have tired to put something in the query for that field but I get back the error "Invalid Use of Null"

If I could put in the criteria that worked I would be all set.

Any ideas?

A:Solved: Access (Make Table) Query Results Returned not Same as Table

6 more replies
Answer Match 60.48%


Just wondering if anyone could tell me if and how i can revert back to an older version of a table.

I had a table saved contain product details
whilst i was away a work mate accessed the table deleted some colums and saved over the table.
i was just wondering if the was anyway to undo this save, or if a back of the previous table could be found somewhere.

using Access 2000 on Win XP (no restore point available).

thx in adv.

A:Access 2000 table backups? / undo a save to a table

Not unless it's on a drive where you have been backing up the entire database or have specifically set up for the database to backup the data...

1 more replies
Answer Match 60.06%

I have windows vista home premium and Office Excel 2007 Professional Plus edition. When I select a range of table to make a pivot table , a blank table appears and no Field List menu appears... have tried to make it appear by clicking Toggle button of show field list but it do not respond... have tried to uninstalling and reinstalling Excel but to no avail..I have noted that a lot of buttons on ribbon on pivot table menu ribbon do not appear to respond ... why is this so.. PLEASE help

More replies
Answer Match 60.06%

I'm suffering the same problem stated by Rcook : Excel 2007 pivot table field list missing
After a long seach, Rcook closed off stating that his problem was solved
with the following statement :

Thanks - Interesting I never picked up that one when I did my search. Anyway - I tracked through all of the solutions, getting despondant as not working.Until I got to removing


That did the trick. Now all I have to do is reconfigure all my settings. Hoping I don't lose the Field List again
My question :
I'm a bit reluctant for removing what is indicated.
How do do it ?
What are the consequences ?
What has to be reconfigured ?
Thanks a lot in advance for any advice.

More replies
Answer Match 60.06%

I have a Word 2010 document and I want to have specific text appear if a date is more than 42 days away from today (whatever today happens to be). I've got a three-cell table that looks like:


| DATE: | 12/25/13 | { =IF(B1-TODAY()>42,TRUE,FALSE) } |

I keep getting a Syntax Error on my field in the 3rd cell. I think the problem is that Word doesn't understand "TODAY()" like Excel does, but I don't know if there is any other way to calculate the days between two dates? My eternal gratitude to anyone who can figure out how to do this properly.

A:Date calculation using Word 2010 table/field Syntax Error

You will need to create a spreadsheet then "Link" that spreadsheet containing the calc cells.

Things to Remember When Linking
If you move the linked Excel file, you break the link. To fix this, you need to relink the file.
Double-click on the Excel data in your document to edit it: this opens the linked spreadsheet.
You can also edit the spreadsheet directly: the changes appear in your Word document once you save the Excel spreadsheet.

3 more replies
Answer Match 60.06%

I've upgraded from Excel 2000 to 2007 and cannot get the pivot table field list to show. I activate the pivot table and switch it from hide/show either by right clicking or from the toolbar but still no joy. I''ve looked all over the spreadsheet to see if it hidden anywhere but cannot find it. Any suggestions? If it is hidden somewhere can I move it via vba code?

A:Solved: Excel 2007 Pivot Table Field List Missing

16 more replies
Answer Match 59.64%

I am starting this Access database from scratch. I have to import/upload a sales file weekly. I will be adding this file to a main sales data table. The issue I have is that this file does not have any dates on it. I need to be able to add a start and end date to the table.

Example fields that I would need/have

start date
end date

Item #
Store #
Sales Units
Sales Dollars

What is the best way to handle this? I have an idea - just not sure what order I should put my steps for importing the file, asking for the date range, adding the date to the table, etc....

A:Solved: Access - import table - add date to table

9 more replies
Answer Match 59.64%

I am having trouble updating data from one table to another. I am not Appending the data.

I've linked the two tables on an ID field.
I created a select query to see the data I would update.
I changed the query to an Update query.
In the Update To field I placed the following: [table].[field name]

After running the query I noticed the update did not work. The data from the first Table did not get updated to the second Table it wound up being deleted from the first Table.

Your help is appreciated.


A:Solved: Access 2003 Update Table from another Table

Buttercup1, welcome to the Forum.
I am not sure why you wan to update the second table with data from the first table.
Can you show me a screenshot of your query with the table(s) in and also your Table relationships?

3 more replies
Answer Match 59.64%

In this database, i have a data entry form called "Information Requested" and all information in this form when entered are added into table Information Requested. In the form, 3 fields Names, Business Units and Emails are drop down lists based on table Contact List. However, what the form does now is i have to pick the name, business unit and email manually. It's not really efficient this way. what i want is when i pick the name, the corresponding business unit and email of that person will automatically show up in the box. How can I achieve this?

A:[Access 2010] How to append record from one table to another table.

The data should NOT be transferred to the table, only displayed on the Form, as it should only be entered once.
You can display it on the form by including the data in the Combo as a "Concatenated" field or you can transfer the data to "Unbound" fields.

1 more replies
Answer Match 59.64%

I'm trying to run a clear table query (to keep the table, but delete the data), and then an append query to repopulate the table, and it's not working.

...any idea what I'm doing wrong?

A:ACCESS clear table/append table not working?

11 more replies
Answer Match 59.64%

Hey there folks...
I am in need of some assistance. Obviously!

I need a form which allows a user to select a record from one table and completely move the record to a totally separate table in the same database. I need it to transfer all the data for the selected record.

AutoNumber will not be an issue as each record has unique identifiers.

I found this article online but it seems it will append ALL data and doesn't allow a user to select the correct record. This uses an Append Query which makes sense but again it seems as though it will transfer ALL records. All I need is for it to transfer a specific record depending on the user's selection in the form.

A:MS Access 2007: Moving a record from one table to another table...

Is this to "Archive" the record?
It is not normally necessary to actually "Move" a record, as you can just have a tick box called archive or similar that can be used to exclude that record from normal display and to display it as an Archived record.
There are 2 ways to do what you want, if it is really necessary, one is using an Append query, you can Identify the currently selected record on the Form using
forms![Form Name]![name of Key Field]
in the First Criteria row of your Key field, where Form name is the actual name of your form and name of Key Field is the actual name of your Key Field.
The other version is to use a VBA recordset Clone to do the same thing.

1 more replies
Answer Match 59.64%

In DOS-based Alpha 4, a database I've been using for years, text fields were limited to about 250 characters, but a memo field, stored separately from the rest of the record, if I understood this, was possible with far more space.  In migrating via Excel from Alpha 4 to Access, I seem to have lost the contents of the Memo field.  Does anyone know the filename where such fields were stored, perhaps dependent on the original table name to which they apply?  I do not think there were any such files with the .dbf extension, which constitute tables typical for databases.  I imported all files with that extension, and there were only 2, the 2nd a set of "child" records related to the first.  Any hints on where to look and how to re-attach this information would be appreciated.  I'm presuming some key field in common, of course, but don't know which of the many files in the old collection of such (that orchestrated all the database functions) is the one to try to import into, perhaps first, Excel, and then into Access.  Thanks for any help offered. (One thing I'll be trying, not done yet, is to just look for large files, regardless of extension, and snoop to see if I can tell what they contain.)

More replies
Answer Match 59.22%

Request any help possible,

- Involves MS Access 97 SR-1 and MS WORD 97 SR-1

-Report generated for records Containing many Memo-Type Fields.

Reports are fine when Printing from Access, However Using Publish It with MS WORD or Save As/Export... Commands prouduces an RTF file with many memo field values truncated (Records with larger amounts of Data)

There seems to be no set limits to number of Characters (256, 512, 1024)

I've tried using the free support Site (Knowledge Base) And have not found an Adequate Answer.

Thank You In Advance for Any Help.


A:Access report memo-fields truncated on export to RTF File

7 more replies
Answer Match 58.8%

I am working on a database that stores genus, species, and variety I want to sort the data with genus, then species, then variety.

Now the tricky part. Sometimes there is no species. When this is the case, I want the database to sort that record off of variety.

I have though of a way to do this, but need help with the code.

If I make a species2 field, I could create an expression that would look at the species field. If the species field is empty, it could copy the data from variety. If it is not empty, then copy the data in the species field.

Once the species2 field has the updated data, I could then do a sort

genus, species2, variety

The report would have genus, species and variety showing and species2 would be checked off to not show in the report.

I am using Microsoft Access 2000.

Any help would greatly be appreciated.

A:Access - if field is blank, copy field A, if not copy field B

You should not actually need another field to do this, just another column in your report's Query. Something like this as a Field header :-

dummySpecies: iif(isnull([species]), [genus], [species])

this should provide a column with the species in if it there and the genus if it is not, which you can use in place of your actual species field in the report for sorting.

1 more replies
Answer Match 55.02%

Hi All,

I'm attempting to import data from an Excel spreadsheet into Access. As I'm just trying to get it to work first and then properly attempt to use it, I've simple exported my table "tblTenants", and renamed it Import.xls and changed one column of data.

Public Sub ImportExcelData()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTenants", "C:\Documents and Settings\Greg Webster\My Documents\Property DB\ImportFiles\Import.xls", True, "A3:I5"​End SubClick to expand...

However, I get the following error:
Field "F1" doesn't exist in the destination table "tblTenants."Click to expand...

And I don't know why, please help.

A:TransferSpreadsheet :: Field "F1" doesn't exist in the destination table

8 more replies
Answer Match 53.34%

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.

A:Copy data fields in same table to other fields in same table MS Access

16 more replies
Answer Match 52.92%


I need to add rows from Access table to Excel spreadsheet by using VBA.

I cannot create a new file for various reasons, so I cannot use command “TransferSpreadsheet”.

Is it possible to do it?


A:Solved: Access 2003. How to add rows from Access table to Excel spreadsheet by using

16 more replies
Answer Match 52.92%


We have an Access application that holds employees' table. When new employee comes, the email stating the name of a new employee and the link to this Access app. being sent to a supervisor. When supervisor starts the app, it opens the list of all new employees for a supervisor to choose from.
What my boss wants is, that access will open a new employee record by itself, depending on the name from the email. In other words, he wants to open the specific record by the link from email.

Is it possible to do this?


A:Solved: Access 2003 - Opening specific record in Access table via link in Outlook

16 more replies
Answer Match 52.08%

Looking for the best approach to updating/altering an Access table via DSN-less connection string.

Situation: This process will be run nightly via MS Schedule tool

1 - contains 1 table which acts as a linked central datasource for
numerous databases - this cannot be change. - in some instances this
maybe left active which affects the altering of the table. Alter is
need to change 3 fields's data types or convert prior to update.

2 - contains the temporary table and the code or possibly just the code
to update the data in the linked table source.

Note this code
cannot be kept in DATABASE1 - due to the fact that this database is
occasionally opened and used by other users, so I cannot have the
Autoexec fired when the user access the database - hence the need for

Now the Oracle data source contains numerous fields
that need to be converted in 1 way or another, including converting a
date that in contains a text (varchar) datatype ie. 090822, when I
attempt to covert this and place in an Access Date/Time field - I am
getting an error.

My thought is to create the temp table then
export into Database 1 - which will replace the existing with the new
data - or possibly have the Database2-tbl linked in Database1 and do a
delete and append queries - however, then I run into the conversion

Any and all suggestions is greatly appreciated.

that needs the conversio... Read more

More replies
Answer Match 50.82%


I have a database with a field called "Date". But on that field, I just want an automatic date of 10/31/05 meaning when the user goes that field it will be automatically populated with that date. How do I do that?

A:Access - Field

6 more replies
Answer Match 50.4%


I've looked at a few forums on this, but none of them seem to highlight the problem in this situation!

I've got an access split DB, and I've been creating some new forms. I've only created them in exactly the same way as previous forms, but these ones are coming up with the above message. The error message appears 3 times before letting me enter data. The query that the form is based on is only based on 1 table.

The wierd thing is that I have 2 forms that are based on the same query - both created this afternoon, and one of them will add data, and the other one wont. I have attached both parts of the DB so you can test it. Just try adding data into a new record on both forms.

The form "Yellow Team KPI Entry" is the one that brings up the error box, but the "Main Data Query 2" Form does work. I've checked all the settings that I can, but can't find anthing wrong there.

I'd be very grateful for your help.

A:Access: Field Cannot Be Updated

16 more replies
Answer Match 50.4%


I am stuck with my personal project in Access 2007. Basically I am trying to construct a database for my DVD's based one movies titles and actors. I made three tables, actors, movie info, and a third table to resolve the many to many relationships. Then I made a form with the actor names and a subform for the movies titles that actor is involved in. The form's purpose is to add movies to an existing actor...easy enough.

My problem is when I try to add a movie, I get the message "Field cannot be updated". Very frustrating! I read some posts on this site regarding similar issues but it didn't help me in this case. It's probably something really obvious which I am missing. Any suggestions would be greatly appreciated.


A:Access Updating Field

The was a problem with the record source of your sub form.

See the attached

3 more replies
Answer Match 50.4%

Is it possible?

I have a database which has many Y/N fields. I would like to be able to make a calculation based on:

Is the field checked(true)
If so, What is the value of that field.

Some fields I would assign a value of either 2,10,15,40

How would this be done?


More replies
Answer Match 50.4%

In access 2000 database have designed Form.
Previously able to enter value in a previously designed form.
Designed new form and now cannot enter data or value into one text field. No trouble in other fields of same form.
Unable to figure out the reason.
Tks BobA

More replies
Answer Match 50.4%

I am configuring a database, where I am needing to fill in fields that are greater than 255 characters - Closer to several THOUSAND characters...

Can something be constructed to have all data filled in on one field, and if it goes over 255 characters, will automatically cut off at 255 chars and move the remaining characters to another field, and if that one is greater than 255, move the remaining to a third field, and so on....

Or better yet, is there a way to remove the 255 character limitation?

I am using Access 2003.

Thanks for any help!

A:Access Field Sizes

Just use a Memo field.

2 more replies
Answer Match 50.4%

I am trying to develop a little accounting program in MS Access 2007. I want to take transactions such as a credit card payment and be able to drill down into its component parts. I would enter the credit card invoice and then be able to enter the charges that made up the credit card total. Does anyone know how I might do this?

A:Split field in Access

Create separate tables for the Invoice and invoice items associated with that invoice number in a one to many relationship.

3 more replies
Answer Match 50.4%

I've got 2 large tables that I have joined by relationship, since having all information in 1 table results in the "more than 255 fields" error. Now I want to create an input form for / based on the 2 tables, but get the same error message when I try to create a query for the form. Since I will also need a report based on all the information, how can I proceed? I've whittled the individual field sizes down as far as they can go, I think. And many of the items in the form are "yes/no." Is there a way I can increase the default field size? Thanks for any help.

A:Access field size

6 more replies
Answer Match 50.4%

I am an Access newbie that is feeling my way through designing a database. I am trying to compare data from a field in form1 (the data is from table1) to a field in table2. The fields in each of the tables have the same name and are the same data type. If the data displaying in form1 exists in table2, I want to make an image on the form visible, if the data displaying in form1 does not exist in table2, I do not want the image to be visible. I assume this is going to use vb, but I don't know where to begin.

The field in form1 is [CleanResults].[TestID]
The field in table1 is [UpdatedResultsTable].[TestID]
The field in table2 is [DisabledResultsTable].[TestID]
The image is [CleanResults].[AttnImage]

Any help would be greatly appreciated.

A:Field comparison in Access

if you create a query that joins table 1 with table two using the same field it should only show the rercords that occur in both. then use that query to populate your forms.

3 more replies
Answer Match 50.4%

In Access, is there a macro or function that can be used or setup via a key combination that will sum the total of any numeric field of a table that I have placed my cursor in and display the total in a little window. I would like to use this instead of having to go through a query process.
For years I have used a DOS database Paradox 3.5. It has a function called "imageCSUM" that I have assigned to a key combination and will do this.
Has anyone done this or something like it that could be adapted.

A:Access Sum of field Macro??

7 more replies