Cascading Combo boxes to give unique ID. SO CONFUSED!

[fxkqwan]

Hi,

I've built a database that records samples sent out by our business and monitors stock levels accordingly.

The user input form for dealing with samples requests is built over a cascade of combo boxes which go from sample type>product range>item>colour, once colour is selected the form finds the unique ID which correlates to a stock level monitor.

As standard, my cascaded combo boxes refer to several queries with the following form:

Private Sub Product_AfterUpdate()
Me.Item = Null
Me.Item.Requery
Me.Item = Me.Item.ItemData(0)
End Sub

The queries increase in complexity to select the right records, the colour query looks like this:

SELECT DISTINCT Products.[Colour Name], Products.[Sample Type], Products.Product, Products.Item
FROM Products
WHERE (((Products.[Sample Type])=forms![Order Details]![Sample Type]) And ((Products.Product)=forms![Order Details]!product) And ((Products.Item)=forms![Order Details]!Item));

Some sample types do not need detail from every combo box (if the user chooses 'folder' from sample type, then there is no need to enter colour etc.) so I would like the form to only require further input when necessary.

The form will autofill a combo box if the prior combo box has been manually filled, but I'd like this to all be automated so that the user can select an item which doesn't require colour input and have the unique Id filled automatically to avoid a clunky interface.



I figure the way out might be some kind of 'if' command which updates the unique ID and stock if the sample type is 'folder', is there a way to do this via VBA or a query?
Or is there a way to make all comboboxes auto-update with no user input?

Thanks

[orange]

There are 3 free videos at Datapig related to Cascading combos.
Have you watched them?
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html
http://www.datapigtechnologies.com/f...combobox3.html

I think this would be helpful.

[fxkqwan]

Thanks, I've looked over the videos, afraid that's not it though, I've mastered that far.

What I'm trying to get is a system where if only 'products' is relevant, then only 'products' needs to be selected, and so on for more complex items through 'items' and 'colour'.

I've tried to solve it by rewriting my select query to the following containing nested iif statements:

SELECT DISTINCT Products.ID, Products.[Colour Name], Products.Item, Products.Product,

IIf((([forms]![Order Details]![Sample Type])="Binder" Or ([forms]![Order Details]![Sample Type])="Sample Card" Or ([forms]![Order Details]![Sample Type])="Brochure"),

(SELECT DISTINCT Products.ID, Products.Product FROM Products WHERE ((Products.[Product])=forms![Order Details]!Product)),

(IIf((([forms]![Order Details]![Sample Type])="Swatches"),(SELECT DISTINCT Products.ID, Products.Item FROM Products WHERE ((Products.[Item])=forms![Order Details]!Item)),

(SELECT DISTINCT Products.ID, Products.[Colour Name] FROM Products WHERE ((Products.[Colour Name])=forms![Order Details]!Colour)))))
AS IDSELECT
FROM Products;


Which returns the following error: "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

Am I trying to get to complicated?

[orange]

That's a strong possiblity.
Do you have a data model?
Can you post a picture of the relationships?

Can you more clearly identify your environment and exactly what it is you are trying to do?

I'm trying to get is a system where if only 'products' is relevant, then only 'products' needs to be selected

is just a little too vague for me.

I'd recommend watching the datapig videos again.
Set up an example with your data and work thru the 1,2 and 3 combo examples. Understand the examples.

Then, work on your specific problem.

Good luck.

Note: If you want to know more about subqueries, go here http://allenbrowne.com/subquery-01.html

[fxkqwan]

The form works with two tables, 'order details' and 'products'. It draws all info from the 'products' table, through cascading combo boxes to deliver a unique code for each particular sample that can be ordered. There are four combo boxes, 'sample type', 'product', 'item' and 'colour' which correspond to fields in the 'products' table.

The main function of the form is to give the user a clear way to get through our many products through to the unique ID which links to stock levels being monitored and is used for filling the order details table which is used for fulfilling sample orders.

The particular problem I have is that not all products are of the same type, and different product types need different levels of detail to pin them down and deliver a unique code.
For instance, we have some sample binders, which is found by selecting 'binder' in sample type, then the product range for that binder in product, then the particular binder it is in item.
However, we also do loose samples which need to have 'loose sample' selected in sample type, product range selected in product, which item in that product range it is in item and finally what colour it is in colour.
AND (!) we have brochures which are found through selecting 'brochure' in sample type, and then the particular brochure it is in product.

What I want to have is a situation where only 'sample type' and 'product' are shown when the form is loaded, and extra combo boxes are shown when relevant. The user will only need to fill out what is necessary and access will deliver the unique code and show the stock level based on that.

My form is working to be able to run through the combo boxes and deliver the unique ID, and I thought that the ItemData(0) part of my VBA code would automatically populate all extra combo boxes without any need for user input.
My problem is that for this autopopulation to take place, the form needs user input on the previous combo box, which is what I want to avoid, as for things like binders and brochures, the user should only have to fill in what's necessary.

My queries at the moment run so that it narrows down the combo boxes from input in the previous combo box ('product' will show relevent results according to what's in 'sample type' etc.) and all fields 'sample type', 'product', 'item' and 'colour' have something in them (even if, in the case of brochure and binder, some are just a duplicate of the previous field). After going through the options, the unique ID is derived from the 'colour' combo box.

I want a way so that Access will either:
a) automatically fill in the rest of the combo boxes if there is only one option so that 'colour' gets automatically filled if there are no other options for 'item' or 'colour',
or
b) find the unique ID for the product based off of earlier combo boxes.

And I can't seem to find a way to do this.

Am I being clearer now? Thanks for your continued help, I really appreciate it.

[orange]

Can you attach a sample mdb? Take out any confidential stuff.