Long question, simple solution!?

[woolyfoot]

Hi

I was wondering if anyone can help me.
I am trying to build a database as part of my IT A-Level coursework, but I have little experience with Access. Although I have built the main part of the database, I am now stuck with something which I am sure is quite simple, if only I knew how to do it!!
I have got four tables at the moment, Suppliers, Category's, Products and Orders. From these I have made a form which will be used to put in new orders, and it works so that when it is filled in, the "order" is automatically entered into the Orders table.
My problem is that in this form I have drop down boxes for category name, supplier name and product name, which are looked up and taken from the respective tables. I want to have it so that when I select a category from the category drop down list, it automatically limits my choices in the suppliers list, and offers me only the suppliers which supply products in the category I have chosen. Then the products list should only be the products from the selected supplier.
I apologise if that makes absolutely no sense at all, it is very difficult to explain by writing it down... my teacher can not help me, she appears to know even less about databases than me, and I have had to teach myself the very little I know! I know this must sound very pathetic and trivial, but I just can not make it work! Any help would be greatly appreciated!
I also have another slight problem in that I can get my form to calculate the total cost of the order, by taking the price and the quantity, but then this value does not appear in my orders table.
Thanks for taking the time to read this and helping me if you can!! I am really grateful!

Charlotte xx

 

[trucktime]

Solution

Sounds you need cascading combo boxes, search the forum on this topic.
It is a very popular subject.

You can also check this link, there are some downloadable examples here.

http://www.candace-tripp.com/_pages/access_downloads.asp

As for the calculated field: this field should not be in a table.
It can easily cause inaccuracies and it also leads to storage overhead.
A good practice is to do the calculations in a query and make the results
available in a form and/or report.

Good Luck.

 

[mwn1218]

In the query your combo box is using to pull the information (access this by clicking on control source), under the Category field (add this field if it is not there) and under Total Row select where and add this code in the criteria row: (if you do not see the Total Row click the Sigma looking icon in the tool bar).

[Forms].[YourForm]![Name of your Combbox]

Hope that helps

 

[woolyfoot]

i'm still having big problems with this and i dont know why!!! ive tried the things suggested, and have spent all day looking at various posts and sites, i just dont know why it wont work... i'm going crazy!!!