Need help with append query, please

[EManning]

Using A2K. I've got a subform that displays a list of agencies by date for
each client. For example, currently there are 10 agencies. Client #59 has
30 agency records: one set for 6/22/2005, one set for 7/1/2005, and one set
for 7/15/2005. If the user adds an agency for #59 on the 6/22/2005 set, an
append query is created in the AfterUpdate of the agency name to append this
new agency to all other clients. Here's the query:

INSERT INTO tblAgencyAssistance ( ID, AgencyAssistance, AssistanceDate,
AssistanceAmount )
SELECT DISTINCT tblAgencyAssistance.ID, "AAA" AS Expr1,
tblAgencyAssistance.AssistanceDate, 0 AS Expr2
FROM tblAgencyAssistance
WHERE (((tblAgencyAssistance.ID)<>59) AND
((tblAgencyAssistance.AssistanceDate)<>#6/22/2005#));

This WHERE clause ignores all records for #59. However, there should be a
new record appended for 7/1/2005 and a new record for 7/15/2005. If I don't
use the clause I get duplicate 6/22/2005 records for #59.

Can anyone tell me what I'm doing wrong? Below is a sample of
tblAgencyAssistance if that helps:

ID AgencyAssistance AssistanceDate AssistanceAmount
59 Dental Assistance 6/22/2005 $6.00
59 Dental Assistance 7/1/2005 $10.00
59 Dental Assistance 7/15/2005 $0.00
60 Dental Assistance 6/1/2005 $14.00
60 Dental Assistance 7/1/2005 $15.00
61 Dental Assistance 7/1/2005 $0.00


Thanks for any help or advice.

 

[Michel Walsh]

Hi,


Other relations may be violated... or something like that. Turn on warnings,
or use the optional flag dbFailOnError:


CurrentDb.Execute strSQL, dbFailOnError


if you execute the statement within VBA.



Hoping it may help,
Vanderghast, Access MVP