Simple If statement Using DlookUp

[darinmc]

Hi, Im trying to check a value in a table, EmpRegNo. I'm using Dlookup to check whether its there or not.
If the value is there, then I want a form to open! If not, then a msgBox to appear...

I placed a txt Box as a test to see if the dlookup was working, and the EmpregNo was been placed in it BUT my if Statement doesnt appear to work???
Sorry about this simple question

Private Sub cmdAbsences_Click()
Dim varX As String

varX = Nz(DLookup("[tEmpReg]", "tblAbsences", "[tEmpReg] = Forms![frmEmployeeNEW]!EmpRegNo"))

Me.test = varX

If Forms![frmEmployeeNEW]!EmpRegNo = Me.test Then
MsgBox "test"
On Error GoTo Err_cmdAbsences_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmAbsences"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAbsences_Click:
    Exit Sub

Err_cmdAbsences_Click:
    MsgBox Err.Description
    Resume Exit_cmdAbsences_Click
Else

End If
End Sub

Thx
Darin

 

[Remou]

You would be better posting in Forum702.

Private Sub cmdAbsences_Click()
Dim varX As String

On Error GoTo Err_cmdAbsences_Click

varX = Nz(DLookup("[tEmpReg]", "tblAbsences", "[tEmpReg] = Forms![frmEmployeeNEW]!EmpRegNo"))

Me.test = varX

If Forms![frmEmployeeNEW]!EmpRegNo = Me.test Then
MsgBox "test"

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmAbsences"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_cmdAbsences_Click:
    Exit Sub

Err_cmdAbsences_Click:
    MsgBox Err.Description
    Resume Exit_cmdAbsences_Click
End Sub

The above is still all over the place, but should work.

 

[darinmc]

Thx Remou
I did a little bit of fiddleing and was still getting a few problems, the if statements weren't working when they should be. I changed the Beginning of the if statement to:
If varX... etc this seemed to correct everything.

Private Sub cmdAbsences_Click()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, StrFile, stDocName, stLinkCriteria
Dim varX As Variant
    'Dim stDocName As String
    'Dim stLinkCriteria As String
On Error GoTo Err_cmdAbsences_Click

Msg = "There are NO ABSENCE ENTRIES for this employee!!!" & vbCr & "     Do you need to CREATE an entry???   ' Define message."
Style = vbYesNo + vbQuestion + vbDefaultButton2    ' Define buttons.
Title = "Absence"    ' Define title.
Help = "DEMO.HLP"    ' Define Help file.
Ctxt = 1000    ' Define topic


varX = DLookup("[tEmpReg]", "tblAbsences", "[tEmpReg] = Forms![frmEmployeeNEW]!EmpRegNo")
varX = IIf(IsNull(varX), 0, varX)
Me.test = varX


If varX > 0 Then 'Forms![frmEmployeeNEW]!EmpRegNo
    stDocName = "frmAbsences"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
        'MsgBox " test"
        If Response = vbYes Then    ' User chose Yes.
            'MyString = "Yes"    ' Perform some action.
            stDocName = "frmAbsences"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else    ' User chose No.
         'MyString = "No"    ' Perform some action.
        End If

End If

Exit_cmdAbsences_Click:
    Exit Sub

Err_cmdAbsences_Click:
    MsgBox Err.Description
    Resume Exit_cmdAbsences_Click

End Sub

Thx for help