Using one input on a form to supply the criteria for 2 queries which populate 2 list/combo boxes...

[taholmes160]

Hi Folks:

I am a model railroader, and I am working on developing a small database to generate waybills which direct cars with imaginary loads to different destinations around my model railroad layout (more details available if you have questions)

Let me start off by saying that I realize that right now my data structure is VERY ugly, I KNOW this, right now I'm trying to get queries and forms working to see if the concept is workable before I put all the effort into building proper tables and properly normalized data only to find out that I cant do what I want to. Additionally, this db will likely only feature at the most 50K lines and much more likely 500 or so



I have a company table that has the following data in it:

ID	Company Name	City	State	Commodity	Send/Receive
1	Company A	City A	State A	Plywood	S
2	Company A	City A	State A	2x4s	S
3	Company A	City A	State A	Pine Logs	R
4	Company B	City B	State B	Coal	S
5	Company C	City C	State C	Coal	R
6	Company C	City C	State C	Cinders	S
7	Company D	City D	State D	Plywood	R
8	Company E	City E	State E	Plywood	R

I would like to be able to have a form with an input field at the top that I would enter a commodity into, and that value would be used to supply the criteria for 2 queries. Those queries would be used to populate companies in 2 list / combo boxes one for shippers the other for receivers.

so for example if I entered PLYWOOD in the text box at the top of the form, then the shipper combo box would contain company A, and the receivers combo box would contain Company D and E. Then I would have the opportunity to choose from each list/combo box which shipper and which receiver I wanted to use. and those values would be used to populate a table (called for example "Waybills") and that table would be used to fill a report which would be pulled from all the values (and some additional ones not currently represented) on the company table to create the waybill form.

THe problem is, I'm not sure how to set up the queries to accept the input from the form, nor, how to make the combo / list boxes accept the value from the top of the form so they know what companies to list so that I can choose them.

I have attached my DB file, such as it is for you to look at / manipulate etc, again, I know my data structure is AWFUL, I'll fix that in the second version once I find out if this is workableSLRG_Waybills.zipSLRG_Waybills.zip

Thanks

Tim
San Luis and Rio Grande Model Railroad

[June7]

This is called cascading (or dependent) combobox. A very common topic.

A common naming convention is to use a prefix that indicates object type, such as tblCompanies, cbxCompany, cbxRecievers, cbxCommodity.
Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

The textbox should actually be a combobox with a RowSource:

SELECT DISTINCT Commodity FROM [tblCompanies] ORDER BY Commodity;

Then cbxShippers RowSource:

SELECT ID, [CompanyName] FROM [tblCompanies] WHERE [Send_Receive] = "S" AND Commodity = [cbxCommodity];

and cbxRecievers RowSource

SELECT ID, [CompanyName] FROM [tblCompanies] WHERE [Send_Receive] = "R" AND Commodity = [cbxCommodity];

Then you need VBA code in each combobox GotFocus event to Requery its RowSource:

Private Sub cbxShippers_GotFocus()
Me.cbxShippers.Requery
End Sub

Private Sub cbxReceivers_GotFocus()
Me.cbxReceivers.Requery
End Sub

[taholmes160]

Thank you, that seems to work just fine --

Thanks again
TIM

[ssanfu]

I would suggest designing the dB on paper, whiteboard, cardboard, sticky notes, etc BEFORE getting into Access. Then trying to enter data (on paper for each table) to see if the design works.

Not sure how you would relate a sender company with a receiver company (or two) with the current design. Do you have dates and BOL???


Here is a dB I threw together: