Dlookup, Checing for duplicates

[dweekley]

I am using dlookup to check the table for a duplicate value. I am getting an invalid use of null. I'm thinking that would be correct, just handling it wrong?

'Check Duplicate Entry


Dim CUSTID As String 'Customer Number

CUSTID = DLookup("[CustomerID]", "t_Customer", "[CustomerID] = '" & Forms!f_Customer!txtCustomerID & "'")

If Len(txtCustomerID.Value & "") = 0 Then Exit Sub

If txtCustomerID.Value = CUSTID Then



If MsgBox("There is a Customer ID by that number!" _
& Chr$(13) _
& "You must create a different Customer ID number.", vbOKOnly, _
"Duplicate Customer ID") = vbOK Then

txtCustomerID.Value = ""
txtCustomerID.SetFocus

End If

End If

TIA

[dweekley]

Well...I changed the As String to As Variant (due to the null) and seems to be working after some more reading.

Now I am trying to set the focus back to the txtCustomerID control. No luck. Keeps going to next in line.

[dweekley]

Ok...here is where I am at. Working less the setfocus. Being used on the Lost Focus Event. Now I am thinking that if someone goes into that record and tabs through the event will trigger when it doesn't need to now. Better event to use...or something to cover the tabbing through?

'Check Duplicate Entry


Dim CUSTID As String 'Customer Number
Dim CUSTNAME As String 'Customer Name

CUSTID = Nz(DLookup("CustomerID", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))
CUSTNAME = Nz(DLookup("Customer_Name", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))

If Len(txtCustomerID.Value & "") = 0 Then Exit Sub

If txtCustomerID.Value = CUSTID Then

If MsgBox("You must create a different Customer ID number!" _
& Chr$(13) _
& CUSTNAME & "...Has already been assigned that number.", vbOKOnly, _
"Duplicate Customer ID") = vbOK Then

txtCustomerID.Value = ""
txtCustomerID.SetFocus

End If

End If

[davegri]

Dim CUSTID As String 'Customer Number
Dim CUSTNAME As String 'Customer Name


If Len(txtCustomerID.Value & "") = 0 Then Exit Sub
CUSTID = Nz(DLookup("CustomerID", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))
CUSTNAME = Nz(DLookup("Customer_Name", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))


If txtCustomerID.Value = CUSTID Then

If MsgBox("You must create a different Customer ID number!" _
& Chr$(13) _
& CUSTNAME & "...Has already been assigned that number.", vbOKOnly, _
"Duplicate Customer ID") = vbOK Then

txtCustomerID.Value = ""
txtCustomerID.SetFocus

End If

End If

Makes more sense to move the line as indicated.

[dweekley]

Thanks for the update. I am thinking now that because I have the 'If, End If' as individuals I am going to have to either use some IfElse or Select Case to keep them from running individually. I hope I explained that correctly. Otherwise each will attempt to run. I have this on the LostFocus event.

I also found the issue with setting the focus after a Messege box. Trying to setfocus back to itself when it actually hasn't left yet.

'Check Duplicate Entry


Dim CUSTID As String 'Customer Number
Dim CUSTNAME As String 'Customer Name

If Len(txtCustomerID.Value & "") = 0 Then Exit Sub

If Len(txtCustomerID.Value & "") > 0 And Len(txtCustomerID.Value & "") < 3 Then

If MsgBox("You have entered an invalid number to be used as a Customer ID!" _
& Chr$(13) _
& "Please select no less than 3 numbers.", vbOKOnly, _
"Bad Number Entry") = vbOK Then

GoTo CISFCF

End If

End If

CUSTID = Nz(DLookup("CustomerID", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))
CUSTNAME = Nz(DLookup("Customer_Name", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))

If txtCustomerID.Value = CUSTID Then

If MsgBox("You must create a different Customer ID number!" _
& Chr$(13) _
& CUSTNAME & "...Has already been assigned that number.", vbOKOnly, _
"Duplicate Customer ID") = vbOK Then

GoTo CISFCF

End If

End If

CISFCF:


Me!btnCUSTSRCH.SetFocus
Me!txtCustomerID.SetFocus
Me!txtCustomerID.Value = ""

'Debug.Print CUSTID
'Debug.Print CUSTNAME

End Sub

[pbaldy]

Validation is typically done in the before update event. You can cancel the update and leave focus with:

Cancel = True

[davegri]

Dim CUSTID As String 'Customer Number
Dim CUSTNAME As String 'Customer Name

If Len(txtCustomerID.Value & "") = 0 Then Exit Sub

If Len(txtCustomerID.Value & "") > 0 And Len(txtCustomerID.Value & "") < 3 Then

    MsgBox "You have entered an invalid number to be used as a Customer ID!" _
    & Chr$(13) _
    & "Please select no less than 3 numbers.", vbOKOnly, _
    "Bad Number Entry")
    GoTo CISFCF
End If

CUSTID = Nz(DLookup("CustomerID", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))
CUSTNAME = Nz(DLookup("Customer_Name", "t_Customer", "CustomerID = '" & Forms!f_Customer!txtCustomerID & "'"))

If txtCustomerID.Value = CUSTID Then

    MsgBox "You must create a different Customer ID number!" _
    & Chr$(13) _   
    & CUSTNAME & "...Has already been assigned that number.", vbOKOnly, _
    "Duplicate Customer ID") = vbOK Then
    GoTo CISFCF
End If

CISFCF:
Me!btnCUSTSRCH.SetFocus
Me!txtCustomerID.SetFocus
Me!txtCustomerID.Value = ""


'Debug.Print CUSTID
'Debug.Print CUSTNAME
End Sub

You don't need the msgbox function to get the results you want. A display type messagebox will work. See the updated code. It gets rid of 2 IFs.

pbaldy (and you) are right. A better place for the code is in the before_update event.