How do you reference a field in a query from a recordset? (1 Viewer)

[GD1977]

All-
I am attempting to run through a recordset and use some select queries to find out if a particular record exists, and then if it does, to update it. I was wondering how I could pass a value within the recordset to one of the select queries. I have it loosely set up as:
Dim chk1 As DAO.Recordset
Set chk1 = CurrentDb.OpenRecordset("_Check1")
If chk1.RecordCount > 0 Then
chk1.MoveFirst
Do Until chk1.EOF

'--> This is where I am having trouble - there is a field in the recordset, chk1!OID, that I need to link to the query _Check2 - how do I do that?
If DCount("*", "_Check2") > 1 then
Run Insert
Else
Run Update
Endif
chk1.MoveNext
Loop
End If
chk1.Close

How can I use a value from the chk1 recordset in the _Check2 query? What do I put into the criteria? Help is appreciated, thanks

 

[Peter D]

...
If DCount("*", "Select * from _Check2 Where MyField = " & chk1!OID) > 1 then
...

... is one possible way to do it.

 

[vbaInet]

Slight correction:

If DCount("*", "_Check2", "MyField = " & chk1[COLOR=Red]![/COLOR]OID) > 1 then

But the exclamation mark is not a valid character for a field name. So substitute chk!OID for the right field name.

By the way if the field is a Text datatype, you need to enclose it in quotes.

NB: SQL statements is not valid in an aggregate function.