Generate a report based on value from Combo box

[KrisDdb]

Hi All

I am a newbie with MS Access. So pls go easy on me. Now heres my question..




I am trying to design a form which would have a combo box cbo1(Market) and a command button (btn1) to finally view a report based on the values selected in the combo box. The query (say qry1) that the report uses has about 11 columns, in which Market is one of them. And I have 8 different Markets. Now i need to be able to generate the report for two options:


1. I should be able to show the columns for the selected market ONLY(it need not be multiple selections; just one will do). Example: If I selected the Market 'Chicago', I want the report to display the column values for only Chicago.

And the other option is.


2. I should be able to show the columns for 'ALL' markets together. I dont actually have an option by name 'ALL' in the 8 values for market. I would like the combo box to show the option 'ALL' along with the 8 values in the list. Is that possible? If yes, how?


I dont know if i have given sufficient information, but if more information is needed, shoot it up. Any help is greatly appreciated

[pbaldy]

You can use this technique:

BaldyWeb wherecondition

I would probably just let the lack of a selection in the combo represent the "all" choice, but you can also do this:

http://access.mvps.org/access/forms/frm0043.htm

and test for it.

[alansidman]

The first video will show you how to set up the query and search. Change the text box to a combo box and do pretty much the same thing, however, look at the second video to see how the combo box works with a filter and adapt it for a query.

First video: http://www.datapigtechnologies.com/f...earchform.html

Second video: http://www.datapigtechnologies.com/f...tomfilter.html

Alan

[KrisDdb]

Paul & Alan

First of all, Thanks for the quick reply

As for the answer, your suggestions suited my need pefectly Thankyou so much for that.

Now I am trying to add more functionality to my form.. I have got to introduce a second combo box. I have a question concerning to this. Should I post it as a seperate question? or can i continue here?

[pbaldy]

I would say if it's related to this thread you can continue here, otherwise it should probably be a new thread. That keeps the archive cleaner.

[KrisDdb]

Its furthur into my earlier question.. so i wil post it here.

Like I said earlier, I have my combo box displaying the list of Markets. Now for every market, i have some casetypes. (There are about 50 of them in the whole db, but i am considering only 4 for now with this report.. AUT, PI, SS, WC).

Now what I need to do is, I want to put in a second combo box that would give me the 'case types' in it, BUT ONLY THOSE related to the market selected above. Say, 'Chicago' market has only AUT and PI, the second combo should display only AUT and PI. And when I select one of the options, the report should be displayed with columns for only that Casetype for that Market. I know I will have to change the button click event code to refer to the second combo.. but I am getting the report for all casetypes under the market (I mean, it returns all the above case types for the market! I am not able to return only one casetype).

Suggestions??

[pbaldy]

Your question about the combos sounds like this kind of thing:

Baldy-Cascading Combos

Not sure what method you used from our suggestions. If mine, the code for two numeric values would look like:

DoCmd.OpenReport "ReportName", , , "FieldName = " & Me.ControlName & " AND SecondFieldName = " & Me.SecondComboName

[KrisDdb]

@ Paul: I used your method for implementation. BTW, its not numeric, its text. So I used the second line of code from your first link (frm your first post in ths thread). It works fine when I use this

DoCmd.OpenReport "repNewIntakeMarket", acViewPreview, , "Market = '" & Combo0 & "'"

But when I try to use the AND to combine both combo boxes, I am getting a type mismatch error. This is the code I used for the combination..

DoCmd.OpenReport "repNewIntakeMarket", acViewPreview, , "Market = '" & Combo0 & "'" And "Type = '" & Combo1 & "'"

I also set up the AfterUpdate event for the first combo box to this :

Private Sub Combo0_AfterUpdate()
Dim strSource As String

strSource = "SELECT DISTINCT Type " & _
"FROM qryUnionIntakes " & _
"WHERE Market = '" & Me.Combo0 & "' ORDER BY Type"
Me.Combo1.RowSource = strSource
Me.Combo1 = vbNullString
End Sub

Because of this, the second combo lists all the values under the market selected in the first combo. What did I mess up!?

[pbaldy]

You went a little overboard with the quotes. Try:

DoCmd.OpenReport "repNewIntakeMarket", acViewPreview, , "Market = '"  & Combo0 & "' And Type = '" & Combo1 & "'"

As to the combos, not sure exactly what you're saying the problem is. The code looks okay, though I think Type is a reserved word, so try bracketing it: [Type]

If that doesn't help, add this line after the string is built:

Debug.Print strSource

and run the code. That line will print the finished SQL out to the VBA Immediate window, where you can examine it or copy/paste it to a new query and test.

[KrisDdb]

Paul:
I am now tweaking the code and trying out different things! I haven't got to my result yet.. but I think I will get there. You have given me some food for thought
Thanks for your replies. Will postback after I solve it.