Loading Combo Boxes on a Continious form

D

Dave Y.

Hello,

I’m looking for some direction. I’m developing a survey tool in MS Access
2003 for a local department in our organization. For the survey setup, I
store the questions in one table and the valid answers for the questions in
another table, each question could have a different set of valid answers. The
survey itself is a single form with a continuous-form subform that displays
the questions and a combo box for the respondent’s answer to the survey
question. When I load the survey I want each combo box to list only the valid
answers for the associated questions. I have tried changing the Row Source on
the combo box to:

SELECT [tblAnswers].[AnswerId], [tblAnswers].[AnswerText] FROM [tblAnswers]
WHERE [tblAnswer].[QuestionId] = Me.[QuestionId].

Me.[QuestionId] is a text box on the subform that holds the reference to the
question. I end up getting the valid answers for the last question in the
survey in every combo box list. Any ideas?
 
M

Marshall Barton

Dave said:
I’m looking for some direction. I’m developing a survey tool in MS Access
2003 for a local department in our organization. For the survey setup, I
store the questions in one table and the valid answers for the questions in
another table, each question could have a different set of valid answers. The
survey itself is a single form with a continuous-form subform that displays
the questions and a combo box for the respondent’s answer to the survey
question. When I load the survey I want each combo box to list only the valid
answers for the associated questions. I have tried changing the Row Source on
the combo box to:

SELECT [tblAnswers].[AnswerId], [tblAnswers].[AnswerText] FROM [tblAnswers]
WHERE [tblAnswer].[QuestionId] = Me.[QuestionId].

Me.[QuestionId] is a text box on the subform that holds the reference to the
question. I end up getting the valid answers for the last question in the
survey in every combo box list. Any ideas?


This is a fairly common question that does not have a simple
answer. Try using Copy/Paste to make a copy of your combo
box. Remove the criteria from its row source (i.e. the
Answers table.

Then add a line of code to this new combo box's GotFocus
event:
Me.[your existing combo box].SetFocus

Now set the new combo box's Left and Top properties to be
exactly the same as your existing combo box.

It may be confusing to have one control on top of another,
but when you tab or click to the new combo box, it will
immediately set the focus to your existing combo box that
will temporarily bring it to the front so you can do your
usual selection. When the focus moves away from the combo
box, the new combo box will automatically return to the
front.

You should also set your existing combo box's TabStop
property to No and set the new combo box's TabOrder property
to the appropriate number.
 
D

Dave Y.

Thanks Marshall, I'll give it a try
--
Dave Y.


Marshall Barton said:
Dave said:
I’m looking for some direction. I’m developing a survey tool in MS Access
2003 for a local department in our organization. For the survey setup, I
store the questions in one table and the valid answers for the questions in
another table, each question could have a different set of valid answers. The
survey itself is a single form with a continuous-form subform that displays
the questions and a combo box for the respondent’s answer to the survey
question. When I load the survey I want each combo box to list only the valid
answers for the associated questions. I have tried changing the Row Source on
the combo box to:

SELECT [tblAnswers].[AnswerId], [tblAnswers].[AnswerText] FROM [tblAnswers]
WHERE [tblAnswer].[QuestionId] = Me.[QuestionId].

Me.[QuestionId] is a text box on the subform that holds the reference to the
question. I end up getting the valid answers for the last question in the
survey in every combo box list. Any ideas?


This is a fairly common question that does not have a simple
answer. Try using Copy/Paste to make a copy of your combo
box. Remove the criteria from its row source (i.e. the
Answers table.

Then add a line of code to this new combo box's GotFocus
event:
Me.[your existing combo box].SetFocus

Now set the new combo box's Left and Top properties to be
exactly the same as your existing combo box.

It may be confusing to have one control on top of another,
but when you tab or click to the new combo box, it will
immediately set the focus to your existing combo box that
will temporarily bring it to the front so you can do your
usual selection. When the focus moves away from the combo
box, the new combo box will automatically return to the
front.

You should also set your existing combo box's TabStop
property to No and set the new combo box's TabOrder property
to the appropriate number.
 
D

Dave Y.

Thanks Marshall. This worked great. I was able to get the results I needed.
--
Dave Y.


Marshall Barton said:
Dave said:
I’m looking for some direction. I’m developing a survey tool in MS Access
2003 for a local department in our organization. For the survey setup, I
store the questions in one table and the valid answers for the questions in
another table, each question could have a different set of valid answers. The
survey itself is a single form with a continuous-form subform that displays
the questions and a combo box for the respondent’s answer to the survey
question. When I load the survey I want each combo box to list only the valid
answers for the associated questions. I have tried changing the Row Source on
the combo box to:

SELECT [tblAnswers].[AnswerId], [tblAnswers].[AnswerText] FROM [tblAnswers]
WHERE [tblAnswer].[QuestionId] = Me.[QuestionId].

Me.[QuestionId] is a text box on the subform that holds the reference to the
question. I end up getting the valid answers for the last question in the
survey in every combo box list. Any ideas?


This is a fairly common question that does not have a simple
answer. Try using Copy/Paste to make a copy of your combo
box. Remove the criteria from its row source (i.e. the
Answers table.

Then add a line of code to this new combo box's GotFocus
event:
Me.[your existing combo box].SetFocus

Now set the new combo box's Left and Top properties to be
exactly the same as your existing combo box.

It may be confusing to have one control on top of another,
but when you tab or click to the new combo box, it will
immediately set the focus to your existing combo box that
will temporarily bring it to the front so you can do your
usual selection. When the focus moves away from the combo
box, the new combo box will automatically return to the
front.

You should also set your existing combo box's TabStop
property to No and set the new combo box's TabOrder property
to the appropriate number.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top