Append Query writing multiple records.

[JJurek]

I have an append query (obviously)

When I click on the button to perform the task, the query reads the data
from the form correctly, however when I look in the table it has written out
the same record the exact same times as the number of previous records.

In other words, before the append query I have 12 records, when I run the
query from the form I then have 24. Likewise if I have 13 records, it shows
26 after the query is run

What did I miss?

-J

-J

 

[John Spencer]

Show us the Append query. It seems probably that you are not limiting the
records to be appended to one, but what is causing that is difficult to say.

I would guess that you have the target table included in the query that is
generating the record.

Perhaps you need to use an alternate version of the append query

INSERT INTO TableA (<<<<LIST OF FIELDS>>>)
VALUES (<<<LIST OF VALUES>>>)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JJurek]

The query is:

INSERT INTO [Licensing Specialist] ( Initials, [First], [Last] )
SELECT DISTINCT [Initial] AS Expr1, [FirstName] AS Expr2, [LastName] AS Expr3
FROM [Licensing Specialist];

The button to call the query is:

Private Sub cmd_AddSpecialist_Click()
On Error GoTo Err_cmd_AddSpecialist_Click

Dim qd As DAO.QueryDef
Dim retval As Variant

Set qd = CurrentDb.QueryDefs("qry_AddLicSpec")

qd!Initial = Me.txt_Inits
qd!FirstName = Me.txt_FirstName
qd!LastName = Me.txt_LastName

qd.Execute dbFailOnError
qd.Close
Set qd = Nothing

retval = MsgBox("Licensing specialist added successfully", vbOKOnly,
"Specialist Added")
Me.txt_Inits = ""
Me.txt_FirstName = ""
Me.txt_LastName = ""


Exit_cmd_AddSpecialist_Click:
Exit Sub

Err_cmd_AddSpecialist_Click:
MsgBox Err.Description
Resume Exit_cmd_AddSpecialist_Click

End Sub

 

[JJurek]

John,

Excellent eye on your part.

I changed the query to:

INSERT INTO [Licensing Specialist] ( Initials, [First], [Last] )
VALUES ([Initial], [FirstName], [LastName]);

Works perfect. Thanks for the insight!

-J

 

[John Spencer]

That query is going to add one record for every record in the table Licensing
Specialist - so it is going to duplicate all the records.

If you wanted to use that to add one record, you would need to add a where
clause restricting the source to one record. For instance it might look like
the following and it would add one record if there was one record in the table
with the initials of JPS

INSERT INTO [Licensing Specialist] ( Initials, [First], [Last] )
SELECT DISTINCT [Initials] AS Expr1, [FirstName] AS Expr2, [LastName] AS Expr3
FROM [Licensing Specialist]
WHERE Initials = "JPS"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County