Combo Box to Filter Report

[Guest]

Ok I have been tasked with doing this and am very new to Access. So if I dont
understand please bear with me. Here is what i am trying to do. I want to use
a form with a combo box or list to display only that information selected on
a report. I have the combo box and command button created. Command button
will open the report but is not filtering what i select in the combo box.
Please Help. Is prob a simple answer. Here is that i have so far.

Option Compare Database
Option Explicit

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String

stDocName = "rptContacts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

 

[tina]

you need to add a WHERE clause to the OpenReport action, as

DoCmd.OpenReport stDocName, acPreview, , _
"FieldName = " & Me!ComboBoxName

read the OpenReport Action topic in VBA Help, to understand the syntax of
the argument, and how it works.

hth

 

[Guest]

DoCmd.OpenReport stDocName, acPreview, , "LastName =" &Me!cboContacts

Ok My combo box pulls the information off a query and that filed name is
Last Name.
When I put the code in above another window pops up asking for Paramater
Value?

 

[Guest]

Check the spelling of your field name, e.g, might it be [Last Name] or [LName]?

Hope that helps.
Sprinks

 

[Guest]

Thanks that did it. I enclosed the fieldname with [] and worked like a charm.

Now how can have it that when i dont select anything in the combo box to
display all the records for the report. As it stands right now it will just
give me a blank report. Can i add the option for All in the combo box or
something to effect?

Check the spelling of your field name, e.g, might it be [Last Name] or [LName]?

Hope that helps.
Sprinks

DoCmd.OpenReport stDocName, acPreview, , "LastName =" &Me!cboContacts

Ok My combo box pulls the information off a query and that filed name is
Last Name.
When I put the code in above another window pops up asking for Paramater
Value?

 

[tina]

the easiest way is simply to use an If statement in your command button's
code, as

If IsNull(Me!cboContacts) Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , <include your WHERE clause
here>
End If

hth

Thanks that did it. I enclosed the fieldname with [] and worked like a charm.

Now how can have it that when i dont select anything in the combo box to
display all the records for the report. As it stands right now it will just
give me a blank report. Can i add the option for All in the combo box or
something to effect?

Check the spelling of your field name, e.g, might it be [Last Name] or [LName]?

Hope that helps.
Sprinks

DoCmd.OpenReport stDocName, acPreview, , "LastName =" &Me!cboContacts

Ok My combo box pulls the information off a query and that filed name is
Last Name.
When I put the code in above another window pops up asking for Paramater
Value?

:

you need to add a WHERE clause to the OpenReport action, as

DoCmd.OpenReport stDocName, acPreview, , _
"FieldName = " & Me!ComboBoxName

read the OpenReport Action topic in VBA Help, to understand the syntax of
the argument, and how it works.

hth


Ok I have been tasked with doing this and am very new to Access. So if I
dont
understand please bear with me. Here is what i am trying to do. I want to
use
a form with a combo box or list to display only that information selected
on
a report. I have the combo box and command button created. Command button
will open the report but is not filtering what i select in the combo box.
Please Help. Is prob a simple answer. Here is that i have so far.

Option Compare Database
Option Explicit

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String

stDocName = "rptContacts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

 

[Guest]

Thanks again very much!!

the easiest way is simply to use an If statement in your command button's
code, as

If IsNull(Me!cboContacts) Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , <include your WHERE clause
here>
End If

hth

Thanks that did it. I enclosed the fieldname with [] and worked like a charm.

Now how can have it that when i dont select anything in the combo box to
display all the records for the report. As it stands right now it will just
give me a blank report. Can i add the option for All in the combo box or
something to effect?

Check the spelling of your field name, e.g, might it be [Last Name] or [LName]?

Hope that helps.
Sprinks

:

DoCmd.OpenReport stDocName, acPreview, , "LastName =" &Me!cboContacts

Ok My combo box pulls the information off a query and that filed name is
Last Name.
When I put the code in above another window pops up asking for Paramater
Value?

:

you need to add a WHERE clause to the OpenReport action, as

DoCmd.OpenReport stDocName, acPreview, , _
"FieldName = " & Me!ComboBoxName

read the OpenReport Action topic in VBA Help, to understand the syntax of
the argument, and how it works.

hth


Ok I have been tasked with doing this and am very new to Access. So if I
dont
understand please bear with me. Here is what i am trying to do. I want to
use
a form with a combo box or list to display only that information selected
on
a report. I have the combo box and command button created. Command button
will open the report but is not filtering what i select in the combo box.
Please Help. Is prob a simple answer. Here is that i have so far.

