Combobox which is dependent on another combobox is not working?

[keun]

I have a combobox where the available entries are supposed to be dependent on another combobox.

My problem is that when I put this form on a subform by putting it in a tab, I get an error. This combobox works when it's not in the tab of the subform.

The row source for the dependent combobox is:

SELECT tblCaseID.CaseID, tblCaseID.CaseType FROM tblCaseID WHERE (((tblCaseID.ClientID)=Forms!FrmNotesTabSubform!cboClientID)) ORDER BY tblCaseID.CaseID;


The AfterUpdate for cboClientID is:

Private Sub cboClientID_AfterUpdate()
Me.cboCase = Null
Me.cboCase.Requery

End Sub


The subform is set up with the relationship on ClientID, and I think this is where my problem is, but I do not know how to fix it.

 

[dhookom]

I expect the forms... referemence is wrong. There is a good source on this at Refer to Form and Subform properties and controls

Duane
Hook'D on Access
MS Access MVP

 

[keun]

I've looked at that, and made some changes, but am still getting an error.

Since both of my controls are on the same form (Subform1 in the language of the page you linked to) it seems that me!cboClientID should work. But when I am prompted with a dialog box to enter the parameter value for me!txtClientID, but that value is populated!

 

[MajP]

Since both of my controls are on the same form (Subform1 in the language of the page you linked to) it seems that me!cboClientID should work

Sorry, no.

If the form is instantiated as a subform, you can not reference it by its form name through the forms collection. You can only reference it as a property of a subform control, through the main form. So the following is wrong if "frmNotesTabSubForm" is a name of a form instantiated as a subform:
Forms!FrmNotesTabSubform!cboClientID

It has to be
Forms!NameMainForm!NameSubFormControl.Form!cboClientID

You can simply test it. Open a form with a subform. Go to the immediate window and type:
?Forms!YourMainFormName.name
It will return the name of the main form
Then type
?Forms!NameOfTheSubForm
It will give a "Microsoft can not find the form error"


Forms! is a reference to the

 

[dhookom]

Go to the immediate window

To do this, press Ctrl+G.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Private Sub cboClientID_AfterUpdate()
Me!cboCase = Null
Me!cboCase.RowSource = "SELECT CaseID,CaseType FROM tblCaseID WHERE ClientID=" & Me!cboClientID & " ORDER BY CaseID"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886