Run an append query from a form Results

I have linked tables from a Paradox database to Access 2003.

The Paradox database has several hundred tables, I am interested in querying a set of them. The set of tables I am interested in are generated automatically by the database, each time certain entries are created a new table is generated. The tables all contain the same type of data, the fields I am interested in are as follows ….

ObjectID, text, (a unique ID field for a person)
DateSearched, date,
Status, text

The table name, eg J000161, represents a Job no. Each time a new job is created a new table is created. The people appearing in the table may simultaneously be in more than one table.

I have created a new table, called Total, with the following fields;

Nos, Autonumber

Then a Union query as follows;

SELECT ObjectID, Consultant, Status, "J000161"
as [JobNo] FROM J000161
UNION SELECT ObjectID, Consultant, Status, "J000162"
as [JobNo] FROM J000162
UNION SELECT ObjectID, Consultant, Status, "J000163"
as [JobNo] FROM J000163;

I have then used the Union Query as Source for an Append query using table Total as the destination table.

New J000…etc tables are created regularly, I would like to know if there is a way of adding these new tables to the Union query without having to manually enter SQL. Is there a means of creating the union query based on a selection from a user input form ? And if so how to create a drop down list in an input form that lists all the tables that are Job tables that can be selected for inclusion to the union query? (ie those with J000161, J000160 titles etc)

The people in the job tables and the status of those people is updated frequently. It seems once the info is appended it stays in the table. Is there a way of refreshing the table each time the append query is run ?

Any ideas appreciated.

I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.

The following error message occurs:

"ODBC -- insert on a linked table 'linked tblname' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)

I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.

What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.

I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated!

I'm trying to use an append query to copy data to be further modified leaving the original tables in tact.

The data is to come from two tables 'orders' & 'orders details' and append to two tables 'delivery' & 'delivery details'.

Assume the following:
‘orders’ has ‘orderID-(AutoNumber)’, ‘customer’ & ‘cust_order’
‘delivery’ has ‘deliveryID-(AutoNumber)’ ‘orderID’ (combo), ‘customer’ & ‘cust_order’
‘order details’ has ‘orderID’, ‘item’, ‘description’, ‘qty’ & ‘price’
‘delivery details’ has ‘deliveryID’, ‘item’, ‘description’, ‘qty’

Both ‘orders’ & ‘delivery’ have forms that show the respective details tables in a sub form (i.e. for item 1,2,3...).

Both ‘orders’ & ‘delivery’ have (AutoNumber) on their primary index (required).

‘delivery’ form has a combo for selecting the relevant orders.orderID value to select the set of records to be appended.

I have created two append queries, one that appends required data from the ‘orders’ table to ‘delivery’ table, the second to append the data from the ‘orders details’ table to the ‘delivery details’ table. (The thought being to run these together in a macro via a command button)

The second one seems to run OK as it will append the required data from ‘order details’ into new records in the ‘delivery details’ sub form and is linked back to ‘delivery’ by ‘deliveryID’.

The problem I'm encountering is that when the first append query is run, I need the selected data from ‘orders’ to go into the current active records in ‘delivery’.
The result I'm getting is that the selected data does append into the correct table & fields, however in the next new record.

i.e. if I’m in ‘delivery - form’ and creating a new entry, all text boxes are blank exept ‘deliveryID’ which is (AutoNumber).
I select the ‘ordersID’ combo and enter or pick the required value from the dropdown list which sets the criteria for data in ‘orders’ to be appended (lets say ‘ordersID’ = 1234), after selecting this the ‘deliveryID’ will be given a value automatically (lets say ‘deliveryID’ = 9876).

At this point the idea is to have a command button run the append query so that the corresponding values in ‘orders - customer’ & ‘orders - cust_order’ are appended into the current open records in ‘deliveryID’ = 9876 not the next/new records.

The result I'm getting is the values in ‘orders - customer’ & ‘orders - cust_order’ are appended into the next or new record set, ‘delivery’ table, ‘deliveryID’ = 9877

