How to perform an action query-like operation in VBA?

[rocklee]

I need to add a bunch of record from table 1 to table 2 provided that they don't already exist in table 2.

I originally had an action query to append records from table 1 into table 2 plus some additional info, but this doesn't check for duplicates. I will need to do a separate process to check that the record doesn't already exist in table 2. This sounds like 2 query processes.

Do I have to create a query looping through records in table 1 before appending them to table 2? or can I do this with action (append) query?

 

[jzwp22]

If I understand you correctly, you should be able to do this by using an unmatched query as part of your append query. Use the unmatched query wizard available in Access to find those records that occur in table 1 but not in table 2.

This query returns the records in table 1 that do not have matching records in table 2 via the last name field

query name: [Table1 Without Matching Table2]
SELECT Table1.txtLastName, Table1.txtAddr, Table1.txtCity
FROM Table1 LEFT JOIN Table2 ON Table1.txtLastName = Table2.txtLastName
WHERE (((Table2.txtLastName) Is Null));

Now use the above query to build your append query

INSERT INTO Table2 ( txtLastName, txtAddr, txtCity )
SELECT [Table1 Without Matching Table2].txtLastName, [Table1 Without Matching Table2].txtAddr, [Table1 Without Matching Table2].txtCity
FROM [Table1 Without Matching Table2];

 

[rocklee]

Thanks for the quick reply.

After playing around with the query wizard and unmatched query, I think I understand a little more about what it does. But I'll explain more about what I'm trying to do.

Basically the records (class list) that I want to append to table 2 (attendance list) are appended according to a date.

This date is created on the form which runs the action query to create the new class list. New students are added to the class list which will be appended whenever a class/date is created.

The problem is if the user tries to run this again with the same date, it will create duplicates.

I need to be able to add a new class according to the date created without duplicating the records again (as append would do).

I don't know how to interpret that using unmatched query.

 

[jzwp22]

Regarding this statement:

This date is created on the form which runs the action query to create the new class list. New students are added to the class list which will be appended whenever a class/date is created.

Are you running the append query via a button on the form that supplies the date in addition to other field information? What code do you have so far?

I am thinking that you can use an IF...THEN in conjunction with the DCount() function to determine whether the append should be executed or not. Execute if the DCount=0; don't execute if DCount>0.

I would have to know more about how you are doing this to be able to help further. Could you post your database with any sensitive data removed?

 

[Insane_ai]

Use the unmatched query as your source for the Append query.


pseudocode:
INSERT INTO

(field1,field2)
SELECT (Field1,field2) From [Unmatached Query]

 

[rocklee]

Regarding this statement:



Are you running the append query via a button on the form that supplies the date in addition to other field information? What code do you have so far?

I am thinking that you can use an IF...THEN in conjunction with the DCount() function to determine whether the append should be executed or not. Execute if the DCount=0; don't execute if DCount>0.

I would have to know more about how you are doing this to be able to help further. Could you post your database with any sensitive data removed?

Hi jzwp22,

Yes I am trying to run the append query via a button that supplies the date.

I created an alternate solution by doing a table lookup for existing data with this :

Set MyDB = CurrentDb()

strsql = "SELECT * FROM [Activity Attendance] WHERE [Activity ID] = '" & Forms![Manage - session - add students].[search_activity] & "' AND [AttendanceDate] = '" & Forms![Manage - session - add students].[createdate].Value & "'"

Set rsc = MyDB.OpenRecordset(strsql)

If rsc.EOF Then
    MsgBox ("Adding new class!")
    DoCmd.OpenQuery "a_attendance Query", acViewNormal, acEdit
Else
    MsgBox ("This record already exist!")
End If

Insane_ai - I want to add the class list according to the activity and registered list which may occur several times in table 2 however the dates should be different.

 

[jzwp22]

Glad to hear that you worked out a solution; good luck on your project.