Error popup after click ok on type mismatch

I am using a combo box to search for records and it is telling me I have a type mismatch. I have been unable to find where this mismatch would be. The combo box still performs the desired function of selecting all records based on their "region"

I am using an ID field which is selected using the combo box but I want to pull through a name as well so I have input an unbound textbox and used VBA on change in the ID to update the name to the corresponding ID in the unbound textbox

Form For privacy reasons I snipped around some of the other fields showing data.

The VBA in the on_change event in ID goes

Private Sub txtSupplierID_Change()

Me.txtSupplerName.Value = Table("tblSuppliers").SupplierName
Where Table("tblSuppliers").ID = txtSupplierID

End Sub

I'm aware this code could be very wrong but I could not find anywhere else that showed me how to do what I was trying to do. Any suggestions on how to actually do it would be appreciated. If I have not been as detailed as needed or yo have any questions please ask.

Edit for Clarification: The form is not being used to save data. It is only being used to display data and issue an output to a report.

CodePudding user response：

Code is not just wrong, it is nonsense. Cannot reference a table object like that. And that Where usage is not valid. One way to pull value from a table is with DLookup domain aggregate function. Advise use AfterUpdate instead of Change event.

Private Sub txtSupplierID_AfterUpdate()
Me.txtSupplerName.Value = DLookup("SupplierName", "tblSuppliers", "ID = " & txtSupplierID)
End Sub

Might want to correct textbox name from txtSupplerName to txtSupplierName.

However, should not save both supplier ID and supplier name into this table. There really should be no need for this VBA. Just put DLookup() expression in a textbox ControlSource. For other methods of pulling the supplier name to form, review answer in MS Access Identical Comboboxes for Autofill