‘deliveryID’ = 9876, ‘ordersID’ = 1234, ‘customer’ = blank & ‘cust_order’ = blank

‘deliveryID’ = 9877, ‘ordersID’ = blank, ‘customer’ = appended data & ‘cust_order’ = appended data.

Can this be fixed so that the appended data for ‘ordersID’ = 1234 goes into the text boxes of ‘deliveryID’ = 9876 ????


I am running a append query from a button.

In the query, I have set the OrderID criteria to:
Quote: [Forms]![sfrmOrders]![txtOrderID] Whenever I try to run the query from the open form & subform, an Access 'Enter Parameter Value' window pops up.

I would like to use this Criteria for many different features in my db (SendObject, etc.), but I cannot find how to set the OrderID based on the current form.

If anyone can see what I am doing wrong, I would appreciate your help!


Hi all. Having searched around I cannot seem to find what i'm after. I have a database which i hope to give users the option to run an append query which will copy certain pieces of information form one weeks to a another week.

Originally I thought about w/c + 7 which would give me a new week, but not all users were copying from one week to the next sometimes they would want to copy from one week to a date that was 2 or 3 weeks later.

What I can't find out is how to make the query append this date field to a date that is specified by the user.

Any ideas please so that I don't have to make some people angry with me.

Ok let me explain
I have a list of 50 people who may have accessed events A,B,C at different times but what I want to do is to put a date in my form, run the quaery which will list all people who have Attended A,B or C

When I do my normal query, I put the fields in, but if I put a date filter in Event A, it will not show up any person who may have attended event B but not Event A.

I thought about running an append query but this would mean running the data several times with possible duplication of client names.

Ideally I would like to have my report to show
Date: 31/07/2006
Name A B C
K.Brown Y Y
J Blogg Y Y
K Smith Y
K Ellum Y Y

then I can just use a count at the bottom

The methods I have tired will filter event date A, but will not show J Blogg
If I create another append for Event B, and append that data to a table, then I will have duplicaion of K Brown.. Any Help Appreciated.

Last thing - when I run the append queriy and get my duplications, the fields where I had a Y/N option - if it was YES then I get a -1 instead of 1 ??? Why? and how do I make sure that when appending that the Y answer is a 1 not a -1

I understand that I can use an append query to add data to a table from a form and then have it run by using a command button. I want to do this because when I link the form directly to the table I do not want the form to create a new record every time it is invoked. (it does this automatically because I have visual basic code move to acNewRec and fill in some of the fields on load and lock them off, this is neccessary) I also do not want it to create the record immediately because the user might then decide to cancel their actions and leave the form - then I have half a record's worth of data in my tables!

I could just use VB and recordsets to do it but SQL is more elegant. The problem is, when I use the append query it appends an extra record to the table for every single record that already exists within it. So a table with 1500 records will have 1500 new entries containing duplicate data!

This is all a mess, so what would be a better approach? The user does not need to navigate through the records on this form or make edits, it's strictly for additions. There is a subform linked to a table that is in a one-to-many relationship with the table that the parent form must update to, but I'm quite happy to leave that one linked directly.

Any suggestions appreciated I've probably done something thick..

Hi all-

I have a form with an unbound text box which the user enters a date into. Then a button is used to run an append query which adds transactions for that date to a table. I want add a criteria to the append query which will stop it from adding transactions to the table if the date entered on the form is already listed in the table (I am trying to keep them from adding duplicate records to the table).

My SQL is below. Without the criteria the appendquery works. However, when I added in the criteria to check for the date from the form for the same date in the table I get the message that 0 records will be appended to the table even when the date from the form is not listed in the table.

Do I have the criteria wrong? Do you need more information?

	WHERE ([Asset Depreciation Table].ME_DATE)([Forms]![Calculate]![MEDATE]);

I will appreciate all assistance!!

I ran with this idea that I grabbed off this board and now I ran into a dead end...

