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]

I am thoroughly confused and willing to accept a copy of your mdb file. You would need to email me first to find out which of my email addresses would accept an attachment and the exact format to send to me.

If we get this figured out, we can reply back to the forum.

duane(At)hookom(dot)net

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: before I email you the mdb file, perhaps this one snag will solve the whole thing:
I open the report, which is based on my parameter queries. (The parameters point to the form.) The report's Open event has a DoCmd to open the form frmSegment, which the user will use to feed parameter values. frmSegment's OK_Click() event has this code:
frmSegment.Visible = false.

This code (frmSegment.Visible = false) generates the following error, which I cannot resolve: "The expression you entered as the On Click event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX control. *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluating the function, event, or macro.

I also cannot find reference to this error on microsoft.com. If you can help with this, I might be off and running!

 

[dhookom]

Try change the code to:
Me.Visible = False

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]

I tried that first and got the same message. I changed it to be specific thinking it would help, but it didn't.

 

[dhookom]

Maybe you should show us all your code...

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]

...send the mdb file to your email address?

 

[dhookom]

Email me first so I can give you instructions off-line.

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]

 

[dhookom]

frmSegment seems to have been corrupt. When the form was renamed and a new one created, the report worked as expected.

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 to dhookom for taking the time to discover that my form was corrupt. Creating a new one caused the report to work beautifully.

Thanks also to PHV for the WHERE syntax 15 Nov 05 16:59, which worked flawlessly once dhookom discovered my form problem and I was able to continue.

 

[TheSouthCole]

Ok, now how do I modify this SQL to allow the user to choose ALL Segments? I can't make the FAQ's on this topic work because my choices are not coming from a table, so I can't use a UNION.
Here's my working code:
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 OR ([NEW YTD Bookings for %MarketShareReport].SegDescr) In ('Intermodal','Boxcar') AND Forms!frmSegment!cboSegment='Rail'));

 

[PHV]

WHERE [NEW YTD Bookings for %MarketShareReport].AnnualCTDEstimate<>0
AND ( [NEW YTD Bookings for %MarketShareReport].SegDescr=Forms!frmSegment!cboSegment
OR ([NEW YTD Bookings for %MarketShareReport].SegDescr In ('Intermodal','Boxcar') AND Forms!frmSegment!cboSegment='Rail')
OR Forms!frmSegment!cboSegment='All'
)

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

 

[TheSouthCole]

that worked wonderfully! (much simpler than my attempts)
Thanks so very much!