Check for a value in the results of a query

[magicdds-]

I have a query with a number of resulting records. I need to check if PARTYID
in any of those records equals PARTYID in my subform.

I was going to put code in the AfterUpdate property of PARTYID on the
subform to check the list of records returned by the query. I know I can
check the first record with a DLookUp statement, but I don't know how to
scroll through all of the records returned by the query, to check the results.

Can anyone suggest how to do this.

Thanks,
Mark

 

[George Nicholson]

I know I can check the first record with a DLookUp statement...

You are thinking of DFirst(). DLookup looks at all the records in the
table/query and returns a value *if* a matching record is found, so you
should be able to use it for what you want. (aircode follows)

lngResult = Nz(Dlookup("[PartyID]", "qrySomeQuery","[PartyID] = " &
Form!SubformControl!Form!PartyIDControl),0)
If lngResult = 0
' Dlookup didn't find a match, so it returned Null. We changed Null to
Zero to avoid a TypeMismatch error.
MsgBox "No Matching Record"
Else
Msgbox "Match found"
End If

 

[magicdds-]

With a little tweeking, I got it to work.
Thanks George

I know I can check the first record with a DLookUp statement...

You are thinking of DFirst(). DLookup looks at all the records in the
table/query and returns a value *if* a matching record is found, so you
should be able to use it for what you want. (aircode follows)

lngResult = Nz(Dlookup("[PartyID]", "qrySomeQuery","[PartyID] = " &
Form!SubformControl!Form!PartyIDControl),0)
If lngResult = 0
' Dlookup didn't find a match, so it returned Null. We changed Null to
Zero to avoid a TypeMismatch error.
MsgBox "No Matching Record"
Else
Msgbox "Match found"
End If

--
HTH,
George

I have a query with a number of resulting records. I need to check if
PARTYID
in any of those records equals PARTYID in my subform.

I was going to put code in the AfterUpdate property of PARTYID on the
subform to check the list of records returned by the query. I know I can
check the first record with a DLookUp statement, but I don't know how to
scroll through all of the records returned by the query, to check the
results.

Can anyone suggest how to do this.

Thanks,
Mark