Cascading Combo Box Won't Update

  • Thread starter Thread starter Marcia
  • Start date Start date
M

Marcia

I have various "Homes" where "Residents" live within various
"Locations" within the Home. These locations vary among the homes;
for example, the River Home may have "East" and "West," while the
Brook Home has "North" and "South."

From my Residents form, I want to choose their Home through a combo
box, and when I click on a second combo box (Location) I want to see a
filtered list of the locations that apply only to the
previously-chosen Home.

Based on the following list of tables and fields, I have been able to
accomplish this, **EXCEPT** the second combo will not update the
filter after I have first clicked on it without closing and re-opening
the form. I want the combo to update automatically every time I
change records.

My tables/form setup and code is as follows:

Tables and Fields:

Table: HOMES:
fldHomeID
fldHomeName

Table: HOMELOCATIONS:
fldHomeLocID (autonumber)
fldHomeLocation (text)
fldHL_HomeID (number)

Table: RESIDENTS:
fldRes_HomeID
fldRes_HomeLocation

Form: frmRESIDENTS:
cboRes_HomeName (combo that displays
Homes.fldHomeName, &
stores Homes.fldHomeID in
Residents.fldRes_HomeID)
cboHomeLocation (combo that displays
HomeLocations.fldHomeLocation, & stores
HomeLocations.fldHomeLocID in
Residents.fldRes_HomeLocation)

First ComboBox (cboRes_HomeName):
In the "After_Update" event, I entered the following code:
Private Sub fldRes_HomeID_AfterUpdate()
Forms!frmResidents!cboHomeLocation.Requery
End Sub

Second ComboBox (cboHomeLocation):
In the "Row Source" property, I entered the following SQL query:
SELECT DISTINCT tblHomeLocations.fldHomeLocID,
tblHomeLocations.fldHomeLocation
FROM tblHomeLocations
WHERE (((tblHomeLocations.fldHL_HomeID)=[Forms]![frmResidents]!
[fldRes_HomeID]))
ORDER BY tblHomeLocations.fldHomeLocation;

Does anyone know what I'm doing wrong?

Thanks,
Jessi
 
Jessi,

Try it like this...
(* indicates change)

First ComboBox (cboRes_HomeName):
* Private Sub cboRes_HomeName_AfterUpdate()
* Me.cboHomeLocation.Requery
End Sub

Second ComboBox (cboHomeLocation), Row Source:
SELECT tblHomeLocations.fldHomeLocID, tblHomeLocations.fldHomeLocation
FROM tblHomeLocations
*WHERE ((tblHomeLocations.fldHL_HomeID)=[Forms]![frmResidents]!
[cboRes_HomeName])
ORDER BY tblHomeLocations.fldHomeLocation;
 
Thanks for your help... I am only able to make it work, though, if I
modify the home NAME combo box first. But if I click on the Home
LOCATION without first changing the home NAME, the LOCATION items do
not change to match those assigned to the HOME.

Is there any way to get the items in the second combo (home location)
to automatically update without first changing the home name? Is it
because I am using the "Requery" command in the first combo's "After
Update" event? If so, should I move the Requery command somewhere
else?


I have posted the codes again because I made a typo on the first try:

FIRST COMBO BOX (Event Procedure):
Private Sub cboResHomeName_AfterUpdate()
Me.cboHomeLocation.Requery
End Sub



SECOND COMBO BOX (SQL Code in Row Source):
SELECT DISTINCT tblHomeLocations.fldHomeLocID,
tblHomeLocations.fldHomeLocation
FROM tblHomeLocations
WHERE (((tblHomeLocations.fldHL_HomeID)=[Forms]![frmResidents]!
[cboResHomeName]))
ORDER BY tblHomeLocations.fldHomeLocation;


Thanks,
Jessi

___________________________________________________________


Steve Schapel said:
Jessi,

Try it like this...
(* indicates change)

First ComboBox (cboRes_HomeName):
* Private Sub cboRes_HomeName_AfterUpdate()
* Me.cboHomeLocation.Requery
End Sub