I wanted to track the history of 2 specific fields on a subform. I saw an example that used an append query to do this. I created an append query that actually appends the entire record into a history table.
The fields in the append query look like this:
Field: Expr1: Forms!sbfPartsInventory_PS!SerialNumber
Append To: SerialNumber
Field:Expr2: Forms!sbfPartsInventory_PS!Date
Append To: TransactionDate
and so on....Expr3 is Status... Expr4 is PartInvID...Expr5 is the PONumber.

On the subform, in the OnChange event of the Date field of the subform
I added code to run the append query. (The user will change the date first and then change part status which is the next field on the subform).
DoCmd.SetWarnings False
DoCmd.OpenQuery "appAppendHistory_PartStatus"
DoCmd.SetWarnings True

Issue #1
When testing this by opening the subform itself....
The append query enters 8-9 entries in the history table. I'm only expecting 1 record to be appended. Not every field is changing. I can't figure out why it's appending that many records at a time. All entries are essentially duplicates except for the date_changed field which is defaulted to now(). Is OnChange the wrong event to use? Or is the append query the wrong approach for this?

Issue #2
When I test this from the parent form, when I try to change the date field I get the "Enter Parameter Value" dialog for each entry in the append query. Do I have to tell the parent form how to reference the subform
in the append query or something? by altering this statement in the subform? Forms!sbfPartsInventory_PS!Date

Any ideas would help!

I have a database whose purpose is to gather and query data from identical linked tables with the same field names. The current process is this:

I have a macro that deletes all data from a temporary table.
I have an input form that the user lists items from one field to get the appropriate records.
I run a union query to gather all the data from all the linked tables.
I run a query to ensure that I get all records listed by the input form.
I then run an append query to append the new data to the temporary table.
That temporary table data is displayed on a form that I use to add some new data into. This data is added into three different tables within the database.
Finally I run a report from the temporary table to sum up all of the work done.

To equate to real world the tables represent inventory lists in different locations, all with unique identification numbers. I have the database pull up all the information from all the tables and the user types in the id numbers they are looking for. Then once they have that data, they add three new bits of information such as where they want it delivered, how many they want and when they want it. I then present a report that is sent to the shipping department to take action.

All that to describe the problem. For some reason or another that I cannot find, I am always one record short on the query. I am using a simple

[Forms]![PCMKIF]![pcmk1] Or [Forms]![PCMKIF]![pcmk2] Or [Forms]![PCMKIF]![pcmk3] Or [Forms]![PCMKIF]![pcmk4]

statement on the query. If I list 3 items on the input form, I get two results. If I list 4 items on the input form I get 3 results.

I have temporarily overcome this by adding a hidden input form box with a default value that will never be used by the inventory system. This allows me to get the right output each time. I would simply like to know what it is I am missing for my own sanity. Thank you for your help and time.

“frm_ENTRY” is showing info from “tbl_ESKER_IMPORT_TEMP”

I want to add a button to “frm_ENTRY” called “ACCEPT” that will run the query “qry_TRANSFER” to send a copy of the data to the table “tbl_TESTS”

I then want it to check off the [COMPLETE] field in “tbl_ESKER_IMPORT_TEMP” so that record will not show up again in the form. (I might need to make an update query to check the box???)

Basically the info in the TEMP table is coming in from an outside source. It usually has a lot of errors so I want my guys to review it, make any changes, then ACCEPT it into our main table. (tbl_TESTS) So my user opens the form, sees one record, makes changes, clicks accept, then it runs the append query to update the record to our table then updates it as complete.

Thank you to anyone who will help. I am sure it will be easy for you guys. Thanks.


I have made a small price checking program that uses 1 form and subform and a few queries to move information from an inventory table to a History table when a user clicks an Add Me control button. The history table can be viewed in the subform. The Inventory table is in the main form.

The Add Me Control simply runs an append query to move the currently viewed item into the history table.

