Incorrect use of DLookup in if statement VBA MSAccess

Question

I am trying to use an if statement to determine if a text field contains a value from a database. Except I get an error when nothing is entered.

Here is the code:

Private Sub btnLogEntry_Click()


If Me.txtMemberID.Value <> "" And DLookup("MemberID", "Member", "MemberID = " & Me.txtMemberID.Value & "") > 0 Then
MsgBox "Entry Successful", vbInformation, "Alert"
MsgBox "Welcome", vbInformation, "Alert"
TempVars!activtyMemberID = Me.txtMemberID.Value
TempVars!activityTime = Me.txtTime.Value
TempVars!activityAccessType = Me.txtAccessType.Value
TempVars!activityFranchiseID = DLookup("[FranchiseID]", "Member", "MemberID = " & Me.txtMemberID.Value)
DoCmd.RunSQL "INSERT INTO GymActivity VALUES (Tempvars!activtyMemberID, 
Tempvars!activityTime, Tempvars!activityAccessType, 
Tempvars!activityFranchiseID)"

DoCmd.Close
DoCmd.OpenForm "frmMainPage"
Else
MsgBox "Please re-enter your Member ID"

End If
End Sub

Here is the error I receive: https://imagebin.ca/v/3ZBvRiXlZM2t

Spelling error on `activtyMemberID` ! You really sould use `option explicit` at top of your module and declare your variables to avoid that kind of errors — iDevlop, Sep 04 '17 at 07:00

Bozhidar Stoyneff · Accepted Answer · 2017-09-04T11:06:37.583

The problem is with your edge case. I.e. when the txtMemberID.Text == "".

Do note that VBA have a very limited support of lazy evaluation - i.e. evaluation of nested conditions, only if the evaluated ones have passed the check so far - and the And operator does not fall in this support, meaning all the conditions, concatenated with the And statement are evaluated in order of appearance, even if the first (leftmost) ones in the line result into False.

So when there is no text in the txtMemberID textbox, the DCount(... function is still evaluated and ... bang you'll end up with malformed expression for criteria; just as shown in the error message.

Two options are available to overcome this situation. The lazy evaluation is supported only within the Select Case statement.

The other option is to nest your If statements, like so:

Private Sub btnLogEntry_Click()

If Me.txtMemberID.Value <> "" Then
    If DLookup("MemberID", "Member", "MemberID = " & Me.txtMemberID.Value & "") > 0
        MsgBox "Entry Successful", vbInformation, "Alert"
        MsgBox "Welcome", vbInformation, "Alert"
        TempVars!activtyMemberID = Me.txtMemberID.Value
        TempVars!activityTime = Me.txtTime.Value
        TempVars!activityAccessType = Me.txtAccessType.Value
        TempVars!activityFranchiseID = DLookup("[FranchiseID]", "Member", "MemberID = " & Me.txtMemberID.Value)
        DoCmd.RunSQL "INSERT INTO GymActivity VALUES (Tempvars!activtyMemberID, 
        Tempvars!activityTime, Tempvars!activityAccessType, 
        Tempvars!activityFranchiseID)"

        DoCmd.Close
        DoCmd.OpenForm "frmMainPage"
    Else
        MsgBox "The specified MemberID not found."
    End If
Else
    MsgBox "Please re-enter your Member ID"    
End If

End Sub

Gustav · Answer 2 · 2017-09-04T06:55:37.063

0

You should take advantage of the fact, that DLookup returns Null for "not found" and look up FranchiseID in one go:

Private Sub btnLogEntry_Click()

    Dim FranchiseID As Variant

    If Not IsNumeric(Me.txtMemberID.Value) Then
        ' None or invalid user input. Exit.
        MsgBox "A member ID is required."
        Exit Sub
    End If

    FranchiseID = DLookup("[FranchiseID]", "Member", "MemberID = " & Me.txtMemberID.Value & "")

    If Not IsNull(FranchiseID) Then
        MsgBox "Entry Successful", vbInformation, "Alert"
        MsgBox "Welcome", vbInformation, "Alert"

        TempVars!activtyMemberID = Me.txtMemberID.Value
        TempVars!activityTime = Me.txtTime.Value
        TempVars!activityAccessType = Me.txtAccessType.Value
        TempVars!activityFranchiseID = FranchiseID

        DoCmd.RunSQL "INSERT INTO GymActivity VALUES (Tempvars!activtyMemberID, Tempvars!activityTime, Tempvars!activityAccessType, Tempvars!activityFranchiseID)"

        DoCmd.Close
        DoCmd.OpenForm "frmMainPage"
    Else
        MsgBox "Please re-enter your Member ID"            
    End If

End Sub

edited Sep 04 '17 at 06:55

answered Sep 04 '17 at 06:11

Gustav

53,498
7
29
55

That will still fail if txtMemberID is equal to a zero-length string, since it will produce an invalid `WHERE` statement in the `DLookUp` (`WHERE MemberID = `, since MemberID is a number, it won't have any delimiters thus is invalid). The approach by @Bozhidar-Stoinev avoids this error. – Erik A Sep 04 '17 at 06:31
An unbound textbox will not hold an empty string. But what's missing is a way to escape. That has been added. – Gustav Sep 04 '17 at 06:51
Ah, yes, my wording is incorrect, but the point isn't. Because if `Me.txtMemberID.Value` is `Null`, the result will be the same (concatenating a string with `Null` yields the same result as concatenating a string with a zero-length string). I see you've corrected the error, though. – Erik A Sep 04 '17 at 07:01

Incorrect use of DLookup in if statement VBA MSAccess

2 Answers2