Second ComboBox (cboHomeLocation), Row Source:
SELECT tblHomeLocations.fldHomeLocID, tblHomeLocations.fldHomeLocation
FROM tblHomeLocations
*WHERE ((tblHomeLocations.fldHL_HomeID)=[Forms]![frmResidents]!
[cboRes_HomeName])
ORDER BY tblHomeLocations.fldHomeLocation;

--
Steve Schapel, Microsoft Access MVP

I have various "Homes" where "Residents" live within various
"Locations" within the Home. These locations vary among the homes;
for example, the River Home may have "East" and "West," while the
Brook Home has "North" and "South."

From my Residents form, I want to choose their Home through a combo
box, and when I click on a second combo box (Location) I want to see a
filtered list of the locations that apply only to the
previously-chosen Home.

Based on the following list of tables and fields, I have been able to
accomplish this, **EXCEPT** the second combo will not update the
filter after I have first clicked on it without closing and re-opening
the form. I want the combo to update automatically every time I
change records.

My tables/form setup and code is as follows:

Tables and Fields:

Table: HOMES:
fldHomeID
fldHomeName

Table: HOMELOCATIONS:
fldHomeLocID (autonumber)
fldHomeLocation (text)
fldHL_HomeID (number)

Table: RESIDENTS:
fldRes_HomeID
fldRes_HomeLocation

Form: frmRESIDENTS:
cboRes_HomeName (combo that displays
Homes.fldHomeName, &
stores Homes.fldHomeID in
Residents.fldRes_HomeID)
cboHomeLocation (combo that displays
HomeLocations.fldHomeLocation, & stores
HomeLocations.fldHomeLocID in
Residents.fldRes_HomeLocation)

First ComboBox (cboRes_HomeName):
In the "After_Update" event, I entered the following code:
Private Sub fldRes_HomeID_AfterUpdate()
Forms!frmResidents!cboHomeLocation.Requery
End Sub

Second ComboBox (cboHomeLocation):
In the "Row Source" property, I entered the following SQL query:
SELECT DISTINCT tblHomeLocations.fldHomeLocID,
tblHomeLocations.fldHomeLocation
FROM tblHomeLocations
WHERE (((tblHomeLocations.fldHL_HomeID)=[Forms]![frmResidents]!
[fldRes_HomeID]))
ORDER BY tblHomeLocations.fldHomeLocation;

Does anyone know what I'm doing wrong?

Thanks,
Jessi
Click to expand...
Click to expand...
 
Jessi,

Do you mean so the Locations combobox shows the correct list when you
move from record to record on the form? If so, you could try putting
the Requery code on the form's On Current event, as well as the Home
combobox's After Update event.
 
Thanks! That fixed it.

Jessi
____________________________

Steve Schapel said:
Jessi,

Do you mean so the Locations combobox shows the correct list when you
move from record to record on the form? If so, you could try putting
the Requery code on the form's On Current event, as well as the Home
combobox's After Update event.

--
Steve Schapel, Microsoft Access MVP

Thanks for your help... I am only able to make it work, though, if I
modify the home NAME combo box first. But if I click on the Home
LOCATION without first changing the home NAME, the LOCATION items do
not change to match those assigned to the HOME.

Is there any way to get the items in the second combo (home location)
to automatically update without first changing the home name? Is it
because I am using the "Requery" command in the first combo's "After
Update" event? If so, should I move the Requery command somewhere
else?


I have posted the codes again because I made a typo on the first try:

FIRST COMBO BOX (Event Procedure):
Private Sub cboResHomeName_AfterUpdate()
Me.cboHomeLocation.Requery
End Sub



SECOND COMBO BOX (SQL Code in Row Source):
SELECT DISTINCT tblHomeLocations.fldHomeLocID,
tblHomeLocations.fldHomeLocation
FROM tblHomeLocations
WHERE (((tblHomeLocations.fldHL_HomeID)=[Forms]![frmResidents]!
[cboResHomeName]))
ORDER BY tblHomeLocations.fldHomeLocation;


Thanks,
Jessi
Click to expand...
Click to expand...
 
Back
Top