variable auto fill options (1 Viewer)

[Stephanie T.]

Many of our customers (distributors) have multiple “ship to” addresses. I would like to set up our “Orders” form so that when the data entry person picks one of our customers from the “distributors/bill to” combo box, the combo box under “warehouse/ship to” will only allow her to choose one of that distributor’s warehouses.

If it is easiest to make the “distributors/bill to” combo box two columns, one with the Distributor and one with the Warehouses that will then auto fill the “warehouse/ship to” box, that is fine (I can do that). That would mean that when she goes to the combo box for “distributors/bill to” she will see each distributor as many times as they have warehouses, but she can choose based on the warehouse. But is that really my only option?

Ideally, she should be able to choose the distributor and then only have choices in the “warehouse/ship to” field based on the warehouses that distributor has.

I have a Distributor table that has all of the distributor information, and a Warehouse table that has their warehouse information. The Warehouse table’s form is a subform on the distributor table, so I can take all of the pertinent information for the “orders” form from the warehouse table since the warehouses are linked to the distributors. If that helps.

Any help is greatly appreciated, sample code is also appreciated. I’m using Access 97 if that matters, and still a code novice.

Thank you in advance,
Stephanie

 

[Russ]

Don't know if anyone has answered you qeustion but came across it whilst trying to find something similar for myself.

After a bit of work I came up with a possible solution. I have attached the file for you to have a look at but as for coding I am in the same situation as you (a Newbie) so its all pretty basic.

Hope it helps

 

[Stephanie T.]

Russ,

Thank you very much for your help. I have to run at the moment so I will look at it later. I have found out that what I was looking for was called "Cascading Combo Boxes" I put another question out there on the forum once I found this out. Some very helpful people have also posted databasse that show a very simply way to set these up. If you do a search for Cascading Combo Boxes and find the thread that I started, you should easily be able to download that database too. You may not need it now, or it may be a good reference for the future.

I appreciate your reply greatly and look forward to pursuing your file tomorrow when I don't have to leave so early.

Best to you,
Stephanie