combo box to populate parameter in query 1

[TheSouthCole]

I have a very complex query (based on several other queries and tables) and want to provide a combo box for user to select queried data.

Question #1: is there any way to do that without VBA code, and if so, how?

Question #2: user wants one of the choices (from this combo box) to be "Rail" but data field stores "Intermodal" or "Boxcar." In other words, they want to choose "Rail" from the combo box and thus pull records where data = intermodal or boxcar, and have it show under the group called Rail. Ideas?

 

[dhookom]

1) You don't need any code to set the criteria of a query to a control on a form. Use an expression like:
Forms!frmYourForm!cboYourCombo

2) You don't have to display the value (bound column) of the combo box.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheSouthCole]

for 2) above:
if they choose "Rail" from my combo box, how do I make that pick the records with "intermodal" and "boxcar" data?

 

[PHV]

WHERE (yourField = Forms!frmYourForm!cboYourCombo OR (yourField In ('Intermodal','Boxcar') And Forms!frmYourForm!cboYourCombo='Rail'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheSouthCole]

I'll try it tomorrow and report back...THANKS ALL!

 

[dhookom]

TheSouthCole,
Do you have a table with field values that tie Rail to Intermodal and Boxcar? If not, you should create one before you come back with more questions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheSouthCole]

dhookom:
I'm sorry, but I don't understand what you mean. I have a table with a field called Segment, and Intermodal and Boxcar are two of the many choices therein. Just now, I am asked to allow the users to choose Rail and have Rail = intermodal or boxcar.

Does this mean I should create some sort of table? I'm sorry if I'm showing ignorance.

 

[dhookom]

Add a field like SegmentGroup and add values in the Intermodal and Boxcar records of "Rail". You can then select Rail from a combo box on a form and use it to return Intermodal and Boxcar records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheSouthCole]

Thanks, dhookom!
A few more questions, if you don't mind...
Rather than create another table, I had thought about adding a "Rail" field to the existing table, but I was unable to figure out how to get the TABLE's field to populate the data automatically if the Segment field contained either "boxcar" or "intermodal." Do you know of a way to do this?

Also, I want to verify that I'm thinking correctly...following your instructions above, I'd have to offer the user a second combobox, correct? Because "Rail" would be coming from a different field than all the other Segment choices. If so, it's certainly a nice and easy way to solve the problem, even though a little clumsy-looking to the user. (I'm into easy solutions!)

 

[dhookom]

I assumed your question 2# was similar to the northwinds products and categories. Your "rail" was a category name and "Intermodal" & "Boxcar" were product names. You could create a combo box based on product categories that would select all the products in that category.

The Northwind products table has a field that identifies the product category. This is the same solution that I was suggesting to you based on your original question.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheSouthCole]

ok, I'll check out all this info tonight and get back to you, dhookom and PVH, with my level of success! Thanks to you both.....

 

[TheSouthCole]

ok, I don't know what I'm doing wrong, but I cannot get the query's parameters to recognize the values in the open dialog box.

Yes, I've looked at Help and yes, I've looked at Northwinds. Yes, all my form & field names match. I'm about ready to get a job pumping gas. Help!

 

[dhookom]

Regarding your combo box:
Form Name:
Combo box Name:
Row Source:
Bound Column:
Column Count:

Regarding your query:
SQL View:

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheSouthCole]

combo box:
Form Name: frmSegment
Combo box Name: txtYear, cboSegment
Row Source: none for txtYear. cboSegment is "Leased";
"OTR";"Grocery";"Dairy";"Distribution";"Stock";
"Owner/Operator";"Rail"
Bound Column: txtYear: none. cboSegment: 1
Column Count: cboSegment: 1

Regarding your query:
SELECT [NEW YTD Bookings for %MarketShareReport].[Cust#], [NEW YTD Bookings for %MarketShareReport].CustName, [NEW YTD Bookings for %MarketShareReport].AnnualCTDEstimate, [NEW AnnualTKEstimate for %MarketShareReport].AnnualTKEstimate, [NEW YTD Bookings for %MarketShareReport].SegDescr
FROM [NEW YTD Bookings for %MarketShareReport] LEFT JOIN [NEW AnnualTKEstimate for %MarketShareReport] ON [NEW YTD Bookings for %MarketShareReport].[Cust#] = [NEW AnnualTKEstimate for %MarketShareReport].[Cust#]
WHERE ((([NEW YTD Bookings for %MarketShareReport].AnnualCTDEstimate)<>0) AND (([NEW YTD Bookings for %MarketShareReport].SegDescr)=[Forms]![frmSegment]![cboSegment]));

Everything mentioned in query's SQL are other queries....

[NEW AnnualTKEstimate for %MarketShareReport] pulls data from a query named "NEW TK YTD Total Bookings" and it is the Bookings query that has a Year parameter to be fed by txtYear on the dialog box.

Also, [NEW YTD Bookings for %MarketShareReport] pull data froma query named "New Shipped - All" and this query also has a Year parameter to be fed by the txtYear on the dialog box.

I had the txtYear working fine, but when messing with the cboSegment field, it all messed up. Now, when I run the report, the dialog box comes up, takes the parameters, but then I get two more dialogs as if the parameters aren't set up right, that say "forms!frmSegment!txtYear" and "forms!frmSegment!cboSegment" AFTER I've already entered those fields and the dialog box is still open.

 

[dhookom]

Do you close your form anywhere?

Why is your combo box not a type that uses a table/query? You should have a table similar to the Categories in Northwind.

What values do you see when you create a query like:
SELECT DISTINCT SegDescr FROM [NEW YTD Bookings for %MarketShareReport];

BTW: Consider finding a naming convention that doesn't allow spaces and/or symbols. Having "%" in a name may cause issues later on.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheSouthCole]

the table that would feed the combo box does not include "rail." Rail is a grouping of two of the field choices: boxcar and intermodal. There are no other groupings. Therefore, a SELECT DISTINCT query like above would NOT return "rail."

You'll be glad to know that I teach new Access users NOT to use spaces or symbols when they name objects. Half the time, I'm experimenting, so I make a very descriptive name and then I end up building it into other objects, so I'm stuck with the name. Also, they've got so many requests that are just one tiny bit different than other requests, that the names have evolved into a mess.

BTW, is there a way to award more points when this is resolved?

 

[PHV]

TheSouthCole, have you tried my suggestion of 15 Nov 05 16:59 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheSouthCole]

PVH:
Unfortunately, I can't even get that far, because I have another more simple parameter to filter year, and even that doesn't recognize that the dialog box is open.

I'm quite sure that your WHERE coding will suit me beautifully, however I've got something more basic that's wrong (open dialog box is not recognized by parameters in queries) that I must fix first, and I think I'm over my head with what could be wrong here first.

 

[PHV]

frmSegment should be an open (and populated) mainform when the report is launched.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheSouthCole]

yes, it is. Can't figure for the life of me why the queries don't recognize it. Any suggestions?