requery combo box on subform

[thersitz]

Access 2003 on XP

here's my code that ain't working:

Private Sub cboState_AfterUpdate()
Dim ctlListCity As Control
Set ctlListCity = Forms!frmOrgs!sfrmContacts.cboCity

ctlListCity.RowSourceType = "Table/Query"
ctlListCity.RowSource = "SELECT tblCities.cityID, tblCities.city FROM
tblCities WHERE tblCities.stateID=[cboState];"

' Requery source of data for list box.
ctlListCity.Requery
End Sub


Basically, I have an Orgs form and a contacts subform upon it. The contacts
subform has 2 combo boxes -- one for state and the other for city. When the
user selects a state, I want to requery the city combo to fill with only
cities from that state. Is this not working because the control is on a
subform?

 

[Graham Mandeno]

Your WHERE clause should be:

.... WHERE stateID=" & cboState & ";"

or, if stateID is a text field, add some extra quotes:

.... WHERE stateID='" & cboState & "';"

Setting RowSourceType is unnecessary (you can set it in design view and it
should never change).

The Requery is also unnecessary - changing RowSource also forces a Requery.

 

[thersitz]

hey Graham,

Thanks. I made the changes you suggested -- but I am still getting the same
error. Choosing to debug sends me to this line in my code:

Set ctlListCity = Forms!frmOrgs!sfrmContacts.cboCity

I get run time error 438: object doesn't support this property or method

Your WHERE clause should be:

... WHERE stateID=" & cboState & ";"

or, if stateID is a text field, add some extra quotes:

... WHERE stateID='" & cboState & "';"

Setting RowSourceType is unnecessary (you can set it in design view and it
should never change).

The Requery is also unnecessary - changing RowSource also forces a
Requery.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Access 2003 on XP

here's my code that ain't working:

Private Sub cboState_AfterUpdate()
Dim ctlListCity As Control
Set ctlListCity = Forms!frmOrgs!sfrmContacts.cboCity

ctlListCity.RowSourceType = "Table/Query"
ctlListCity.RowSource = "SELECT tblCities.cityID, tblCities.city FROM
tblCities WHERE tblCities.stateID=[cboState];"

' Requery source of data for list box.
ctlListCity.Requery
End Sub


Basically, I have an Orgs form and a contacts subform upon it. The
contacts subform has 2 combo boxes -- one for state and the other for
city. When the user selects a state, I want to requery the city combo to
fill with only cities from that state. Is this not working because the
control is on a subform?

 

[Graham Mandeno]

OK, you do need the other change I suggested, but as well as that, change
your Set statement to:

Set ctlListCity = Forms!frmOrgs!sfrmContacts.Form!cboCity

If cboState is on the form frmOrgs, then all you need is:

Set ctlListCity = sfrmContacts.Form!cboCity

In fact, since you refer to it only once, there is no need for a Control
variable at all:

sfrmContacts.Form!cboCity.RowSource = "Select ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

hey Graham,

Thanks. I made the changes you suggested -- but I am still getting the
same error. Choosing to debug sends me to this line in my code:

Set ctlListCity = Forms!frmOrgs!sfrmContacts.cboCity

I get run time error 438: object doesn't support this property or method

Your WHERE clause should be:

... WHERE stateID=" & cboState & ";"

or, if stateID is a text field, add some extra quotes:

... WHERE stateID='" & cboState & "';"

Setting RowSourceType is unnecessary (you can set it in design view and
it should never change).

The Requery is also unnecessary - changing RowSource also forces a
Requery.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Access 2003 on XP

here's my code that ain't working:

Private Sub cboState_AfterUpdate()
Dim ctlListCity As Control
Set ctlListCity = Forms!frmOrgs!sfrmContacts.cboCity

ctlListCity.RowSourceType = "Table/Query"
ctlListCity.RowSource = "SELECT tblCities.cityID, tblCities.city FROM
tblCities WHERE tblCities.stateID=[cboState];"

' Requery source of data for list box.
ctlListCity.Requery
End Sub


Basically, I have an Orgs form and a contacts subform upon it. The
contacts subform has 2 combo boxes -- one for state and the other for
city. When the user selects a state, I want to requery the city combo to
fill with only cities from that state. Is this not working because the
control is on a subform?