By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,086 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,086 IT Pros & Developers. It's quick & easy.

How to use combo box in query by form?

P: n/a
I'm using Access 2000.

I currently have a report being generated using the results of a query
by form. The form used for this query is an unbound form,
frmListDialog. frmListDialog contains two unbound text boxes,
txtExamTitle and txtExamNumber. The user enters either the exam title
in txtExamTitle or the exam number in txtExamNumber as a parameter for
the query. This is currently working fine, however because the exam
titles are often long, and nobody remembers the exam numbers, instead
of the current text boxes I would like to have combo boxes that
contain lists of the exam titles and numbers. How can I do this?

I've created a query, qryTitleNum, which returns all exam titles and
their corresponding numbers. I tried binding the combo boxes to the
fields in qryTitleNum, but I did not get the results I wanted. I
suspect I didn't have the rowsource, etc. properly set. Is this even
the correct approach?
Any help is much appreciated.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The query you describe seems appropriate for the Row Source of the Combo
Box. You can't _bind_ a Control to a Query (that is, use a Query in the
Control's ControlSource property) -- you can only bind it to a Field in the
Form's RecordSource. The RowSource of a Combo box controls the items that
are used to populate its dropdown list.

Larry Linson
Microsoft Access MVP
"RBohannon" <ra*****@hotmail.com> wrote in message
news:ad**************************@posting.google.c om...
I'm using Access 2000.

I currently have a report being generated using the results of a query
by form. The form used for this query is an unbound form,
frmListDialog. frmListDialog contains two unbound text boxes,
txtExamTitle and txtExamNumber. The user enters either the exam title
in txtExamTitle or the exam number in txtExamNumber as a parameter for
the query. This is currently working fine, however because the exam
titles are often long, and nobody remembers the exam numbers, instead
of the current text boxes I would like to have combo boxes that
contain lists of the exam titles and numbers. How can I do this?

I've created a query, qryTitleNum, which returns all exam titles and
their corresponding numbers. I tried binding the combo boxes to the
fields in qryTitleNum, but I did not get the results I wanted. I
suspect I didn't have the rowsource, etc. properly set. Is this even
the correct approach?
Any help is much appreciated.

Nov 13 '05 #2

P: n/a
ra*****@hotmail.com (RBohannon) wrote in message news:<ad**************************@posting.google. com>...
I'm using Access 2000.

I currently have a report being generated using the results of a query
by form. The form used for this query is an unbound form,
frmListDialog. frmListDialog contains two unbound text boxes,
txtExamTitle and txtExamNumber. The user enters either the exam title
in txtExamTitle or the exam number in txtExamNumber as a parameter for
the query. This is currently working fine, however because the exam
titles are often long, and nobody remembers the exam numbers, instead
of the current text boxes I would like to have combo boxes that
contain lists of the exam titles and numbers. How can I do this?

I've created a query, qryTitleNum, which returns all exam titles and
their corresponding numbers. I tried binding the combo boxes to the
fields in qryTitleNum, but I did not get the results I wanted. I
suspect I didn't have the rowsource, etc. properly set. Is this even
the correct approach?
Any help is much appreciated.


No, you don't need to bind the form. Simply click on the "..." button
next to the "Row Source" property in the properties dialog box (after
selecting the combo box of course). This will bring up the query
builder window for that combo - add the correct table then drag and
drop the fields you want (ensuring you include the primary key as the
first column). You might want to change the 'sort' criteria in the
Exam Number or Exam Title to ascending. Do not save the query at this
time! Close the window using the top right "X". You will be prompted
if you want to save the query - click 'yes' (this way the query is
saved to the combo box and you don't have to create one in the
database window).

You still have some adjusting to do... The essential properties to
change in the properties dialog box are:
1. Under the 'Format' tab ensure 'column count' is correct (if you
have only the primary key and the title, it should be '2').
2. Set the 'Column Widths' property to 0;2.5;etc (the first column
must read zero in order for the primary key to be hidden. Set the
other column widths by trial and error).
3. Under the 'Data' tab ensure the 'Bound Column' value is '1'.

Now you're done! You will still have to make it look pretty, but that
doesn't effect its operation.

Cheers,
Bruce
Nov 13 '05 #3

P: n/a
ra*****@hotmail.com (RBohannon) wrote in message news:<ad**************************@posting.google. com>...
I'm using Access 2000.

I currently have a report being generated using the results of a query
by form. The form used for this query is an unbound form,
frmListDialog. frmListDialog contains two unbound text boxes,
txtExamTitle and txtExamNumber. The user enters either the exam title
in txtExamTitle or the exam number in txtExamNumber as a parameter for
the query. This is currently working fine, however because the exam
titles are often long, and nobody remembers the exam numbers, instead
of the current text boxes I would like to have combo boxes that
contain lists of the exam titles and numbers. How can I do this?

I've created a query, qryTitleNum, which returns all exam titles and
their corresponding numbers. I tried binding the combo boxes to the
fields in qryTitleNum, but I did not get the results I wanted. I suspect I didn't have the rowsource, etc. properly set. Is this even
the correct approach?
Any help is much appreciated.

Correct approach - in general terms, yes. The implementation is a bit
off, though. (Uh, yeah, or you wouldn't be posting here!) One way to
do this is to have a second combobox with something like:

(Warning - I'm totally winging this...)

Filter by: [Exam Title]
[Exam Number]

Set the rowsource of the second combo in the AfterUpdate (?) event of
the first combo. Then set it to something like

strSQL = "SELECT [" & me.cboFilterField & "] FROM SomeTable...
WHERE... (if you want filters, put them here...)
cboValueList.Rowsource = strSQL
cboValueList.Requery

And then you'd just build your filter in code and pass it in the Open
event of the report as openargs.
Nov 13 '05 #4

P: n/a
You've brought me fame and fortune and everything that goes with it.

I thank you all.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.