multiple level combo boxes to select a record 1

[roddy17]

hi there,
I have a growing list of shipments. Now they can be sorted in two manners. The form will have 3 drop-down boxes.
First, the user may want to use the top drop-down box which sorts them by distributor and then the bottom drop-down box displays all of the shipments that have that particular distributor for example. Or, the user can use the second drop-down box which has various suppliers of shipments, the user selects a supplier and then again the bottom drop-down box displays all the shipments from that particular supplier.
and then of course, the tab control form contains all of the information linked to the particular shipment that is selected in the bottom drop-down.

my questions are the following:
1. how do i store this? I mean, i assume i have three tables set up - one for the shipments, one for the supplier, and one for the distributor. (They need their own tables cuz the suppliers can exist even if they haven't sent a shipment, and the same for the distributor.)
But do the tables just have regular join relationships?

2. how do i code for the drop-down boxes to do what i want them to do? Is it as simple as a Select Query or will it take some sort of Sql or something?

thank you very much

 

[ToeShot]

Here is a way to do it
I Have actually done this and I posted it for another person in another forum so I will post it here fo you to... (Took A Long Time To Find So I Need To Post It)

These instructions were written for someone who obviouslly had different table. All you need to do is sub in your tables. The key to the thought process here is that You have a Wholesaler this Wholesaler can have many Products and each one of the Products can have many SerialNumbers.
So what happens is that you have a drop down combo box that will show all your Wholesalers. After the Wholesaler is selected the another drop down combox will populate with only that Wholesalers Product. After the product is selected Then you will get a datsheet view of all the serial numbers for that product. Easy right!!!!

Good Luck and have fun. if you have any questions let me know.

1) Create a Manufactor Table with A unique manufactorID and other manufactor info.

2) Create a Product table with an uniquie ProductID and have the manufatcorId on this table also with product description and price etc.

3) Create a third table that will hold all the Barcodes on this table you will have the ProductID number.

4) Create a Relationship with a one to many from manufactor to product then go from product to the barcodes with a one to many relationship.

what this does is basically say I have one manufactor that has many products and each of these products can have many barcodes.

5) Make a form off the barcode table with the wizard and select the datasheet view and call it BarcodeSubform.

6) Make another form off the products table and include a the BarcodeSubform on that form. Using the wizard it will allow you to link the forms by ProductID. Name it ProductSubform

7) Make Another but not off any table. keep it unbound include a subform ProductSubform. Save this form as frmManufactor. add a combobox to this form and have it list just the manufator's name

8) On the ProductSubform add a ComboBox and for the row source select the query builder add the table product to the query the select ProductID, ManufactorID uncheck the show box then select product name. In the Criteria under manufactorID type in
Forms!frmManufactor!ComboBox_Name


Save Everything <-- very important

then run the form frmManufactor what will happen is that you will have a combobox with a list of manufator names after selecting a manufatcor your subform will show with a ComboBox with a list of products for that Manufactor. after selecting a product your datasheet will show you all the barcodes listed for that product. you can also add barcodes to that list in that view.

make seperate forms off the tables that will allow you to add a manufactor and all needed info on that and a form off the products table that will allow you to add the manufactorID and the unique productID

If you have any problems with this let me know and I will try and help you.

http://www.adate.com/accesshelp

 

[BNPMike]

Roddy

I'm just quickly scanning this but aren't you trying to do too many things on the same screen?

You can have Screen A which selects on distributor. You show details just by using the normal form-subform arangement kindly provided by a wizard.

If however the user decides to select on supplier then stick a button on which opens a new screen - same idea as before only now supplier as the main form.

Nothing complicated including the screen. One thing I notice about many screen designs is how much people stuff on them. Users only do one thing at at time so what's the point of squeezing a lifetime's work onto one screen? mike.stephens@bnpparibas.com

 

[roddy17]

Hey there Mike,
i gotta say that i totally agree with you re: way to much stuff on one form. (Gotta mention the fact that there are over 40 fields in this tab control subform. They want everything on one screen.)
The only thing here is that my users happen to be of status, and what they want, is to an extent what they get.
And my thing is, that i'm not yet in a position of experience and status to work to redirect the scope for this project.
So, i'm doing what i can to give them a user (very) friendly system, that shows everything on the one screen.
thanks though...............roddy

 

[ToeShot]

In defense of this of multiple combobox to select certain data. Just so people understand what i did here.

I have one form that has one field. (just one field On the subform) A drop down box, (in my case team divisions). Then the combobox on the subform is active and filled with the data from the selected item in the first combobox I have a combobox and three fields and another subform (total 5 items). When a certain item is selected from the combobox on the subform then the second subform displays a data sheet view to view 3 more fields.

So in after everything is selected I have a total of 9 items(I included the comboboxes) that I am looking at. This is not a lot But I my searching capabilities are great. For example I have team divisions in these divisions I have team names and in these team names i have multiple players.

My relationships run like this One Division to Many Teams, One Team To Many Players.

So when I want to find out who is playing on what team I need to know the division and team name. (I can have the same team name in many divisions and many of the same players on different teams in different divisions)

So on my form the first box Selects my divisions then the second combobox is populated with all the teams from that division. then whe I select a team I get a datasheet view of the players on that team.

I find this very useful and flexible.

I hope this explains this forms function well enough.