append query adding all records to table instead of one

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Tony said:
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.
Click to expand...

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.
 
I'm a newbie so I'm not sure how to access the SQL code of a query.
Click to expand...

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.
 
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];
 
Tony said:
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];
Click to expand...

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.
 
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 said:
Tony said:
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];
Click to expand...

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)
Click to expand...
 
Tony said:
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.
Click to expand...

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?
 
Tony said:
The event I use is AfterUpdate. After I edit the record I go to the
next record and go back real quick.
Click to expand...

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.
 
Back
Top