Create ComboBox based on results from Form

[srossbach]

Hi - I am running Office XP Professional and I am trying to create a form with several different fields. The most important are the UNIT and CHAPTER fields which contain info stored in the following tables: TBL_UNITS and TBL_CHAPTER

In the form I have created a COMBO box that gets the UNIT numbers from TBL_UNITS and stores the UNIT information in the MEMBER TABLE under "Unit"

That works fine....the trouble comes when I need to use the information stored under "Unit" to query only the CHAPTERS that match that particular unit and create a COMBO box with those matching CHAPTERS that will store that Chapter information in the MEMBER TABLE under "Chapter"

TBL_UNITS has the following fields:
ORG Number (number)
UNIT (number)
Description (text)
TBL_CHAPTER has the following fields:
ORG Number (number)
UNIT (number
CHAPTER (number)
Description (text)

So for example if the UNIT # entered into form is 23 - I need the COMBO box for Chapter to pull only the CHAPTERS that fall within UNIT 23 and their description (2 - Staff; 3 - Officer; 4 - Youth Club, etc) and have the selected information stored in the MEMBERS Table under "Chapter"

Any suggestions are greatly appreciated. Thanks.