Attn John Vinson - Combo box advice

[Dave B]

I'm having no success following the instructions you gave to Connie's post
on Mar 26 regarding the two combo boxes where one is a filter for the other.
You refered to them as cboCategory and cboItem.

I have created a form with two combo boxes similary named and they get their
data from fields - Category & Item, created in "Table1".

Query - Under the Item field I created the criteria line "
=[Forms]![Form1]![cbocategory]"

I have done the code in the "after_update" event of the cbocategory box.

Private Sub cboCategory_Afterupdate()
Me!cboItem.Requery
End Sub

When I return to the form - all - of the selections remain in the Item
combobox after I have made a selection in the category box.

Hopefully I've provided enough detail for you to suggest what I'm doing
wrong.

Thank you

Dave

 

[John Vinson]

Query - Under the Item field I created the criteria line "
=[Forms]![Form1]![cbocategory]"

I don't like the quotemarks there... Please open the Query in SQL view
and copy and paste the actual SQL text to a reply here.

 

[Dave B]

SELECT Table1.Item
FROM Table1
WHERE (((Table1.Item)=[forms]![form1]![cbocategory]));

The "" were only in the message I posted not in the query.

Thanks
Dave

Query - Under the Item field I created the criteria line "
=[Forms]![Form1]![cbocategory]"

I don't like the quotemarks there... Please open the Query in SQL view
and copy and paste the actual SQL text to a reply here.

 

[John Vinson]

SELECT Table1.Item
FROM Table1
WHERE (((Table1.Item)=[forms]![form1]![cbocategory]));

The problem is that you're searching for Categories in the Item field.
You should be searching the Category field instead:

WHERE (((Table1.Category)=[forms]![form1]![cbocategory]))

 

[Dave B]

After hours of checking and re checking still no success. The best I can get
is the two comboboxes with all data, from their respective fields in Table1,
showing in the form. I cannot get the CboCategory to filter CboItem. I have

Item Category
1 a
2 b
3 c
4 a
5 b
6 c
7 a
8 b
9 c

I'm expecting that a selection of "a" (without quotes) in CboCategory
will produce list of "1-4-7" only in CboItem.

I don't know what else I can tell you..Thanks for trying to help.

Dave

SELECT Table1.Item
FROM Table1
WHERE (((Table1.Item)=[forms]![form1]![cbocategory]));

The problem is that you're searching for Categories in the Item field.
You should be searching the Category field instead:

WHERE (((Table1.Category)=[forms]![form1]![cbocategory]))

 

[John Vinson]

After hours of checking and re checking still no success. The best I can get
is the two comboboxes with all data, from their respective fields in Table1,
showing in the form. I cannot get the CboCategory to filter CboItem. I have

Item Category
1 a
2 b
3 c
4 a
5 b
6 c
7 a
8 b
9 c

I'm expecting that a selection of "a" (without quotes) in CboCategory
will produce list of "1-4-7" only in CboItem.

Please post the following properties of each of the two combo boxes:

Row Source (post the SQL)
Column Count
Bound Column
AfterUpdate event (post the code)

 

[Dave B]

CboCategory

Row Source SELECT Table1.ID, Table1.Category FROM Table1;
Columm Count 2
Bound Columm 1

Private Sub Cbocategory_AfterUpdate()
Me!Cboitem.Requery
End Sub


CboItem

Row Source SELECT Table1.ID, Table1.Item FROM Table1;
Columm Count 2
Bound Columm 1

 

[John Vinson]

CboCategory

Row Source SELECT Table1.ID, Table1.Category FROM Table1;
Columm Count 2
Bound Columm 1

Private Sub Cbocategory_AfterUpdate()
Me!Cboitem.Requery
End Sub


CboItem

Row Source SELECT Table1.ID, Table1.Item FROM Table1;
Columm Count 2
Bound Columm 1

In order to limit cboItem to those items from a specific category you
must (as described upthread, and which I thought you'ld done!) base
cboItem on a Query which limits the items to those in a particular
category.

This RowSource explicitly and specifically selects all items from
Table1.

The first combo is flawed as well; it seems to select an individual
ItemID (I presume that ID is the Primary Key of Table1).

If you don't have a separate table of Categories (which would be a
very good idea), change cboCategory to

Row Source: SELECT DISTINCT Category FROM Table1 ORDER BY Category;
Column Count 1
Bound Column 1

to allow the user to select any of the existing categories;

and cboItem to

SELECT Table1.ID, Table1.Item FROM Table1
WHERE Table1.Category = Forms!yourformname!cboCategory;

This will limit the list of ID's and Items to those matching the
selected category.

 

[Dave B]

I have the query done as you had earlier stated in the upthread. If the
latest data I sent does not reflect this then that may well be the
oblem - not reading the query. I will try again later this weekend.....

Dave