DLookup - removing criteria if fields are not needed

[John Desselle]

I have a simple ZipCode - City/State dlookup function in my database:

Private Sub cboZip_AfterUpdate()
    strCity = DLookup("[City]", "[tblZIPS]", "[ZIP] = " & [ZIP])
    strState = DLookup("[State]", "[tblZIPS]", "[ZIP] = " & [ZIP])
    strZipCode = DLookup("[Zip]", "[tblZIPS]", "[ZIP] = " & [ZIP])
   
End Sub

This pretty much does what I want. You find the zip code in the combo box, select it and the City and State fields are automatically populated.

Where I am having trouble is how do you get around a user deleting the zip code if the record it was chosen for doesn't need it.

Right now I get:
"Run-time error '3075':
Syntax error (missing operator) in query expression '[ZIP] ='.

Debugging has the first line of code highlighted.

[John Desselle]

I've updated the code to make it a little faster, and easier to understand, but am still getting error if I remove the entry in cboZip.

Private Sub cboZip_AfterUpdate()

    strCity = DLookup("[City]", "[tblZIPS]", "ZipID = " & cboZip & "")
    strState = DLookup("[State]", "[tblZIPS]", "ZipID = " & cboZip & "")
    strZipCode = DLookup("[Zip]", "[tblZIPS]", "ZipID = " & cboZip & "")
   
End Sub

[John Desselle]

I've gotten it to work. I am positive it's not the smoothest or cleanest solution, but it works:

Private Sub cboZip_AfterUpdate()
    
    If IsNull(Me.cboZip) Then
        strCity = ""
        strState = ""
        Else
    
    strCity = DLookup("[City]", "[tblZIPS]", "ZipID = " & cboZip & "")
    strState = DLookup("[State]", "[tblZIPS]", "ZipID = " & cboZip & "")
    strZipCode = DLookup("[Zip]", "[tblZIPS]", "ZipID = " & cboZip & "")
   End If
   
End Sub

[NeoPa]

John Desselle:
I am positive it's not the smoothest or cleanest solution

You shouldn't be. It's fundamentally fine. The indenting is somewhat misleading, and adding empty strings to a string calculation is somewhat unnecessary, but it's what's required. It also fails to allow for invalid Zip codes to be entered. Here's another version that might be a little more finely tuned :

Private Sub cboZip_AfterUpdate()

    strZipCode = IIf(IsNull(Me.cboZip), _
                     "", _
                     DLookup("[Zip]", "[tblZIPS]", "ZipID = " & .cboZip))
    If strZipCode > "" Then
        strCity = DLookup("[City]", "[tblZIPS]", "ZipID = " & strZipCode)
        strState = DLookup("[State]", "[tblZIPS]", "ZipID = " & strZipCode)
    Else
        strCity = ""
        strState = ""
    End If

End Sub

This way, any invalid, or unfound, zips used will correctly set all the values to blank and only a known Zip code will populate the strings.