VBA Question on Comparing fields and updating

[daveofgv]

Hello All -

I have a quick question that I need help with. I am a VB.NET programmer and don't know much about VBA....

I have two tables - first table has a column with file names, a column with file names after being renamed and a status column...



Second table has a column with new file names.

(I have a directory that I renamed each file with a unique name 0000001.tif, 0000002.tif, etc....)

All I need to do is create a button on a form that will look at table 2 and if the file name is there it will update the status column on table 1 (I have all 0's in it and if the file name on table 2 is there then the status field will change from 0 to 1).

Hope this makes sense.

Can anyone help?

Thanks in advanced

[daveofgv]

After playing around with access a little more I found out how to do it - don't know exactly how....however, it's working

Thanks to anyone that has read this post.

It can be closed.

[June7]

Not clear what you need.

You want to check if each value in Table2 is in Table1 (the Renamed field?) and if there is a match update status on Table1?

That should be a straightforward UPDATE query.

Start by creating a SELECT query that joins the two tables on the common field. Jointype 'show records where both values are equal'. Then switch the query to an UPDATE type. Under Status field on the UPDATE TO row put the status value you want. The SQL would look like:
UPDATE Table2 INNER JOIN Table1 ON Table2.NameRev = Table1.NameRev SET Table1.Status = "y";

EDIT: I was composing reply as you posted. Glad you found solution.

The status field is a text or number field, not a Yes/No? Access uses -1 as Yes and 0 as No in Yes/No type field.