Hi all
Yet another DLookup question. I've researched as many threads as I could find, but still can't resolve the issue.
I have a sub form with a combo box where users can select a customer to whom they can transfer a number of service records for a machine to a new customer. (who has taken possession of the equipment).
Before the records transfer proceeds, I want to check that the customer to whom the record/s is/are being transfer actually owns the equipment to which the service records belong.
Aapologies for the spaces in the table name, it's a legacy issue.
So if CustomerID does not have a record in the table [Customer Machine Components] for the MachineNo in question, then DLookup should resolve Null.
If I run the code as is, sometimes it will work and throw up the Null error message box if applicable, and other times it won't work. Even if I am testing on the same customer record.
I'm really stumped with this and can't understand why it would work sometimes, and not others, even on the same dataset, and would appreciate some insight.
The Combobox has this code attached to the OnChange event;
Code:
Private Sub cmbNewOwner_Change()
FilterComboAsYouType Me.cmbNewOwner, "SELECT * FROM Customers", "CompanyName"
End Sub
This is the code that I have attached to the AfterUpdate Event of the combo box.
Code:
Private Sub cmbNewOwner_AfterUpdate()
Me.txtNewOwnerID = Me.cmbNewOwner
If IsNull(DLookup("MachineNo", "[Customer Machine Components]", "[CustomerID] = '" & Me.cmbNewOwner & "'")) Then
MsgBox Me.cmbNewOwner.Column(1) & " does not own Machine No " & txtMachineNo & ".", vbExclamation, "Error"
Me.cmbNewOwner.SetFocus
SendKeys "{BKSP}", True
Else
'continue processing the request
MsgBox "OK to transfer"
End If
End Sub
The combo box is unbound.
MachineNo in the table [Customer Machine Components] is a a short Text field without lookup.
CustomerID in the table [Customer Machine Components] is a short text field with lookup to a Customers table.