Referring to Controls on Subforms

[Jables]

Here's how my forms are set up.

Main Form ---> frmStreets
SubForm -----> frmPhasingAndCoordination

On the subform, I have two combo boxes. The second combo box gets it's values from a query based on what the user selects in the first combo box. This all works fine if the subform is opened by itself. Here are the names of the combo box controls:

cboMasterLocationPrim
cboMasterLocationSec

Me.cboMasterLocationSec.Rowsource = SELECT [Streets].[SecondaryStreet] FROM Streets WHERE ((([Streets]![PrimaryStreet]=[Forms]![frmStreets]!cboMasterLocationPrim]));

When the user updates the first combo box (cboMasterLocationPrim), there is some VBA code--->Me.cboMasterLocationSec.Requery

This all works fine when the subform is opened by itelf.

When the subform is placed on the main form, It doesn't work. Well, it sort of works. Whenever the user updates the first combo box, the query dialog box pops up and the user must manually enter the search criteria. After this, the second combo box displays the correct values.

Whenever I insert the subform onto the main form, I change the SQL statement to refer to [Forms]![frmStreets]![subfrmPhasingAndCoordination]![cboMasterLocationPrim], but this doesn't do any good.

How should I refer to this control?

 

[Databaseguy]

See faq705-964 Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Jables]

So do I have to change every bit of code associated with subfrmPhasingAndCoordination? That is, everytime I set a controls property, it has to be referred to as

Forms("frmStreets&quot("subfrmPhasingAndCoordination&quot.Form.Controls.("cboMasterLocationPrim&quot

Is there an easier way?

 

[Databaseguy]

Well.....Yes.

To minimize typing, you could:

Dim ctl as control
Set ctl = Forms("frmStreets&quot("subfrmPhasingAndCoordination&quot.Form.Controls.("cboMasterLocationPrim&quot

With ctl
.value = "Your VAlue"
.Width = 40
'etc
End With
Or ctl.value =
Or myTextBox = ctl.value

Etc

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy