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

Combo Box Options Problem

P: n/a
I need help limiting the options shown in a combo box.

I have two combo boxes that appear in a datasheet view subform. The
form looks like an Excel spreadsheet, which is what the user prefers.
Among the fields in the table are two combo boxes:
1) Product Family
2) Products

When the user is adding data, the Products list is easily limited to
only the products in the product family using an unbound combo box. I
set the row source to limit the list returned based on what the user
selected in the Product Family list. When the user updates the Products
list, the on change event fires at which time the value in the table
field is updated with the value in the unbound combon box.

The problem occurs when the user is updating or changing data. The
unbound products list does not show the product currently in the
database for that record. When I try to set the control source to
"Product," all of the options are blank. Also, when there is more than
one record showing, when the user selects an option in the unbound
list, all records show the same data. So for an update situation, I
believe the field must be bound. So how do I get the product combo box
to show
1) the product in the record currently and
2) the list of available products limited by what is selected in the
product family list.
Any suggestions?
Thanks,
Joni b

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Thanks for the link, but it doesn't help the problem. My problem is two
fold. First, when the form is first opened, the second combo box does
not show anything when I need it to show what is in the database
currently. Second, when the first combo box is changed, the options in
the second combo box need to be changed only on that row. For example:

When the user opens the form, Row 1 shows the product family of Widgets
and Row 2 shows the product family of Screws. The product lists are
blank. What I need it to show is the product currently in the
underlying table, such as:

Row 1:
Product Family = Widgets
Product = 2 inch
Row 2:
Product Family = Screws
Product = Flat Head 3/4 inch

Second, when the user changes the first row to another product family,
such as Nails, all the product lists on all the rows show the options
for nails. I need only the first row to show the options and the rest
of the rows to remain as they are.

Maybe I'm asking too much of Access?

Nov 13 '05 #3

P: n/a
After searching the web for solutions and trying different ideas, I
found a very simple solution to this problem. So I'm posting it here
incase anyone else comes across the same issue....

For the second combo box, let it initially show all of the options
available. This way, when the user opens the form in datasheet view,
they can see what is already in the table. So the RowSource would look
something like:

SELECT ProductName.tblProducts FROM tblProducts

Then, in the forms' Current event, enter the code to change the row
source and limit it to only the options you want to show. For example:

Dim strSQL as String

strSQL = "SELECT ProductName.tblProducts FROM tblProducts " & _
"WHERE ProductFamily.tblProducts = " & _
"[Forms]![Product Info]![Product
Subform]![cmbProductFamily]"

me.cmbProducts.RowSource = strSQL

When the user selects the row, only that row's product list will
change. The other rows' product lists still show the product in the
underlying table.

Next, in the first combobox (the one that has the liminting factor on
the second combobox), put a requery in the onchange event. Such as:

me.cmbProducts.Requery

This will requery the Products List when the user changes the Product
Family and show only the available options for that product family.

Hope this helps anyone who has the same problem...

Thanks,
Joni

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.