My question is such. On my version of access 2007 I have query warnings turned off, but when I make my install with the dev extensions, specifying to install the run time environment if 2007 isn't detected, and when another user installs on their machine everytime they hit the Add Me button they have the two warnings pop up "You are about to append 1 row" "You cannot undo these changes."

The run time doesn't have options to turn it off, it doesn't have any options really. Is there coding I can use instead of the query to perform this task or is their a way to disable these warnings?

Is it possible to run an append query that only gathers data from one single open form? ie: I have an open form and when I click a button, I want the append query to gather just the names selected on that form, not all the names from every person on every form. If this is possible, can anyone please explain to me how to accomplish this. Thanks for your help. Happy Holidays.

Hi all, I am an amateur access user and am trying to help out friend with a new database. The database records customers. Each customer has unique ID. Each customer has a payments schedule i.e. owes the company money and its due to be paid in installments. I have set up a form for adding new customers, and their agreement payment dates togther with their payment amounts on each date. Each customer has different payment dates and different amounts payable per payment date. There is also a yes no box which records whether a partiular payment has been received or not.

Currently all information is on one table being a customer table. I want to be able to run a report which will identify a customer where a payment date has passed and where payment has not been received. The problem is that by running a query to see if a payment date has passed my query give a result of each customer and beacuse all payment dates are recorded on the customer table all payment dates are displayed for each customer. In other words Im not able to set up an invoicing function that will generate invoices for payment dates passed as it also picks up the future payment dates.

I feel like I should record the payment dates in a seperate table so that my query on that table will only give results of payment dates that are expired. My problem here is that I want the form where a customer is recorded to also record the payment dates. So here is the crux, how can I get a form to record customer details in the custmer table and to record each payment date with a customer ID in a payment dates form?

I think an "append query" running off of the form save button which would take info from text boxes in the form and add it to a payment table might be the solution, but how would I get an append query to make a new record in payments table making a new record for each of the payment dates and also adding the payment amount and the customer ID for each of the payment dates in the payments table?....HELP

I'm not great with Visual basic and therefore I list below the current table fields. I really would appreciate any help with coding!

[Customers].[Customer Name]
[Customers].[1st payment date]
[Customers].[2nd payment date]
[Customers].[3 rd payment date]
[Customers].[1st payment amount]
[Customers].[2nd payment amount]
[Customers].[3rd payment amount]
[Customers].[1st payment amount received yes no]
[Customers].[2nd payment amount received yes no]
[Customers].[3rd payment amount received yes no]

I have created a query that takes parameters from a form, wraps the parameters with "LIKE (*"¶meter&"*)" to search for substrings in the underlying table. The command button on the form Opens the Query which then produces the datasheet view in a NEW TAB.

I'd like to do the same sort of thing with a Split Form.

