Full Version: Dynamically created combo boxes?
UtterAccess Forums > Microsoft® Access > Access Forms
donm
Hello - I have a customer orders database that when a user adds an order instead of typing in choices they will simply choose items from a combo box. This I know how to do.
However, because there will be so many items (Products) to choose from the combo box will be too large to scroll through. So, I thought a solution would be to create a "Categories" table linked to the "Products" table.
Then on a form have two combo boxes. One for Categories the other for the Products. The first combo (Categories) will be auto-filled of course with available categories. When a user chooses a category, the second combo box (Products) will auto-fill with only the available Product choices from that category.
The database that I have currently only has products, and no categories table as stated before and the products listing will be too long.
I think the first place for me to start would be to add the "Category" table - making the "CatID" an auto-number and the primary key for this table.
Here's where I begin to get lost and need help. Then in the existing "Products" table add the "CatID" as a foreign key? (not sure).
Then what I would like to see is a form which I already have called "Add an Order and Details" which a user can add the customer details and order details... the order details is pulled from a sub form called "order details" - here's where I really get lost.
On this form is where I want the combo boxes to auto-fill and just be choices for the user to choose from. A Category combo and a Products combo.
I can upload the database in it's current state - if anyone could take a look and then make suggestions on where I need to start with getting these combo boxes added to the "Add an Order and Details" form I would really appreciate it.
TIA,
Tim
Does that mean that to buy a product, you first need to know the category and then select from the available products from that category?
If the product list is too long why not allow to type in the name of the product and combo box will auto correct that for you.
But if you must do two combo boxes, here is the tutorial
http://support.microsoft.com/default.aspx?...kb;EN-US;289670
Good luck
Jerry Dennison
Welcome to UtterAccess!
Is Tim has pointed out, what you're asking for is Cascading Comboboxes. There are several good illustrations in the Code Archive (I think that's where they are).
On a side note: your table structure is sound but you have defined outer joins in the relationship view. These joins are meaningless outside of queries and really should be changed back to inner joins in the RW.
donm
Tim - I tried the tutorial and whenever I choose a category in the first combo box, the second fills with every product in the database instead of just the choices for that specific category?
Any ideas?
Jerry, I did a search on combo boxes in the Code Archive and really didn't see anything that would do the trick? Do you know of one specific example there that I could look at?
Oreally appreciate your help,
Jerry Dennison
Try Candace Tripp's website. She has some examples that can be downloaded.
http://www.candace-tripp.com/pages/access_downloads.aspx
donm
I seem to have this working, I mean the categories are displaying in the category combo... but when I click on the second combo (Products) I receive the following error:
Microsoft can't find the macro 'Me.' - and the product combo is empty.
Anyone know why this happens?
TIA -
Jerry Dennison
Where are you putting this code? It must be in a form or report module. You cannot use the Me. predicate in a control property (i.e. event property).
donm
Jerry - I was told to do this - in the GotFocus event of the Products combo use the following code:
e.cboProduct.RowSource = "SELECT ProductID, ProductName FROM PRODUCTS WHERE CatID = " & Me.CboCat & ";"
Me.CboProduct.Requery
The Products combo is on my "Order Details Subform"
Jerry Dennison
Then you need the full path to the control.
orms!MainFormName!SubFormName!ComboboxName.Rowsource
donm
That doesn't seem to work either? Could it be that my form names have spaces in them? (i.e. "Add an Order and Order Details (Main form)" and "Order Details Subform" (sub form)
Jerry Dennison
If you have spaces you must enclose the names in braces []'s. I encourage you to never use spaces or special characters when naming.
donm
Would this be correct then?
orms![Add an Order and Details]![Order Details Subform]!cboProducts.Rowsource = "SELECT ProductID, ProductName FROM PRODUCTS WHERE CatID = " &Me.cboCategory & ";"
Me.CboProduct.Requery
Jerry Dennison
I don't know. What happens when you try it?
donm
I get the following error:
Microsoft Access can't find the macro 'Forms!Add an Order and Details!Order Details Subform!cboProducts.'
The macro (or it's macro group) doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name of the macro's macro group was last saved under."
And it doesn't matter if there are spaces or not (tried it both ways) and I receive the same error message?
Jerry Dennison
Where are you putting this code? It must go in an event module.
donm
OK, I'll post a graphic of what I have with an explanation -
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;
© - 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".
What I am trying to do is get the Category combo to auto-fill with the available categories and then when I choose a category the Products combo will auto-fill with only those Products that are specific to the chosen category.
THere is the solution that I was given -
-------------------------------------------------------------------------------------------------------------------------------------------
"First make sure the Column Count for the Cat Combo is set to 2 and the Column Widths set to 0";1".
Second, in the GotFocus event of the Products combo use the following code:
Forms![Add an Order and Details]![Order Details Subform]!cboProducts.Rowsource = "SELECT ProductID, ProductName FROM PRODUCTS WHERE CatID = " &Me.cboCategory & ";"
Me.CboProduct.Requery
Make sure that the data types on your tables match up. CatID is an autonumber, right? If so, it needs to be a Long Integer in the Product table."
------------------------------------------------------------------------------------------------------------------------------------------
This is where I am now and I am getting the "Microsoft can't find the macro..." error
Jerry Dennison
To display something meaningful in the combobox you must hide the bound column by setting it's columnwidth property to 0" and the column you wish to display to something like 1" or 2".
Is for the second combobox, it's on a subform. Using the Me. predicate can sometimes cause unpredictable results when used with subform controls. Use the full path to the first combobox just as you used the full path to the second combobox.
donm
Still get the same result...

"Microsoft can't find the macro..."

I am begining to think there is no one who can get this to work, I want to display the available categories in the first combo and and then in the second combo only auto-fill the combo with the Products specific to the chosen category in combo 1.

All of this takes place on a subform.

I believe I have tried every example known to mankind and have yet to get any of them to work.

I do appreciate the help... and hope that someone can help me come up with a solution.

BTW... the "category" combo is working. It's the Products combo that is causing all of the problems.


Edited by: donm on Thu Dec 15 8:54:48 EST 2005.
Jerry Dennison
Post the db.
donm
Ok, here is the database...
Edited by: donm on Thu Dec 15 10:17:18 EST 2005.
Jerry Dennison
Ok, you must add the code to the event procedure of the event property. You have it in the macro procedure for the event property. Click on the down arrow next to the event property and select [event procedure]. This is where you need to put the code. If you had any macros defined they would show up in the list of options to choose from but you would still want to select [event procedure].

PS. you should not define outer joins in the relationship view. These should be limited to queries only. (they serve no purpose in the relationship definition). You should also enforce referential integrity everywhere it makes sense to do so (i.e. between a product and its category).
donm
I apologize for my ignorance, but I am in the learning stages of Access, and again I really appreciate your help.
You say, "you must add the code to the event procedure of the event property."
Is this the screen after you click on "properties" for the cboProducts and then the event tab? Further, you say the "event property" would that be "Before Update, After Update, GotFocus, etc." which one?
Again, my apologies for my ignorance.
donm
hmmmm? It got real quiet?
Jack Cowley
If you go to Jerry's last post you will see to the right of his avatar a list and the second item is Reply. If you click there and post your question in the post box at the bottom of the form Jerry will be notified of your post and will, when he can, respond. You are using the Quick Reply and this does not notify the person you are replying to that you have responded so unless they happen to peruse the forums they will not know that you have posted a response.
th,
Jack
donm
I apologize for my ignorance, but I am in the learning stages of Access, and again I really appreciate your help.
You say, "you must add the code to the event procedure of the event property."
Is this the screen after you click on "properties" for the cboProducts and then the event tab? Further, you say the "event property" would that be "Before Update, After Update, GotFocus, etc." which one?
Again, my apologies for my ignorance.
Jerry Dennison
Did you read my last post? It gave step by step instructions on where to put the code. Click in the event property (this is where you have the code now) and you will notice it has a down arrow to the right. Click on this arrow and select '[event procedure]'. Once you select [event procedure] click on the button with the three ...'s and this will open the event procedure code module (it actually opens the form code module at the correct control event procedure). Paste your code here.
donm
Jerry - great! Getting closer now... I pasted the code there and now I receive the following error:

Microsoft Visual Basic - Compile Error - Method or Data member not found and when I click "Ok" the following piece of code is highlighted in "blue" near the end of my pasted in code - ".cboCategory" from the following line of code:

Me.cboProduct.RowSource = "SELECT ProductID, ProductName FROM PRODUCTS WHERE CatID = " & Me.cboCategory & ";"




Edited by: donm on Thu Dec 15 22:38:18 EST 2005.
Jerry Dennison
Since you are in a subform you should use the entire path to the control you wish to use.
orms!MainFormName!SubFormName!ControlName
donm
Ok, now I get a run-time error:

Run-time error '2465':
Microsoft Access can't find the field 'cboProduct' referred to in your expression.

Ooooop's - found the problem here... there is supposed to be an "s" at the end of cboProduct.

However, now I have another error something about a syntax error in my query. I'll check it out and if I have any problems... I will post.

Thanks for your help - I really appreciate it!




Edited by: donm on Thu Dec 15 22:57:09 EST 2005.
Jerry Dennison
That's because you've misspelled it. It's cboProducts
Anyway, it still isn't going to work because you have no categories assigned to any products in your product table. Therefore, there is no link between a product and the category(ies) it may belong to. If a product can belong to more than one category, you have no method for defining this relationship (it will require a many-to-many relationship between category and product which will require an intermediate table).
donm
A product can only belong to one category.
Jerry Dennison
Anyway, you still have another significant problem I just noticed. Your cboProducts is not bound to any field in the underlying table. This means that even if you make a choice it won't be recorded. You have this same exact problem with the product data entry table. You don't have the category combobox bound to the catID field (in fact, you don't even have the catID field included in the form's recordset). This means that if you make a choice in that field it will display for as long as the form is open but never gets recorded with the product information.
donm
OK, I am not giving up on this. I have started again, I have the Category table added and I have added the CatID to the Products table. The CatID field is now a lookup and lists the available categories that I have entered into the Category table via the new "Category" form. The "View Products and Prices" form now allows me to add new products and links the proper category to the product in the Products table.
think I am now ready to attempt the combo boxes one more time.
I have attached my database to this post... can someone take a look and make sure that the structure/relationships are sound before I begin?
I appreciate it.
Jerry Dennison
Your structure appears to be ok. You should eliminate the outer joins in the relationship view. You do not need to eliminate the cascading comboboxes if it makes sense to use it.
donm
Thanks again Jerry - not sure if I understand what you mean by "You should eliminate the outer joins in the relationship view"?
Since uploading my database I did join the CatID in the Category Table to the CatID in the Products Table as a one-to-many relationship, with "Enforce Referential Integrity". Is that what you were referring to?
I will upload it again - take a look.
I did start the combo boxes again - they are not working in it's current state. But I am working on it.
donm
Whooo Hooo ! Jerry, I finally got it working!!

The categories and products are now working just like I need them to - I choose a category, and the products fill with only the products specific to the category chosen!

Thanks so much for your help!!


BTW - if anyone else needs something like this in the Access DB - I used the example from http://support.microsoft.com/?kbid=209576
Edited by: donm on Fri Dec 16 9:06:17 EST 2005.
donm
Ooop's I got a little too excited before I actually checked everything out. The combo boxes are working as they should.
My problem now is this. Whenever I am using the "Add an Order and Details" form. I chose a category, and then a product from that specific category for an order - then in the "Record" I click the arrow to advance to the next item ordered. Say there are 5 records for Customer 1 showing in the "Order Details Subform" - I chose a new category and product for record 2... all 5 records change to whatever I chose. However, it only appears to be changing on the form and not actually writing it to the database?
Does that make sense? I hope that I explained the problem right.
I will post my database again... any ideas on what I need to do to only change the record I am working on and not all?
Jerry Dennison
This is what I was talking about earlier. You have not bound the comboboxes to a field in the underlying recordset (this is the ControlSource property). You must bind the control to a field.
donm
OK, I see what you mean with the Product combo.
Could you tell me what I need to do with the Category combo? I don't see anyway to bind it to the CatID? Do I need to add the CatID to the Order Details Table also?
Jerry Dennison
No, the category combo does not need to be bound to any field in the order detail subform. It is simply a convenience for the end user to filter the product to a smaller list of items to scroll through when selecting a product.
donm
OK, so I bind the product combo to the ProductID - I don't need to bind the Category combo? If I do that it should work ok?
Jerry Dennison
It should.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.