Problem with Cascading Combo Boxes

[sprtrmp]

I have a table showing products that fall into 8 different categories (shirts, pants, hats, for example) each of these categories is further divided by size (lets say small, medium, large, etc.). all products are on the same table, but there is a foreign key related to another "category" table.

I am trying to devise a form (based on a query) that will have 2 combo boxes, the first combo will allow me to select one of the 8 top level categories, and the second combo will have a breakdown of different sizes for each of those items.

how do I get the form to filter by 2 separate combo boxes, then be able to make the record for the size that I have chosen active, so I can make changes to the quantity for THAT record??

I am sure I didn't include all the information needed, so go ahead and ask away



Thanks

[June7]

Method I prefer for searching and filtering form http://www.allenbrowne.com/ser-62.html

[sprtrmp]

...anyone? ...anyone?

[ssanfu]

So the new form will be bound to a query that returns all records.
You will need to have two unbound combo boxes in the form header.

The first combo box will select the category.
The second combo box will be limited to the size for that category.
Then VBA code will be required to set a filter on the form for the category and/or size. This will limit the records that matches the combo boxes.

Clear as mud?

[sprtrmp]

ALSEInventory.zipabsolutely...
I sort of figured all that, but not in so few words. it was a LOT more confused in my brain.

I have attached a scaled-down version of what I am working on, basically, it is just 2 forms: one that lists the "categories" and the other that lists the products (ProductCat_FK) that seperates them into the categories on the other table.

I have a query (qryExpiriment) that I had set up to accept criteria from a combo box on a form (frmExp) to filter the query and show only those items whose ProductID_FK matched what was chosen in the combo box. (I don't know if that is anywhere near right, but at one point, at least that much worked).

the second combo box on the form should be where I would pick the size and be able to edit the quantity (which is all I really need to do at this point).

Please see the attached, and let me know where to go from here.

Thanks
Mike

[ssanfu]

See if this helps you........ (didn't want to write a book)

Enjoy.....

[CJ_London]

Try the following - change names to suit.

Note the use of the name of the first combobox in the second combo rowsource, it should not be the same name as a field in your tblSizes and should be enclosed in square brackets

Combo1 rowsource (combo called say cboCategories)

SELECT categoryPK, categoryName from tblCategories ORDER BY categoryName

Combo2 rowsource - note type the sql here, not in a query

SELECT SizePK, SizeDesc from tblSizes WHERE categoryFK=[cboCategories] ORDER BY SizeDesc

Then in the Combo2 enter event put

Combo2.requery

you may also want to put this code in the current event as well for when you return to the record


If your form is a datasheet or continuous form then the above will not be sufficient - see this link

http://www.access-programmers.co.uk/...d.php?t=275155

the second combo box on the form should be where I would pick the size and be able to edit the quantity

the above will pick the size, you will need a separate control to edit the quantity

[sprtrmp]

I definitely like it, but I have one little issue: I do not want the list of items to show up at the bottom of the form. I guess what I mean is that I want there to be one record available, and ONLY after the "size" selection has been made in the second combo. I don't mean to be picky, but the reason for that is that I don't want the people who will be working with this db to be able to get to ANYTHING that they haven't explicitly navigated to. (we are all barely functioning government employees and I know someone is going to open up the form and immediately start hacking away at the first thing they see). otherwise, the "GOOD" data I feed into this thing is going to last about 10 seconds
Any ideas? (I know, "some people", right?)

Another reason for that is that I will need to add and "Add" button at some point that will allow us to make changes when we get new items in, and I want us/me/them to be able to see what they are adding to for clarity purposes. (I should be able to handle the "Add" part though, don't waste your time on that part)

I seriously appreciate how far you got me though.

[ssanfu]

So these are just demos to help you out.. not trying to design your dB; after all, I know nothing about your requirements.

How about this??

[ssanfu]

Oops, didn't remove enough code.

Replace

Private Sub cmboSize_AfterUpdate()
    If IsNull(Me.cmboSize) Then
        Me.Filter = "[CategoryID_PK] = " & Me.cmboSelectCat
    Else
        Me.Filter = "[CategoryID_PK] = " & Me.cmboSelectCat & " AND [ProductID_PK] = " & Me.cmboSize
    End If
    Me.FilterOn = True
End Sub

with

Private Sub cmboSize_AfterUpdate()
    Me.Filter = "[CategoryID_PK] = " & Me.cmboSelectCat & " AND [ProductID_PK] = " & Me.cmboSize
    Me.FilterOn = True
End Sub

[sprtrmp]

Well, you are doing a whole lot better than I could.

that works just about perfectly.

I can't thank you enough!!!

Mike

[ssanfu]

No problems.


Ready to mark this solved?

[sprtrmp]

Sorry about that, I was so excited to have the thing working, I forgot all about that!