Combo Box on a Switchboard Form

[Guest]

This is driving me crazy! I'm trying to create a switchboard form that will
open either one of 30 reports of product lines by company, or one of 30
companies and their product lines. The command must open a report so that
end-users cannot edit data.

I know how to create a combo box with a drop-down list, and I can create a
command button that will open a report.

Can someone please help me combine the combo box and the command button so I
can automate the report retrieval?

Please, please, please!?

 

[Duane Hookom]

It's not clear if you have 30 very similar reports or a few reports that can
be filtered based on a company or other criteria.

Generically, if you have a combo box of report names "cboReport" and a combo
box of company names "cboCompanyName", you would have code like:

Dim strWhere as String
Dim strReport as String
strWhere = "1=1 "
If Not IsNull(Me.cboReport) Then
strReport = Me.cboReport
If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & " And [CompanyName] = """ & _
Me.cboCompanyName & """"
End If
DoCmd.OpenReport strReport, acPreview, , strWhere
Else
MsgBox "Select a report and try again", vbOkOnly, "PEBKAC"
End If

 

[Guest]

Thanks for the info Duane, but I am a casual Access user and not familiar
with how to apply your suggestion. Hopefully some additional info will
clarify what I'm trying to do.

GOAL:
1. A "switchboard" form with two combo boxes: the first to list the
insurance companies (115 - I lied when I said 30!), the second to combo box
to look up one of 68 insurance lines (commercial auto, commercial property,
etc.).
2. Once the company or the line is selected, I want to use that selection to
open the appropriate report.

There's probably an easier way to do this, but I built queries and reports
for each company and each line. It's important that the "consumers" of this
database only have access to read-only output such as reports.

So far, I've built the combo boxes, and I know how to build a command
button. What I can't figure out is how to input the variable output of the
combo box into a triggered "open report" macro or a command button. There's
GOT to be a way!

Thanks again for ANY help - this is getting frustrating.

Jeff

 

[Duane Hookom]

You should not create separate queries and reports for each company and
line. You should have basic reports that can report out different records
based on filters. That's what I was doing with the code:

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & " And [CompanyName] = """ & _
Me.cboCompanyName & """"
End If
DoCmd.OpenReport strReport, acPreview, , strWhere

The same report can be opened and display results for only the company name
from the combo box.

 

[Guest]

Hi Duane,

I get what you're saying - to create a query that feeds off the combo box,
and then build one report based on that query. I knew there had to be an
easier way. What I don't understand is how to build the query.

The code you provided is SQL, right? Is this entered in the "Critieria"
cell of the QBE? I just don't know how to build the query.

I really appreciate your help. Please feel free to e-mail me
([email protected]) if you don't mind.

Thanks again,

Jeff

You should not create separate queries and reports for each company and
line. You should have basic reports that can report out different records
based on filters. That's what I was doing with the code:

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & " And [CompanyName] = """ & _
Me.cboCompanyName & """"
End If
DoCmd.OpenReport strReport, acPreview, , strWhere

The same report can be opened and display results for only the company name
from the combo box.
--
Duane Hookom
MS Access MVP

Thanks for the info Duane, but I am a casual Access user and not familiar
with how to apply your suggestion. Hopefully some additional info will
clarify what I'm trying to do.

GOAL:
1. A "switchboard" form with two combo boxes: the first to list the
insurance companies (115 - I lied when I said 30!), the second to combo
box
to look up one of 68 insurance lines (commercial auto, commercial
property,
etc.).
2. Once the company or the line is selected, I want to use that selection
to
open the appropriate report.

There's probably an easier way to do this, but I built queries and reports
for each company and each line. It's important that the "consumers" of
this
database only have access to read-only output such as reports.

So far, I've built the combo boxes, and I know how to build a command
button. What I can't figure out is how to input the variable output of the
combo box into a triggered "open report" macro or a command button.
There's
GOT to be a way!

Thanks again for ANY help - this is getting frustrating.

Jeff

 

[Duane Hookom]

I prefer to not email directly.

I rarely place any kind of "dynamic" filtering in a saved query. I just
build my queries so they return all possible records. I then use code in a
module like I have provided that opens the report (or form) adding a where
clause.

About the only time I place criteria expressions in record source queries is
when they aren't dynamic or when the record sources are for subreports.

--
Duane Hookom
MS Access MVP

Hi Duane,

I get what you're saying - to create a query that feeds off the combo box,
and then build one report based on that query. I knew there had to be an
easier way. What I don't understand is how to build the query.

The code you provided is SQL, right? Is this entered in the "Critieria"
cell of the QBE? I just don't know how to build the query.

I really appreciate your help. Please feel free to e-mail me
([email protected]) if you don't mind.

Thanks again,

Jeff

You should not create separate queries and reports for each company and
line. You should have basic reports that can report out different records
based on filters. That's what I was doing with the code:

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & " And [CompanyName] = """ & _
Me.cboCompanyName & """"
End If
DoCmd.OpenReport strReport, acPreview, , strWhere

The same report can be opened and display results for only the company
name
from the combo box.
--
Duane Hookom
MS Access MVP



message

Thanks for the info Duane, but I am a casual Access user and not
familiar
with how to apply your suggestion. Hopefully some additional info will
clarify what I'm trying to do.

GOAL:
1. A "switchboard" form with two combo boxes: the first to list the
insurance companies (115 - I lied when I said 30!), the second to combo
box
to look up one of 68 insurance lines (commercial auto, commercial
property,
etc.).
2. Once the company or the line is selected, I want to use that
selection
to
open the appropriate report.

There's probably an easier way to do this, but I built queries and
reports
for each company and each line. It's important that the "consumers" of
this
database only have access to read-only output such as reports.

So far, I've built the combo boxes, and I know how to build a command
button. What I can't figure out is how to input the variable output of
the
combo box into a triggered "open report" macro or a command button.
There's
GOT to be a way!

Thanks again for ANY help - this is getting frustrating.

Jeff