Insert Record checks table

[pfarnell]

To keep it simple, I have a need to as I insert a numeric record into a table that the form makes a call to say go to Form1 if the number is already in the table or Form2 if the record is not in the table. I have been attepting to do this using an embedded macro based on a form event but with no joy. Any ideas ?

[weekend00]

if dlookup("numfield","table1","numfield=" & aNumber ) = null then
docmd.openform "form1"
else
docmd.openform "form2"
end if

[pfarnell]

I am not quite sure I understand this, which part of this passes the number from the form ?

[weekend00]

suppose aNumber is the object which you have the number in.

[pfarnell]

Can I just clarify, where do I put this code ? Sorry Access new boy.

[weekend00]

To keep it simple, I have a need to as I insert a numeric record into a table that the form makes a call to say go to Form1 if the number is already in the table or Form2 if the record is not in the table. I have been attepting to do this using an embedded macro based on a form event but with no joy. Any ideas ?

How did you insert a numeric record into a table ? you need to put the code before you insert a record.

[pfarnell]

I am putting it as an On Exit event on a field, so I barcode in the number and the barcode is set to tab. Code in code builder looks like this.

Private Sub DelegateReferenceNo_Exit(Cancel As Integer)
If DLookup("DelegateReferenceNo", "CheckIn", "DelegateReferenceNo = [DelegateCheckIn]![DelegateReferenceNo]") = Null Then
DoCmd.OpenForm "GoodBadge"
Else
DoCmd.OpenForm "DuplicateBadge"
End If
End Sub

[weekend00]

Did you get it work?
Or more question?

[pfarnell]

No it did not work, seems not to want to user the form field bit.

[ajetrumpet]

No it did not work, seems not to want to user the form field bit.

farnell,

maybe I can issue some help here - are you scanning badges into the system's log when employees enter a corporate building perhaps?

if so, I'm not sure why you have two tables. But at any rate, weekend gave you a domain function, which has a syntax that is relevant to your situation such that:

Private Sub DelegateReferenceNo_Exit(Cancel As Integer)

If DLookup("DelegateReferenceNo", "CheckIn", _
"CRITERIA HERE") Then
DoCmd.OpenForm "GoodBadge"
Else
DoCmd.OpenForm "DuplicateBadge"
End If
End Sub

To me, the following line of code is unclear:

"[DelegateReferenceNo] = [DelegateCheckIn]![DelegateReferenceNo]") = Null

what are you trying to do with that? What is [DelegateCheckIn]!?? Is that a form reference? A table? A query?

Just a little more tweaking and you should have it no problem.

[pfarnell]

Only got one table "CheckIn", "DelegateCheckIn" is a form.

[ssanfu]

if dlookup("numfield","table1","numfield=" & aNumber ) = null then
docmd.openform "form1"
else
docmd.openform "form2"
end if

The problem is with "DLookup(blah) = NULL" . Nothing is equal to NULL, not even NULL. If you write

If NULL=NULL Then
MsgBox "True"
Else
MsgBox "False"
End if


This will *always* display FALSE.

Try:

IF ISNULL(Dlookup("numfield","table1","[numfield] = " & aNumber )) THEN
DoCmd.Openform "form1"
ELSE
DoCmd.Openform "form2"
END IF


HTH
----
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

[pfarnell]

I have the following now but it always goes to "duplicate badge" despite it not having been checked in before.

Private Sub DelegateReferenceNo_Exit(Cancel As Integer)
If IsNull(DLookup("DelegateReferenceNo", "CheckIn", "DelegateReferenceNo = [Forms].[BadgeProduction]![DelegateReferenceNo]")) Then
DoCmd.OpenForm "GoodBadge"
Else
DoCmd.OpenForm "DuplicateBadge"
End If
End Sub

[ssanfu]

The Where clause is looking for "[Forms].[BadgeProduction]![DelegateReferenceNo]" in the field "DelegateReferenceNo". DLookup does not/cannot evaluate the reference to the control within the quotes.

Try:

If IsNull(DLookup("DelegateReferenceNo", "CheckIn", "DelegateReferenceNo = " &[Forms]![BadgeProduction].[DelegateReferenceNo])) Then


HTH
----
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)