Is this possible?

[Lol999]

Okay, after many hours of trawling the net and head scratching I have to ask the question:

is it possible to have a form with 2 cascading comboboxes which dictate which records are shown on the subform?
in addition, I wish to make entries or modifications to the records displayed in the subform.
The data structure for this is a simple 2 table database with a 1 to many relationship.

I have got so far only to fall at the last hurdle.
I have tried using queries for the rowsource of the comboboxes, I have tried al manner of vba, all to no avail.

If someone can put me out of my misery they will be a prince amongst men (or women)

Cheers, Lol

 

[pbaldy]

Sure, presuming the combos return something that can filter the subform. I'd change the recordsource SQL using them in the criteria. Might help to see your effort.

You'll only be able to edit the data if the record source allows it (isn't read only). Typically a subform would be based on the many table, the main form on the one.

 

[pbaldy]

Sure, presuming the combos return something that can filter the subform. I'd change the recordsource SQL using them in the criteria. Might help to see your effort.

You'll only be able to edit the data if the record source allows it (isn't read only). Typically a subform would be based on the many table, the main form on the one.

 

[Lol999]

Okay so I've got it close, bit still a few quirks. I've got the subform to accept data and write it to the correct table but I keep getting a message asking for an entry to a table.
Secondly, if a user decides to keep the first combobox on the same category but change the product it writes the ID for the product as the first in the table and not the correct one.

Best thing is to have a play with it and see what you make of it.

many thanks, Lol

 

[Lol999]

Much apprecciated Pat, but so I can learn just how would you do it?

many thanks, Lol

 

[Lol999]

Hi Pat, I've started building the query in access query builder and I'm having syntax problems with the IsNull function.
Is there an obvious problem below:

SELECT Tbl_Category.ID_Tool_Category, Tbl_Category.Category, Tbl_Product.ID_Product, Tbl_Product.ID_Tool_Category, Tbl_Product.Part_No, Tbl_Product.Details
FROM Tbl_Category INNER JOIN Tbl_Product ON Tbl_Category.ID_Tool_Category = Tbl_Product.ID_Tool_Category
WHERE (((Tbl_Product.ID_Product)=[Forms]![frmProduct]![cboProduct_Picker]) AND ((Tbl_Product.ID_Tool_Category)=[Forms]![frmProduct]![cboFindCategoryID])) OR (([Forms]![frmProduct]![cboProduct_Picker] IsNull) AND ([Forms]![frmProduct]![cboFindCategoryID] IsNull));

Cheers, Lol

 

[pbaldy]

Note the space in Pat's post. It wasn't a mistake.

 

[Lol999]

Hi, yes I found the spaces between IS and Null

I've built a query at last and set it as the record source for the main form. I'm a little unsure as to what to do with the combo boxes though.
I've attached the latest heresy for general perusal.

Many thanks, Lol

 

[Lol999]

Okay, so I've set the recordsource for the main form to a query, qryForm_Builder as based upon Pat's suggestion.
I've created two unbound cascading combo's, the first has it's rowsource set to find the product category, the second has it's rowsource set to find products based upon the category selected in the first combo.

When I open the form I get a input box asking for a value from the second combobox and everything locks up at that point.

I think I've done everything as I should but I'm making little headway if someone could just check the innards that would be great.

many thanks, Lol

 

[Lol999]

I got it working. I set a query for the source of the main form, created cascading comboboxes and set the rowsource via vba and a select statement.

Now let us never mention it again.

 

[pbaldy]

Now let us never mention it again.

LOL! Glad you got it sorted.