Error 424 - Object Required

[KiltedScot]

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
Retrieve record
Display record

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?

[SCBWV]

Form is a key word in VB. Try changing it to something else like frm.

[KiltedScot]

Renamed teh 'Form' parameter as 'Frm' and it still gives me the same error at the 'IF' statement.

[KiltedScot]

I changed the parameter back to 'Form' and commented out the 'IF' section and the code executes perfectly.

So, it appears as though it's something to do with the IF.

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

The controls are definately called 'cboContractAccount' and 'cboMPxN'. In the case of the first cbo, putting a watch on it when I go into the display code, shows that it contains the value, "850034630569", whcih is what was selected in the drop-down. So, the condiiton should be true, and it should then execute the code '.cboMPxN.Column(1) = MPxN'. But, I get the 'Object Required’ error on the 'IF'.

I'm at a loss! :confused:

[vb5prgrmr]

Try .Text...

Good Luck

[KiltedScot]

Changed it to .Text and the code now looks like:

If .cboContractAccount.text <> Null Then
            .cboMPxN.text = MPxN
        Else
            .cboContractAccount.text = ContractAcct
        End If

However, when I execute the code, and trace it, I get the following:

1. Code enters the display routine

2. Form.cboContractAccount.Text contains: "850034630569"

3. So, the field is not equal to Null, but the code does not then go
into the '.cboMPxN.text = MPxN' code line

4. Form.cboMPxN.text contains: I can't put a watch on this, as it gives me the message- "<You can't reference a property or method for a control unless the control has the focus.>"

5. So, I've now changed the code to:

If Form.cboContractAccount.text <> Null Then
            Form.cboMPxN.SetFocus
            Form.cboMPxN.text = MPxN
            Form.cboContractAccount.SetFocus
        Else
            .cboContractAccount.SetFocus
            .cboContractAccount.text = ContractAcct
            .cboMPxN.SetFocus
        End If

6. Executing this code, with Form.cboContractAccount.text containing "850034630569", it does not execute any of the Form.cboMPxN code, which I would have thought it should.

7. When execution continues into the ELSE, it executes the .SetFocus on the .cboContractAccount. However, on the ".cboContractAccount.text = ContractAcct" it gives me the error:

"The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing TopUp from saving the data in the field."

8. Neither of the cbo controls have a 'BeforeUpdate' event, nor do they have a 'ValidationRule'

My head is starting to hurt over this one.

I'm slowly loosing the will to live!

Help! :(

[vb5prgrmr]

Okay, ...Text <> vbNullString Then ' as null<>nullstring, and... or .text <> "" then

Good Luck

[KiltedScot]

Why is this a pain in the a##e!!

Sorry, but I'm getting frustrated with this. Thanks for that, it works a treat using vbNullString instead of just Null. Now though, when I set the combo control to a value, it is triggering the AfterUpdate event for the combo. So, the line of code:

Form.cboMPxN.text = MPxN

is triggering the event proc 'cboMPxN_NotInList'. Although, the value that it is putting in the combo has previously been retrieved from the database and does exist in the record. So, why would it say that it is not in the list and can I suspent the event(s) to prevent it calling the 'NotInList'?

Once again, many thanks for your help.

Duncs

[vb5prgrmr]

Hmmm... Well you might be able to get away with setting the listindex instead of the text...

Option Explicit

Private Sub Form_Load()
Combo1.AddItem "A"
Combo1.AddItem "B"
Combo1.AddItem "C"
Combo1.AddItem "D"
Combo1.AddItem "E"
End Sub

Private Sub Command1_Click()
Dim I As Integer
For I = 0 To Combo1.ListCount - 1
  If Combo1.List(I) = "C" Then
    Combo1.ListIndex = I
    Exit For
  End If
Next I
End Sub

Good Luck

[KiltedScot]

I've given up on this idea, and decided to go back to the drawing board. I think I was making things a little more complicated than they needed to be.

Howver, many thanks to all who assisted me.

Duncs