Dynamic field update help?

[donm1021]

I have the following Tables in my database:

Categories | Sub Categories | Products

Categories has the following fields:

Floors | Exterior | Interior

Subcategories has the following fields:

Carpet | Vinyl | Wood | LapSiding | Shingles | Insulation

I have a form called Order Details and when entering the products through my form I have two "Lookup" fields, the first which is CatID which will return a dynamic listbox in the "Categories" field containing "Floors, Exterior, Interior" pulling from the Categories table.

The second is SubCatID which will return a dynamic listbox in the "Sub Categories" field containing "Carpet, Vinyl, Wood, LapSiding, Shingles, Insulation" pulling from the Sub Categories table.

I am however having problems with my third which is "ProdID". I am wanting it to also be created dynamically through a "Lookup" on the Products table. I want when I choose for instance, "Floors" and "Carpet" from the Order Details that only the choices that match those two ID's populate the "ProdID" field and only the products for carpet not for Shingles or other sub categories.

I hope this makes sense...

TIA for any help that you can offer.

Donm

 

[ScottGem]

I'm not sure if your structure or your terminology is incorrect. Categories shouldn't have FIELDS (Floors | Exterior | Interior) but VALUES. The same for Subcategories.

What you are talking about is a standard technique called Cascading or Synchronized combos. If you search here you should find several threads that show you how to do it.

But to do this you need to adjust your tables. Your Subcategory table needs a field for Category so you can filter the subcategories based on the category chosen. Same thing with Products, it needs a field for Subcategory so you can filter. You don't need Category, unless a subcategory can be in multiple categories.

 

[donm1021]

Yes, you are correct I used incorrect terminology.

I have the following:

Categories (Table)
CatID
CatName

Sub Categories (Table)
SubCatID

Products (Table)
ProdID
ProdName

Order Details (Table)
OrderDetailsID
CatID
SubCatID
ProdID

I enter all of the categories, sub categories, and products.

Now when I place an order I want the form to auto-fill the Category, Sub Category, and Product fields so that I can just make choces rather then type in entries.

I have the Category and Sub Category working on the Add New Order Details form but I can't seem to get the Product Field to auto-fill with products from the Products Table? It just lists every single product entered in the products table rather than products based on the Category and Sub Category choices.

Stumped here...

 

[ScottGem]

The problem here is your lookup tables (Cat, Subcat, Prod) don't define the relationships. If a product belongs only to a specific subcat and a subcat to a specific Cat, then you have to show these relations. Otherwise there is no way to autofill them.

 

[donm1021]

Scott,

First of all thanks for your help with this I really appreciate it. Are you saying I should add the following:

Categories (Table)
CatID
CatName

Sub Categories (Table)
CatID <---ADD so that Sub Categories is related to Categories
SubCatID

Products (Table)
ProdID
SubCatID <-- ADD so that Sub Categories is related to Products
ProdName

Order Details (Table)
OrderDetailsID
CatID
SubCatID
ProdID

If so, I just tried that and I get a dynamically created ListBox for Category and Sub Category in the Add Order Details form - however, I still cannot get the Products List Box to populate on this form based on the choices of Category and Sub Category?

 

[ScottGem]

That's closer. If a Subcategory is associated with only ONE Category then you need CatID as a foreign key in the Subcategory table. You still need a primary key there as well. If a Product is associated with only ONE Subcategory then you need SubcatID as a FK in the Product table. So you would have:

Categories (Table)
CatID (PK Autonumber)
CatName

Sub Categories (Table)
SubCatID (PK Autonumber)
SubCatName
CatID (FK)

Products (Table)
ProdID (PK Autonumber)
ProdName
SubCatID (FK)

However you ONLY need ProdID in your OrderDetails table

Order Details (Table)
OrderDetailsID (PK Autonumber)
ProdID (FK)

Since You can get Subcat from the product and the Cat from the subcat you don't need them in OrderDetails. You still need comboboxes on your form to select the Cat and Subcat so you can filter the Product combo. However, those would be UNBOUND controls.

 

[donm1021]

I am really close now - I just found the following article and used it - and it worked until I placed it on my form. Here is what I used:

http://support.microsoft.com/?kbid=209576

But now when I place it on my form, I get a listing of Categories - but when I choose a Category I get the "Enter Parameter Value" box with "Forms!frmSelector!cboCategorySelect" to enter a value rather than a listing of products to choose from in my second combo box.

Any ideas of what I need to do to correct this?

Thanks!

 

[ScottGem]

I'm not clear where you are getting this. When you are prompted like that it means that Access can't find "Forms!frmSelector!cboCategorySelect" so it assumes its a parameter and is promptng you to fill it in. Check the form name and the controlname, make sure you have spelled them correctly.

 

[donm1021]

OK, I don't know what else to do? I did find an Access database already built online that will do what I want to do, with exeception of having a category table (I figure, I really don't need the subcategory).

