Subform Combobox duplicates

  • Thread starter Thread starter steve o
  • Start date Start date
S

steve o

I have a form and a subform. Within the form I have a combo box
(cboSelCat) to select Categories.

Within the subform I have a combo box (cboSC) that uses a query based
on the catid from cboSelCat in the mailform to give me the
subcategories.

I use the following query in the record source of cboSC:

SELECT SubCategory.subcatid, SubCategory.SubCategory FROM SubCategory
WHERE SubCategory.catid=Forms!frmDataInput!cboSelCat;


It dynamically populates okay. (ie. if I select Fruits in sboSelCat,
then cboSC gives me choices of Apples, Oranges, etc.

The subform is in data sheet view so I have a list of records. So
there is one cboSC for each one of my records in the subform.

The problem is if I select Apples for one subform record, then ever
other subform record then switches to Apples.

I originally had the cboSC query above in the After_Update for the
subform. I also tried it on OnCurrent. But they all give me the same
results.

I've done lots of google searching for the answer, but I can't find
it.

Any ideas?

Thanks,

Steve
 
The problem is if I select Apples for one subform record, then ever
other subform record then switches to Apples.
Click to expand...

This is because although it looks like you have multiple combo boxes
in this column, you really only have *one*, displayed multiple times;
when you change the properties of the combo box on one row, it changes
for all rows.

A getaround for this is to carefully superimpose a textbox displaying
the desired subcategory on top of the text area of the combo box. When
the combo is not selected, you'll see the actual table value; when it
is, the dropdown list will come in front of the textbox and allow you
to select one.
 
A getaround for this is to carefully superimpose a textbox displaying
the desired subcategory on top of the text area of the combo box. When
the combo is not selected, you'll see the actual table value; when it
is, the dropdown list will come in front of the textbox and allow you
to select one.
Click to expand...

Thanks, John. I don't quite understand your workaround, however. Would
you have a sample db with it implemented? My questions are:

1. How do I populate the text box? Let say I have apples recorded in
the table for the Grocery List. I'm storing the ID for apples in my
grocery list table. But in my combo box I can display the Fruit name,
rather than the ID. So I guess I need to do a query for each subform
record to use the subcatid to pull from the subcat table to get the
proper name. Do I do that query on something like "On Got Focus"?

2. How do I update the text box and record changes resultant from
changes to the select box? Once I select Oranges in one drop down box
and then move to the next record, I want it to record in the db and
then update my cover text box.

Thanks so much for your help,

Steve
 
Thanks, John. I don't quite understand your workaround, however. Would
you have a sample db with it implemented? My questions are:
Click to expand...

Well, not for free: I'd have to build it. That goes a bit beyond
volunteer effort!
1. How do I populate the text box? Let say I have apples recorded in
the table for the Grocery List. I'm storing the ID for apples in my
grocery list table. But in my combo box I can display the Fruit name,
rather than the ID. So I guess I need to do a query for each subform
record to use the subcatid to pull from the subcat table to get the
proper name. Do I do that query on something like "On Got Focus"?
Click to expand...

You can base the subform on a Query joining your grocerylist table to
the products table - you certainly should NOT rerun the query.
Alternatively, you can set the Control Source of the textbox to

=DLookUp("[fieldname]", "[tablename]", "[ID] = " & [ID])
2. How do I update the text box and record changes resultant from
changes to the select box? Once I select Oranges in one drop down box
and then move to the next record, I want it to record in the db and
then update my cover text box.
Click to expand...

The textbox will update automatically - you cannot and should not
modify it. It's just displaying the looked-up value from the table;
once the Combo Box updates the ID, the textbox will update all by
itself.
 
Well, not for free: I'd have to build it. That goes a bit beyond
volunteer effort!
Click to expand...

Sure. I was hoping you had a NorthWinds-type of db with a bunch of
cool features and work arounds on display.

I think your suggestions are enough to get me there.

Thanks for all your help.

Steve
 
You can base the subform on a Query joining your grocerylist table to
the products table - you certainly should NOT rerun the query.
Alternatively, you can set the Control Source of the textbox to

=DLookUp("[fieldname]", "[tablename]", "[ID] = " & [ID])
2. How do I update the text box and record changes resultant from
changes to the select box? Once I select Oranges in one drop down box
and then move to the next record, I want it to record in the db and
then update my cover text box.
Click to expand...

The textbox will update automatically - you cannot and should not
modify it. It's just displaying the looked-up value from the table;
once the Combo Box updates the ID, the textbox will update all by
itself.
Click to expand...

Thanks to John's help above I've got it working smoothly except for
one problem. For new records, when I select the combo box (that's
covered by the text box except for the gray down icon) I can select
the right option, but once selected and I jump to the next column, the
text box is not updating. It is recording to the db, but it does not
show up in the text box until I close the form and come back to it (or
by selecting a different combo box in the main form then re-selecting
from that same combo box the record I previously changed).

Now, I did not do the DLookUp that John gave an option for above.
Maybe I'll try that. Otherwise, it seems I need to requery after
update (but he warned against that)

Any ideas?
 
I can select
the right option, but once selected and I jump to the next column, the
text box is not updating.
Click to expand...

Don't requery the Form - just requery the textbox in the combo's
AfterUpdate event:

Private Sub cboCombo_AfterUpdate()
Me!txtTextbox.Requery
End Sub
 
Back
Top