Check for Record with Access 2

[jeffshex]

I need someone to point me in the right direction here.
I want to have some code fire in a before update event that looks at a table to see if a specific ID already exists.
I will probably have it grab the ID from the other form that's open.
How do I go about this? Is it a rows returned kind of thing or a DLookup?

Ideas?

 

[Remou]

If any instance of the ID is sufficient, then DlookUp should suit.

 

[jeffshex]

I have this working, but it seems to never roll over to the else statement, whether the condition is satisfied or not.

If IsNull(DLookup("[ComponentEnrolled]", "tblComponents", "[SS#] = " & Me.[SS#])) Then
        MsgBox "Advise Component Here", vbOKOnly, "Warning!"
    Else
        MsgBox "This person already exists.", vbOKOnly, "Test"
    End If

Is this just how IsNull works or what?
What a bugger.

 

[PHV]

What about this ?

If IsNull(DLookup("[ComponentEnrolled]", "tblComponents", "[SS#] = [!]'[/!]" & Me.[SS#] [!]& "'"[/!])) Then
    MsgBox "Advise Component Here", vbOKOnly, "Warning!"
Else
    MsgBox "This person already exists.", vbOKOnly, "Test"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jeffshex]

That did it!
What exactly did adding that in do?
Was the previous way not passing the criteria properly?

 

[PHV]

I guessed that SS# was defined as text in tblComponents and thus showed you how to deal with non numeric criteria.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886