If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
append query adding all records to table instead of one
I have an append query that will add a record into a Log table each time a corresponding record is edited through a form bound to another table. The problem is that each time I edit a record, all other records in that table are appended in the Log table.
|
#2
|
|||
|
|||
append query adding all records to table instead of one
"Tony" wrote in message
I have an append query that will add a record into a Log table each time a corresponding record is edited through a form bound to another table. The problem is that each time I edit a record, all other records in that table are appended in the Log table. Presumably the WHERE clause of the append query doesn't successfully filter the table for the record you want. Please post the SQL of the query, and tell how the query is supposed to recognize the record to be appended. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
append query adding all records to table instead of one
I'm a newbie so I'm not sure how to access the SQL code of a query.
"Dirk Goldgar" wrote: "Tony" wrote in message I have an append query that will add a record into a Log table each time a corresponding record is edited through a form bound to another table. The problem is that each time I edit a record, all other records in that table are appended in the Log table. Presumably the WHERE clause of the append query doesn't successfully filter the table for the record you want. Please post the SQL of the query, and tell how the query is supposed to recognize the record to be appended. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#4
|
|||
|
|||
append query adding all records to table instead of one
On Mon, 5 Jul 2004 10:24:02 -0700, "Tony"
wrote: I'm a newbie so I'm not sure how to access the SQL code of a query. Open the Query in design view and either select SQL from the dropdown of the leftmost tool in the toolbar (the options are the triangle and straitedge Design view, the datasheet Data view, and the word SQL); or select View... SQL from the menu. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#5
|
|||
|
|||
append query adding all records to table instead of one
Thank You...here is the SQL
INSERT INTO [Log table] ( [Circuit ID], [Billing #], [Circuit Type], [Company Name], [Vendor Name], [Regional PICC Code], [LD PICC Code], [Circuit Status], [Install Date], [Circuit Owner], Department, [Contract Length], [Location Code], MRC, NRC, [Last Update], Name, Notes ) SELECT [Circuit Info Table].[Circuit ID], [Circuit Info Table].[Billing #], [Circuit Info Table].[Circuit Type], [Circuit Info Table].[Company Name], [Circuit Info Table].[Vendor Name], [Circuit Info Table].[Regional PICC Code], [Circuit Info Table].[LD PICC Code], [Circuit Info Table].[Circuit Status], [Circuit Info Table].[Install Date], [Circuit Info Table].[Circuit Owner], [Circuit Info Table].Department, [Circuit Info Table].[Contract Length], [Circuit Info Table].[Location Code], [Circuit Info Table].MRC, [Circuit Info Table].NRC, [Circuit Info Table].[Last Update], [Circuit Info Table].Name, [Circuit Info Table].Notes FROM [Circuit Info Table]; "John Vinson" wrote: On Mon, 5 Jul 2004 10:24:02 -0700, "Tony" wrote: I'm a newbie so I'm not sure how to access the SQL code of a query. Open the Query in design view and either select SQL from the dropdown of the leftmost tool in the toolbar (the options are the triangle and straitedge Design view, the datasheet Data view, and the word SQL); or select View... SQL from the menu. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#6
|
|||
|
|||
append query adding all records to table instead of one
"Tony" wrote in message
Thank You...here is the SQL INSERT INTO [Log table] ( [Circuit ID], [Billing #], [Circuit Type], [Company Name], [Vendor Name], [Regional PICC Code], [LD PICC Code], [Circuit Status], [Install Date], [Circuit Owner], Department, [Contract Length], [Location Code], MRC, NRC, [Last Update], Name, Notes ) SELECT [Circuit Info Table].[Circuit ID], [Circuit Info Table].[Billing #], [Circuit Info Table].[Circuit Type], [Circuit Info Table].[Company Name], [Circuit Info Table].[Vendor Name], [Circuit Info Table].[Regional PICC Code], [Circuit Info Table].[LD PICC Code], [Circuit Info Table].[Circuit Status], [Circuit Info Table].[Install Date], [Circuit Info Table].[Circuit Owner], [Circuit Info Table].Department, [Circuit Info Table].[Contract Length], [Circuit Info Table].[Location Code], [Circuit Info Table].MRC, [Circuit Info Table].NRC, [Circuit Info Table].[Last Update], [Circuit Info Table].Name, [Circuit Info Table].Notes FROM [Circuit Info Table]; This is fine so far as it goes, Tony, but there's nothing in the query that would limit the records selected from [Circuit Info Table] to just the one that was edited on your form. If CircuitID is the primary key of [Circuit Info Table], then you might amend your query to refer to that field on the form in a WHERE clause, like this: INSERT INTO [Log table] ( [Circuit ID], [Billing #], [Circuit Type], [Company Name], [Vendor Name], [Regional PICC Code], [LD PICC Code], [Circuit Status], [Install Date], [Circuit Owner], Department, [Contract Length], [Location Code], MRC, NRC, [Last Update], [Name], Notes ) SELECT [Circuit ID], [Billing #], [Circuit Type], [Company Name], [Vendor Name], [Regional PICC Code], [LD PICC Code], [Circuit Status], [Install Date], [Circuit Owner], Department, [Contract Length], [Location Code], MRC, NRC, [Last Update], [Name], Notes FROM [Circuit Info Table] WHERE [Circuit ID]=[Forms]![CircuitInfoForm]![Circuit ID]; That's assuming that your form is named "CircuitInfoForm", and it has a control on it named "Circuit ID" that currently holds the primary key of the record you want to append. My reformatting of your SQL is just for clarity and convenience. The WHERE clause is the only significant change. However, you should be able to copy and paste my revision into the SQL View of your query, correct the form and control names, and have it work. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#7
|
|||
|
|||
append query adding all records to table instead of one
I copy & pasted your code and adjusted the names of the form and control, but now for some reason it seems the query won't run because the table is not appended.
"Dirk Goldgar" wrote: "Tony" wrote in message Thank You...here is the SQL INSERT INTO [Log table] ( [Circuit ID], [Billing #], [Circuit Type], [Company Name], [Vendor Name], [Regional PICC Code], [LD PICC Code], [Circuit Status], [Install Date], [Circuit Owner], Department, [Contract Length], [Location Code], MRC, NRC, [Last Update], Name, Notes ) SELECT [Circuit Info Table].[Circuit ID], [Circuit Info Table].[Billing #], [Circuit Info Table].[Circuit Type], [Circuit Info Table].[Company Name], [Circuit Info Table].[Vendor Name], [Circuit Info Table].[Regional PICC Code], [Circuit Info Table].[LD PICC Code], [Circuit Info Table].[Circuit Status], [Circuit Info Table].[Install Date], [Circuit Info Table].[Circuit Owner], [Circuit Info Table].Department, [Circuit Info Table].[Contract Length], [Circuit Info Table].[Location Code], [Circuit Info Table].MRC, [Circuit Info Table].NRC, [Circuit Info Table].[Last Update], [Circuit Info Table].Name, [Circuit Info Table].Notes FROM [Circuit Info Table]; This is fine so far as it goes, Tony, but there's nothing in the query that would limit the records selected from [Circuit Info Table] to just the one that was edited on your form. If CircuitID is the primary key of [Circuit Info Table], then you might amend your query to refer to that field on the form in a WHERE clause, like this: INSERT INTO [Log table] ( [Circuit ID], [Billing #], [Circuit Type], [Company Name], [Vendor Name], [Regional PICC Code], [LD PICC Code], [Circuit Status], [Install Date], [Circuit Owner], Department, [Contract Length], [Location Code], MRC, NRC, [Last Update], [Name], Notes ) SELECT [Circuit ID], [Billing #], [Circuit Type], [Company Name], [Vendor Name], [Regional PICC Code], [LD PICC Code], [Circuit Status], [Install Date], [Circuit Owner], Department, [Contract Length], [Location Code], MRC, NRC, [Last Update], [Name], Notes FROM [Circuit Info Table] WHERE [Circuit ID]=[Forms]![CircuitInfoForm]![Circuit ID]; That's assuming that your form is named "CircuitInfoForm", and it has a control on it named "Circuit ID" that currently holds the primary key of the record you want to append. My reformatting of your SQL is just for clarity and convenience. The WHERE clause is the only significant change. However, you should be able to copy and paste my revision into the SQL View of your query, correct the form and control names, and have it work. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#8
|
|||
|
|||
append query adding all records to table instead of one
"Tony" wrote in message
I copy & pasted your code and adjusted the names of the form and control, but now for some reason it seems the query won't run because the table is not appended. That's two different things, actually: the query not running, as opposed to the query running but not appending anything. If you didn't get any error message, I'd guess that the query actually ran, but no record was selected by the WHERE clause. What was the state of the form when you ran the query? Was it displaying the record that you wanted to append? What event of the form did you use to run the query? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#9
|
|||
|
|||
append query adding all records to table instead of one
The event I use is AfterUpdate. After I edit the record I go to the next record and go back real quick.
"Dirk Goldgar" wrote: "Tony" wrote in message I copy & pasted your code and adjusted the names of the form and control, but now for some reason it seems the query won't run because the table is not appended. That's two different things, actually: the query not running, as opposed to the query running but not appending anything. If you didn't get any error message, I'd guess that the query actually ran, but no record was selected by the WHERE clause. What was the state of the form when you ran the query? Was it displaying the record that you wanted to append? What event of the form did you use to run the query? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#10
|
|||
|
|||
append query adding all records to table instead of one
"Tony" wrote in message
The event I use is AfterUpdate. After I edit the record I go to the next record and go back real quick. Hmm, I don't see why that wouldn't work. I take it you mean you go to the next record so as to make Access save the updated record and trigger the AfterUpdate event. Of course, that would only fire if you had in fact changed the value of some bound control on the form. Would you mind posting the SQL as it is now? I'm temporarily stumped. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
Transfer Query Info. To Table | Delano | General Discussion | 1 | June 18th, 2004 11:52 PM |
Modifying table design breaks Append query | MKH | Database Design | 2 | June 15th, 2004 04:05 PM |
surely a form with a ListBox can be used in a query? | 1.156 | Running & Setting Up Queries | 14 | June 2nd, 2004 04:54 PM |
Adding records to a query? | Mike J | Running & Setting Up Queries | 0 | May 23rd, 2004 09:36 PM |