Check to see if there is a record

[Ken Ivins]

I have continuous form for payment which a billing number is entered. I want
to look at the payment table and look to see if that billing number is
already on it. If it is then a message box will appear to alert the user.
There can be more than one record of the same billing number (partial
payments, etc.) but I do want to alert the user in case of a typo.

I tried a Dlookup that return the customerID and if it was null then
proceeded or else pop up the msgbox. That did not work. Any other ideas on
how to do this?

Thanks,
Ken

 

[Allen Browne]

A DLookup() sounds reasonable.
You will need to set the 3rd argument so it identifies the correct
information. To be accurate, you should probably check that an existing
record has not been modified back to the same value, which of couse
DLookup() would find in the table.

Private Sub BillNo_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If Not IsNull(Me.BillNo) Then
If Me.BillNo.Value = Me.BillNo.OldValue Then
'do nothing
Else
strWhere = "[BillNo] = " & Me.BillNo
varResult = DLookup("CustomerID", "MyTable", strWhere)
If Not Isnull(varResult) Then
strMsg = "Bill number already exists for customer " &
varResult
MsgBox strMsg, vbExclamation, "Duplicate"
End If
End If
End If
End Sub

Note: If "BillNo" is a Text type field, you need extra quotes:
strWhere = "[BillNo] = """ & Me.BillNo & """"

 

[Ken Ivins]

Mike,

Thanks for that idea. My dlookup did work but your suggestion might be
better.

Thanks,
Ken