selections from 2 combo boxes

[Guest]

I have 2 combo boxes on my form. I want to be able to run a specfic report
based on the selections from both combo boxes.

The first combo box is Combo99 and contains these values:
1 Month
3 Months
6 Months
9 Months
12 Months

The second combo box is Combo105 and contains these values:
Agent
Agent and Branch
Provider
Provider and Branch
(these are only a few - there are actually 13 options).
(I created the combo box from a table tblReports1 and the column name is
Sort by)

I would like to have one command button that would open the correct report
based on the selection. For example:
If 1 Month is selected from Combo99 and Agent is selected from Combo105, the
report Agent Totals - 1 Month opens.
If 3 Months is selected from Combo99 and Provider is selected from Combo105,
the report Provider Totals - 3 Months opens.

I think that I am on the right track but I don't know how to tie it together
on the command button.

Suggestions/Solutions are greatly appreciated!

Thanks,
Zoe

 

[Guest]

Zoe,

Provided that your naming convention for your reports is consistant with the
pattern that you have described, you could use two Case statement to build
the Name of the Report based on the selections made from the two combo boxes.
Just place the code below in the "After Update" event of both combo boxes.

Dim strRptName as String
if not isnull(me.NameOfSecondComboBox) and _
not isnull(me.NameOfFirstCombobox) then
strRptName = Me.NameOfSecondComboBox + " - "
strRptName = strRptName + me.NameOfFirstComboBox
end if

Then you can use the variable "strRptName" as the name of the report to open.

 

[Guest]

Thanks so much! I changed it up a bit and put the code in my command button
(instead of the combo boxes) to preview the report. It's working great!
Have a great day,
Zoe

 

[Guest]

Your welcome. Glad to help.
--
HTH

Mr B

Thanks so much! I changed it up a bit and put the code in my command button
(instead of the combo boxes) to preview the report. It's working great!
Have a great day,
Zoe