I have two forms:

InterviewMaster and InterviewDetail

InterviewDetail opens up as a subform in InterviewMaster and these two forms are linked through a common field called InterviewID

In InterviewDetail I have a textbox called Questiontype as well as combobox called InterviewDropdown.

The data in the dropdown varies based on the data on in the textbox. To make this happen, I have a next button to move to the next question. Whenever I click on next the following runs:

Dim ctlCombo As Control
 
Set ctlCombo = Forms!InterviewDetail!cmbInterviewDropdown
 
ctlCombo.Requery

The Row Source setting for my combobox is set to look up the required answers, again this is based on the value as per the textbox:

SELECT [queryAnswerOptions].[Answer] FROM queryAnswerOptions ORDER BY [Answer]; 

So the options are determined by my query called queryAnswerOptions

So as I cycle through my questions using my next and previous buttons, the dropdown options are updated based on the value of my textbox. This works perfectly when I open the subform from the navigation pane. However, when I open the main form and click on the next button my dropdown does not have any values. I've tried requerying the subform with no luck. I've also tried opening the subform in full screen from my main form but this also does not work. I also don't want to go that route as it does not work well with the overall flow of my form.

Any assistance will be greatly appreciated.

CodePudding user response：

Problem with the query WHERE criteria parameter is when form is installed as a subform, the form reference no longer works because it must use the subform container control name. I always name container control different from the object it holds, such as ctrDetails.

Option is to put SQL statement directly in combobox RowSource instead of basing combobox RowSource on a dynamic parameterized query object - then it will work whether form is standalone or a subform.

SELECT Answer FROM InterviewAnswers WHERE QuestionID = [txtQuestionID] ORDER BY Answer;