restricting entry options (1 Viewer)

[davec]

I am setting up a form for data entry to a table via a query, and I am trying to have the answer to one question determine the specific answer categories available on a second question. Thus, on the first question I would like the respondent to choose one of 12 responses from a drop down menu. Simple enough, but I would also like this selection (i.e., 1,2, etc.) to determine what the options are that are shown in a drop-down menu for a second question. Thus, if the respondent chooses 1 on the first question, he/she is given a certain set of response categories for the second question (e.g., a. brown, b. yellow, etc.), but if the respondent chooses 2 on the first question, he/she is given a different set of response categories for the second question (e.g., a. whale, b. sardine, etc.). Any help on how to set up this linked, interactive series of questions on a form would be appreciated.

 

[Rich]

Look up "Solutions" and "frmSamp97" for examples of how to restrict data in one combo based on a value selected in another they are available from MS download.

 

[davec]

Sorry Rich, I'm a bit slow. Should I be searching for "Solutions" and "frmSamp97" on Microsoft's download page? If so, I might need more precise directions because it didn't turn up anything that seemed appropriate. Thanks for the help.

 

[Pat Hartman]

They are two separate sample databases. If you have A97 or earlier, there is a copy of solutions.mdb on your install disk and possibly already on your hard drive. The A2K version is only available with the developers' edition. The other db (as well as several more sample databases), is available from the download site.
http://support.microsoft.com/support/access/content/97downloads.asp?FR=0

In a nutshell, you need to use a query as the rowsource for the second combobox and that query needs a where clause that references the first combobox:

...
Where YourField = Forms!YourFormName!FirstComboBoxName;

Then in the AfterUpdate event of the FIRST combo, you need to requery the SECOND combo.

Me.SecondComboBox.Requery

 

[davec]

Thank you Rich and Pat. This is working.

 

[Pat Hartman]

Oops!
You also need to do the requery in the OnCurrent event of the form. Otherwise, if you go directly to the second combo without first changing the first combo so that it's AfterUpdate event fires, the dropdown of the second combo may not be accurate.