Full Version: VBA - DLookup and an If Statement
UtterAccess Forums > Microsoft® Access > Access Forms
Happy Friday ...
I am having a little situtation. I have a DLookup text box on a form that is being triggered by a command button, which is working fine. I would now like to take it one step further and add a text box that says whether or not the DLookup was found in the table or not. Here is my code below.
Private Sub Command23_Click()
txtCustomerSK = DLookup("Customer_SK", "SAR Client List", "CUSTOMER_SK=" & Forms![SAR Client Check]!CUSTOMER_SK)
If Me.txtCustomerSK = Null Then
Me.txtMsgBox.Value = "This client does not exist in the Client List."
Me.txtMsgBox.Value = "This client already exists in the Client List."
End If

End Sub
~~ my problem is that it keeps returning the Else value even if the txtCustomerSK has a value in it. Any suggestions?
Change the line
If Me.txtCustomerSK = Null Then
If IsNull(Me.txtCustomerSK) Then
because you cannot directly detect a Null value by using the = comparison operator.
HTH frown.gif
Larry Larsen
If IsNull(Me.txtCustomerSK) Then
Access: IsNull Function
I would do it differently. I would use:
xtCustomerSK = Nz(DLookup("Customer_SK", "SAR Client List", "CUSTOMER_SK=" & Forms![SAR Client Check]!CUSTOMER_SK),"Not Found")
That's all you need rather then using another text box.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.