If I upload this database can someone take a look and let me know what they think would need to be done to accomplish the following:

When using the "Switchboard" and clicking on the "Add an Order and Details" option... you will then see on the form that follows an area called "Order Details" which is an "order details subform" - on that form I need a "Category" column in front of the "Products" column that when a category is chosen from the "drop down" the Product column combo beside it auto-updates with only the products from that specific category.

Any help with this would be much appreciated - not specfically asking anyone to do this - just asking for help on how to do it. I do want to do it myself so that I can learn how... I just am at a loss on where to go, and what to do at this point... totally frustrated to say the least...help...

TIA,

 

[ScottGem]

You seemed to be really close with what you had setup. Why not post that and explain where the problem is. Instead of trying to modify what someone else did?

 

[donm1021]

Because I thought it was close but when I took a closer look at it - whenever I would choose a category.... it not only changed the category on the orders form... but it also changed all of the previous orders in the database to whatever category I had chosen and would overwrite my entries in the database. I don't know exactly what I did but it was totally screwed up.

I am just really frazzled at this point and want to start again... and like I said, I found this and it's is very close to what I need with the exception of the categories. If someone could get me started in the right direction with this it would be great.

 

[ScottGem]

I told you the Category and SubCat combos should be unbound. That's why the values are changing when they shouldn't.

 

[donm1021]

I believe they were unbound... but at this point, I can't be entirely sure. I just think that if I start over and if someone could help me go step by step (using this database) that it would help a lot... and I could learn a great deal from it.

I may have in my frustration, screwed some other things up in my original database too. If someone could take a look at this database and again start me in the right direction on adding a category I would appreciate it.

 

[donm1021]

OK, now I have it back in it's original state with the Category table and then a Products table with the CatID from the Category Table as a FK within the Products table - should I make the CatID in the Products Table a "Lookup"?

I also have readded the combo's in the sub form "order details" - however, when I select a category in combo 1, all of the products in the products table fill combo 2 not just the products specific to that category?

I can't seem to figure this out?

Any ideas?

 

[ScottGem]

Lookup fields should not be done on the table level. Only on a form using comboboxes or listboxes.

I've given you the instructions on what to do. go back and reread them.

 

[donm1021]

OK, I followed the instructions in this thread... and now when I choose a category, an "Enter parameter box" appears wanting me to type in a "CategoryID"?

Not sure why this is happening now?

 

[donm1021]

I'm not clear where you are getting this. When you are prompted like that it means that Access can't find "Forms!frmSelector!cboCategorySelect" so it assumes its a parameter and is promptng you to fill it in. Check the form name and the controlname, make sure you have spelled them correctly.

Ok now I am receving a different Parameter message prompt now:

"Forms!frm Order Details"

I assume that it can't find my form, correct? and that perhaps the form is misspelled or named incorrectly?

I checked and rechecked... my form is named "Order Details Subform" - it is spelled correct. Is the naming convention incorrect (i.e. there can be no spaces in a named form)?

It is auto-filling categories... but I receive the Parameter prompt for the form message. I feel that I am just missing something, probably simple, but just can't put my finger on it.

Help -

 

[ScottGem]

Well first you can't have spaces in object names. If you do, you have to put the object name in brackets. The form name is what shows in the Database Window. That's the name you have to use when referencing it. You also need a controlname there.

If the control is on a subform, you need to reference it thru the subform so the reference changes:

=Forms!mainformname!subformname.FORM.controlname

 

[donm1021]

Well first you can't have spaces in object names. If you do, you have to put the object name in brackets. The form name is what shows in the Database Window. That's the name you have to use when referencing it. You also need a controlname there.

If the control is on a subform, you need to reference it thru the subform so the reference changes:

=Forms!mainformname!subformname.FORM.controlname

Ok, here is what I have in a Query that I created and believe this is where my problem is:

IIf(IsNull([Forms]![frmOrder Details Subform]![cboCategorySelect]),[CatID],[Forms]![frmOrder Details Subform]![cboCategorySelect])

When I use the above - I receive the Parameter prompt. This is taken from the example in one of my above posts - http://support.microsoft.com/?kbid=209576

 

[donm1021]

OK, I started over again. I think I have tried every example for combo boxes known to man and still cannot get it to work.

I am going to attach a picture of what I have now.

A - Category Combo on "Order Details" subform - Unbound with the following in the Rowsource on the "Data" tab for the cboCategory control:

SELECT [Category].[CatID], [Category].[CatName] FROM Category;

B - Products Combo on "Order Details" subform - Unbound with the following in the Rowsource on the "Data" tab for the cboProducts control:

SELECT [Products].[ProductID], [Products].[CatID], [Products].[ProductName] FROM Products;

C - Is the "Order Details Subform" within the "Add Order and Order Details" form.

Whenever I choose a Category: instead of the Category Name appearing I get the CatID and then in the Product: instead of a product I get an error "Data Type mismatch in criteria expression".