Code for populating one combo box based on the selection in another

[AShaw]

Hello,

I can't quite get my code right for populating one combo box based on the selection in another, and I was hoping someone could please take a look. I didn't create this Access database but I've been asked to add in a second combo box that is based on the selection of the first combo box.

My first combo box is for Rohe (the location of the Maori tribe) and the second is Iwi (the tribe name). So my first combo box (Rohe) has Dvic_ROHE written in the control source and its row source code is:
SELECT tblLookUpValues.DatabaseFieldValue
FROM tblLookUpValues INNER JOIN tblFormQuestionLookUp ON tblLookUpValues.LookUpID = tblFormQuestionLookUp.LookUpID
WHERE (((tblFormQuestionLookUp.FormID)=2) AND ((tblFormQuestionLookUp.QuestionNumber)=75))
ORDER BY tblLookUpValues.DisplayOrder;

The combo box for Rohe (tribe location) is populated by a table named tblLookUpValues. The second combo box for Iwi (tribe) has Dvic_Iwi in the control source. I created a table named tbl_Rohe_Iwi_lookup that contains ROHE (a list of tribe locations) and IWI (the tribes). There are 12 tribal locations and another 10-14 tribes in each location.
The form that the Rohe and Iwi combo boxes are located on is called frmDeceased. This is the code that I attempted to write so that the second combo box (Iwi) is populated with only the tribes that are in the Rohe (location) that was selected from the Rohe combo box. But when I run the query it comes back with no results. Any thoughts? Thanks.

SELECT distinct tbl_Rohe_Iwi_lookup.Rohe, tbl_Rohe_Iwi_lookup.Iwi
FROM tbl_Rohe_Iwi_lookup
WHERE (((tbl_Rohe_Iwi_lookup.Rohe)=[forms]![frmDeceased]![Dvic_Rohe])) UNION select distinct null, null
FROM tbl_Rohe_Iwi_lookup
ORDER BY tbl_Rohe_Iwi_lookup.Iwi;

 

[vbaInet]

This should help:

http://baldyweb.com/CascadingCombo.htm

 

[AShaw]

Thanks, I was following a very similar example but I still can't get this right. I played around with it a bit more and have gotten it to a stage where the Iwi (tribe) does populate based on the Rohe (location) chosen, but if I then go back to Rohe and choose a different location then the Iwi does not change to accommodate it.
For example, I choose Manawatu as the location and it correctly offers me the Manawatu tribes in the next combo box, but then if the user realises they wanted to choose Whanganui as the location, the Iwi (tribe) options do not change when they change the location. In the example you supplied, theirs correctly allows the user to change the State and offers the new City list based on the change. So something is still wrong with mine. Any thoughts please?

 

[vbaInet]

You said you followed a similar example so what are the differences between the one you have and the link?

Also, are you requerying in the After Update event of each combo box?

 

[AShaw]

Ah, yes - it was the Requery I was missing in my Event Procedure.

Thank you - that's what I needed.

 

[vbaInet]

You're welcome!