The Form would have some fields that are taken directly from the selected row at the bottom of the form. And the filter (SQL's Where) uses other text boxes on the form to select which rows should appear at the bottom of the form in the first place.

Operationally, the person doing the search would add tighter and tighter criteria (for the where clause) until the list at the bottom contained the desired record and not too many more. (There are 125K rows to be searched.) He'd then select the appropriate row which would populate many of the form fields. The final step is another button that runs an Append Query to take selected fields from the form (for the selected row) and adds them to another table.

I can't find where to put the selection criteria in the split form. It looks like this should be the perfect solution but I'm missing something. Any help would be appreciated.


Hello me and a few class mates are stuck on a major problem whilst creating a database which shows students and tutors, and the progression they are making during the time at our school. We have four tables they include

Learning Outcome


EnrolementNumber [PK]

SeniorTutor [PK]

All relating together.

The problem is the EnrolementNumber as you can see is the primary key for two tables, the EnrolementNumber is a generated number which takes the year a student started the school and the year they leave e.g. 2012 2013 takes the RIGHT 2 of both being "1213" then adds LEFT 1 of both Surname and Forename in this example using my name Ben Fegan so far its "1213BF" Finally adds a four digit number which is realted to the four digit number in the field above for example 1213BF2001 , the record above would be 1213AD2000.

I have made an append query which in theory changes the necessary fields making the EnrolementNumber is not the problem. I made a form where the user inputs the details via comboboxs which relates to the students table, other than the EnrolementNumber because that is generated but because its the primary key its doesn't allow you to save because there has been a primary key value entered.

Is there any way you can take the values from multiple comboboxes save them on the sly the run an append query which adds the EnrolementNumber before exiting the form, allowing you to save and without the error message Primary Key cannot be a Null Value

Help will be much appriecated if you need an example of the mock up of the database please do ask, Thanks in advance

I'm building a job scheduling database and I'm having a little trouble getting one of the input forms to work as I would like.

The basic idea is that customers book jobs, and I need to be able to assign multiple pieces of equipment and multiple workers to each job. Job table holds all the dates,times,customer details, etc about the job, and then tbljobequipment and tbljobdrivers are my tables to hold the drivers and equipment - they are simple 2 field tables holding jobid and equipmentid (or driverid).

I want the user to be able to select equipment from a drop down list and click a button to add it to the job. As I want multiple entries that rules out a simple bound combo, so I've unbound the combo and set up an append query. It works, but it throws up 2 warning boxes every time its run that will surely scare my users off! Is there any way to turn these off?

Secondly, if a user tries to add a piece of equipment to a job thats already there, it gives a key violation error (which is quite right). Is there any way I can customise the key violation error message to something more meaningful to the user? Thinking about it, perhaps it would be best if I tried a bit of validation code before the append query was run to prevent the message in the first place?

I have a subform displayed on a main form that shows the output from a query. The grid should show the pieces of equipment assigned to a job.

When I create a new job and add a piece of equipment, it doesn't appear in the grid unless I close the form and open it again. If I add another piece of equipment to an existing job it works fine.

If I run the query that holds the data for the grid, the query window shows the equipment that is assigned to that job as it should do, so the data is right but the grid remains blank.

The command button on the form to add the equipment runs an append query to put the data into the table (which it must be doing correctly, because opening the query from the same macro displays the correct data), then it requerys the grid, but still nothing is displayed.

Have I missed something obvious?


I would like to add a record to a table (called Relationship) when the change event fires on a control (Combo8) on my form, based on the value of Combo8 and the value of another control, a TextBox

The two fields in Relationship are Members and Projects. The two values I would like to add to the table are the two current values for these controls.

I tried to do it by running an SQL query in a macro, as follows:
Private Sub Combo8_Change()
Dim YesNoQ
YesNoQ = MsgBox("Do you wish to add this project?", vbYesNo, "Adding projects to member")

If YesNoQ = 6 ThenDim SQL As String
Dim MyDataBase
SQL = "INSERT INTO Relationship (Members, Projects) VALUES (FormsProjectsByMembers!LastNameBox,Forms!Projects ByMembers!Combo8)"
Set MyDataBase = CurrentDb
MyDataBase.Execute CommandText:=SQLElse: End
End If
End SubHowever, this just gives me a run-time error 448: Named argument not found. I know my VBA is poor, especially when it comes to SQL, so there is probably a mistake here.

But am I on the right track, or is there a way to do this without resorting to VBA.

Or can anyone point me to a good article on how to create append queries using values from controls, perhaps, and how to fire them from an event on a form? Just blueskying...

Thanks in advance.

I posted a question last week in query section that SJ MCAbney answered for me. SJ suggested that I use an append query(which, by the way, does exactly what I need it to do) but still having a small problem.

So, here goes. Used a select query to open form by making deliverydate criteria as [Enter day of week]. It pulls out from my table all records with the day I enter...Works great. But, whenever I run the append query,(using a command button that also closes form) I have to enter the day again before it appends the data into the history table.

How do I keep it from asking for the day of week again? I can use it this way but it is being used by another user and I would really like to cut down on mistyping and unnecessay data entry.

Thanks, Noreene

Not finding an answer? Try a Google search.