Option Compare Database
Option Explicit

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String

stDocName = "rptContacts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

 

[Guest]

One more question. Ok got all the above to work. They want it in a little
different format now. Here is what I need. I have an option box with 2
choice, both tied to there own combo box. I select an option and will enable
one of the combo boxxes. What I need to be able to do is select one of the
options then select the search criteria in the combo box and use the command
button to open the report. I would like to have the same command button open
the report for what ever option button i select. I currently have it set up
to be able to do it for only one of the options. How can i get the button to
know what option i have selected and pull the information from the combo box
linked with it? Hope this makes sense!!

 

[tina]

well, i'd probably use a SelectCase statement to check the option group
control (that's what i usually use with that type of control), and put the
criteria string into a variable. then use the If statement to open the
report. assuming that you're still working with only one report, something
along the lines of:

Dim strWHERE As String

Select Case Me!OptionGroupControlName
Case 1
strWHERE = <put your WHERE clause here, referring to the
appropriate combo box control>
Case 2
strWHERE = <put your WHERE clause here, referring to the *other*
appropriate combo box control>
End Select

If IsNull(Me!cboContacts) And IsNull(Me!OtherComboBoxName) Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , strWHERE
End If

hth

 

[Guest]

Thanks again so much worked great!

well, i'd probably use a SelectCase statement to check the option group
control (that's what i usually use with that type of control), and put the
criteria string into a variable. then use the If statement to open the
report. assuming that you're still working with only one report, something
along the lines of:

Dim strWHERE As String

Select Case Me!OptionGroupControlName
Case 1
strWHERE = <put your WHERE clause here, referring to the
appropriate combo box control>
Case 2
strWHERE = <put your WHERE clause here, referring to the *other*
appropriate combo box control>
End Select

If IsNull(Me!cboContacts) And IsNull(Me!OtherComboBoxName) Then
DoCmd.OpenReport stDocName, acPreview
Else
DoCmd.OpenReport stDocName, acPreview, , strWHERE
End If

hth

 

[Guest]

They need to make up there mind. Hopefully this will be the last question.
There are 5 reports all pulling different information for our users. Is there
anywhere I can use this form to select the different reports and have that
information show up in the combo boxxes i have. I have two combo boxxes. One
for lastname and other for the department. Any help would be great.

 

[tina]

Is there

anywhere I can use this form to select the different reports and have that
information show up in the combo boxxes i have.

well, i don't think i follow the question - how and why do you want the
selected report to "show up in the combo boxes"?

hth

 

[Guest]

Ok I have 5 reports. What they want is one Form that they can access all
those reports. Here is what I need to see if can be done. I want a way "combo
box, list" to be able to select which one of the 5 reports i want to pull
info from. Ok second part is I have a form already that works with one of the
reports i can select to filter the report by last name or by department. What
i want is to be able to select say report 1 and when i chose a last name or
the department will show the information for report 1. I want to be able on
that same form select one of the other 4 reports and when i select a last
name or department will display the information for the selected report. I
know I could just make 5 froms and link them to the reports but they dont
want that. Not like they would be able to see the difference but hey. Im just
the builder.

 

[tina]

okay, that's a pretty common requirement. i usually use a listbox control on
my form to display the reports. the quickest way to set this up is to just
use a Value List for the listbox control's RowSource property. i usually use
two columns: the first column has the name of the report as it shows in the
database window; the second column has the "user friendly" name. so the
RowSource looks something like the following, as

rptReport1; Nice Report Name; rptReport2; Another Nice Report Name

make sure you change the ColumnCount property to 2, and the ColumnWidths
property to

0"; 2"

if 2" inches is too wide, or not wide enough, just change it.

the above described listbox (i'll call it lstReports) goes on the same form
as the combo boxes and command button we've been discussing in this thread.
the code behind the command button will be almost identical - we just need
to provide a reference to the listbox control to get the report name, as

Dim strWHERE As String, strReport As String

Select Case Me!OptionGroupControlName
Case 1
strWHERE = <put your WHERE clause here, referring to the
appropriate combo box control>
Case 2
strWHERE = <put your WHERE clause here, referring to the *other*
appropriate combo box control>
End Select

strReport = Me!lstReports

If IsNull(Me!cboContacts) And IsNull(Me!OtherComboBoxName) Then
DoCmd.OpenReport strReport, acPreview
Else
DoCmd.OpenReport strReport, acPreview, , strWHERE
End If

hth