This seems to be a common error, but I can't quite get it fixed in my case.
The basic operation of my database / form is:
Get search value
The form has two combo boxes and several text fields. Each of the combo boxes allows the user to select independent search criteria. So, I have the two combos’ as cboContractAccount & cboMPxN. If the user selects a value from cboContractAccount, the VBA code searches the database for the record that matches this account number and then displays it. Similarly, if they select a value from cboMPxN the database is searched for a record with the matching serial number and this is displayed.
If the user selects an entry from cboContractAccount, the database retrieves all values required to populate the form…including the MPxN entry. What I want to do is, display the MPxN value that has been retrieved from the database, in the cboMPxN control. So, I have the following:
Get record from database where the AccountNum value matches the contents of the cboContractAccount control
Pass the appropriate values to the display record routine and show them
A partial listing of the display record routine is as follows:
Private Sub DisplayRecord(Form As Form_frmHomeVendInfo, _ Optional ContractAcct = Null, Optional MPxN = Null, Optional DateRequested = Null, _ Optional DateFulfilled = Null, Optional Agent = Null, Optional QDOSFileSent = Null, _ Optional UserID = Null, Optional FuelType = Null, Optional ReasonCode = Null, _ Optional Notes = Null, Optional CustomerName = Null, Optional Street = Null, _ Optional City = Null, Optional Postcode = Null, Optional SuccessfulVend = Null) On Error GoTo ErrorHandler With Form If Form.cboContractAccount.Column(1) Is Not Null Then .cboMPxN.Column(1) = MPxN Else If Form.cboMPxN.Column(1) Is Not Null Then .cboContractAccount.Column(1) = ContractAcct End If End If
However, when the code executes, I get the ‘Object Required’ error on the line “If Form.cboContractAccount.Column(1) Is Not Null Then”. My hope with this code is, if the agent has selected a value from cboContractAccount the routine will check the control and, if it is not null, the cboMPxN control needs to be populated with the MPxN value that is passed. Similarly, if the cboMPxN is not null, the cboContractAccount will be populated with the ContractAcct value passed.
As I say, I’m getting the ‘Object Required’ error on the first IF line. Putting a watch on “Form.cboContractAccount.Column(1)” does indeed show the value that has been sleected in the combo.
I’ve searched everywhere, tried everything and I still can’t get it right. What am I doing wrong?