categories form combo box

[John thomas]

I have a ordering program that works great with my products table with the use of a combo box to find and place products into the table
However now i need categories and i cant understand how to get this to work .The combo box lists all the categories but then i need to select a categorie and somehow display all the products of that categorie and then place the selected product into my order details sub form
All help gratefully recieved
Please keep it simple i am a newbe

 

[jdraw]

Sounds like you need tables for Order, Products and ProductCategory.

these videos will help with combo boxes.
You will be looking for Cascading combos -- see

http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

Good luck with your project

 

[Sketchin]

I also found this link for cascading combos helpful:
http://www.fmsinc.com/microsoftaccess/forms/combo-boxes/cascading.html

 

[jdraw]

There are lots of tutorials and info on cascading combo boxes

Here's another link with a tutorial
http://www.techonthenet.com/access/tutorials/combobasics2007/basics01.php

 

[John thomas]

Hi thanks for your help
I followed the tutorial for cascading combo boxes from the first one you suggested ,using queries to do the job .But i cant get it to work .i can get the first combo to work properly,but the 2nd combo when i click on it askf for a parameter to be enterd .It sems that is cant see the choice entered in the first combo
I have spent ages trying to find my eror but no luck any suggestions

 

[jdraw]

Post some code or a database with the issue.
Usually if you're being prompted for an unexpected parameter - there is a spelling mistake.

 

[John thomas]

hi i can i somwhow just post the form or will this not work .I dont really want to post my entire data base .I have checked out the spelling and all sems ok .does it matter in the querie the order of events .thanks for your help it is midnight in the uk and i am going to bed will look for you reply tomorrow

 

[jdraw]

I don't know if you tried to post something or not --- Nothing arrived anyway.

My best suggestion would be to go through the videos step by step and try again.

 

[John thomas]

Hi i hope this may make sense Products is the Table
from ist combo box catogories
SELECT products.category FROM products GROUP BY products.category ORDER BY products.category;

from second combo box item name
SELECT products.itemname FROM products WHERE (((products.category)=[forms]![combo1].[combocat])) GROUP BY products.itemname ORDER BY products.itemname;

 

[jdraw]

For the first combo, I'd be tempted to use
SELECT DISTINCT products.category FROM products ;

What is your Form Name??

For the second,
SELECT products.itemname FROM products WHERE (((products.category)=[forms]![combo1].[combocat])) ORDER BY products.itemname;


I think the syntax should be Forms!YourFormName.ComboName

 

[John thomas]

Hi jdraw
Thanks for taking the time to reply
I call the form combo1 as this is just experimental at this stage to get it to work .once i know i have it right i can go to the nest step .I will try what you suggest
many thanks

 

[jdraw]

John,

Here's a small sample I put together on Cascading/Dependent combos. I tried to make it relevant to your situation.

Good luck with your project.

 

[John thomas]

Hi jdraw
Thanks so much for all your help
I am shure i can now get this to work .I will let you know

 

[jdraw]

You are welcome.
If you do have a problem with the combos and things don't show up as you expect. Then go to the combo box properties-> format-> and check if the field has a 0" (which implies hide this) make it 1" or so and see if you get the data you expect.

 

[John thomas]

Hi jdraw
It works |
I have learnt so much from this .I now know a little about sql a liitle on programming and my knoledge on combos vastly improved and I can now look at a querie and it makes some sence
I almost gave up but persisted and its all been worthwhile

Once agaain many thanks for persovering with me
Best regards John

Onwards to the next problem

 

[jdraw]

You are very welcome. Have a Merry Christmas!