Check if data in Tabl1 matches with any of the data in Table2 using VBA

[Derek]

Hi All

Please see attached the workbook. There are two tabs named Table1 and Table2. In actual there are two tables in Access database named Table1 and Table2. So just to give u an idea how the data is stored in ACcess tables, I have made two tabs in excel workbook.

Now I want Access VBA code that will check if data in Reference field of Table1 matches with any of the data in Reference field of Table2. If it matches then change the status of the corresponding record of Table2 with either "Withdrawn","Obsolete" or "Updated". SO it depends upon which field out of "WIthdrawn","Obsolete" and "Updated" in Table1 stores "Y". At a time only one of them will have "Y" and rest of two fields will have "N" as shown in the sheets.

As in the example, now Reference "R566" of Table1 matches with Table2 Reference so the status field in Table2 for that record will be "WithDrawn".

ANy help would be much appreciated.

Thanks

 

[CJ_London]

You would normally do this with an update query - which you can then execute from VBA if required

The query would be something like

Update Table2  SET Status=switch(withdrawn='Y','Withdrawn',Obsolete='Y','Obsolete',Updated='Y','Updated')
FROM Table2 INNER JOIN Table1 ON Table2.Reference=Table1.Reference
WHERE Instr(withdrawn & obsolete & updated,'Y')>0

I guess you have your own handling for more than one Y in table1 - this code updates on the basis of the first Y found and pulls through only those records in table1 where there is a 'Y'

 

[Derek]

Thanks CJ_London. so how to execute this query using VBA?? ACtually I want the Update query to run when a button is clicked on the form. Can you pls help me in this??

 

[Derek]

If i write the follwowing code then it gives me syntex error:

Private Sub Command5_Click()
Dim ssql As String
ssql = "Update Table2 SET Status=switch(withdrawn='Y','Withdrawn',Obsolete='Y','Obsolete',Updated='Y','Updated') FROM table2 INNER JOIN Table1 ON table2.Reference=Table1.Reference WHERE Instr(withdrawn & obsolete & updated,'Y')>0"
CurrentDb.Execute ssql, dbFailOnError
End Sub

 

[CJ_London]

see you got the vba sorted, with regards the syntax error, I typed it freehand so it is not tested

Suggest you copy the code into a query and run it there and see what error is generated

 

[CJ_London]

just created a dummy update query and the frm is slightly different, try

ssql = "Update  table2 INNER JOIN Table1 ON table2.Reference=Table1.Reference SET Status=switch(withdrawn='Y','Withdrawn',Obsolete='Y','Obsolete',Updated='Y','Updated') WHERE Instr(withdrawn & obsolete & updated,'Y')>0"

 

[Derek]

Thanks a lot CJ_london. That works like a charm.

 

[Derek]

Thanks CJ_London, My next query is, when the data gets updated in Table2 then Update_ID field of Table should store the same ID for all the records which are updated at a single transaction. Suppose 5 records are Updated then Update_ID field should store P1 for all those 5 records.

in the next Update , suppose 3 records are updated then Update_ID field should store P2 for those 3 records.

So there should be one table TblUpdate with the fields ID,UpDate_ID,Date,Username

Please see attached the workbook showing all the tables and how data is stored in the those Access tables.

 

[CJ_London]

Where is the P1, P2 coming from? Please explain more detail

 

[Derek]

The table tblUpdate will store UpDate_ID starting from P1,P2 and so on. so whenever Update button is clicked then first of all tblUpdate should store all the details when the update occurs like Date,Username and the Update_ID and then Table2 gets Updated and also at that time the same Update_ID from tblUpdate will be stored in all the records which get updated in Table2.

I hope I made it clear.

 

[CJ_London]

So I repeat: Where is the P1, P2 coming from? is it a made up value or from another table or what?

 

[Derek]

Its a made up value and its made every time update occurs. When Update button is clicked then the tblUpdate stores new Update details and then Table2 gets updated. in the tblupdate if ID=1 then Update_ID will be P1, if ID=2 then Update_ID will be P2. ID field has Autonumber data type. Update_ID will be Text data type.

 

[Derek]

Please have a look at the attachment as well. Now there are 3 tabs i.e Table1,Table2 and tblUpdate.

 

[smig]

Lets go basic.
Create a select query (use the query builder) and join the fields you want to match. By this you will have only the records that the joined fields are matched.
After you did this change th we query into an update query.

 

[Derek]

Hello CJ_London, Can you please help me in this? How to put Update_ID as well in the table?? Thanks

 

[CJ_London]

to calculate your updateID you would need something like

UpdateID:"P" & DMax("Val(mid(updateID,2))","Table2")+1