Create Two Filters in Form to Execute Report.

  • Thread starter Thread starter mlieberstein
  • Start date Start date
M

mlieberstein

I have a report based on a query. I have a form that needs two pieces of
data to run:

· A text box [txtYMV]: User enters a yearmonthvalue (yyyymm)
· A combo box [cboBusinessGroup]: User selects a Business Group from a table.

Once these boxes are selected, there's a command button [cmd_OpenReport]
that opens the report based on the two criteria, and here's my problem.
(Please be gentle...I'm a newbie coder) :) Below is the code I'm trying to
use:

Private Sub cmd_OpenReport_Click()

Dim strWhere As String

If Not IsNull(Me.txtYMV) And IsNull(Me.cbxBusinessGroup) Then
strWhere = " AND [txtYMV] =""" & Me.txtYMV & """ " _
And strWhere & " AND [Business Group I] = '" & Me.cbxBusinessGroup & "'"
End If

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, strWhere
End Sub

Any Help is much appreciated
 
"here's my problem" ... I didn't see it.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Good Point. My report is generated, but all of the records in the combox are
generated in the report. I want the record chosen in the combobox to be the
filter.

So basically I want the user to enter the textbox [txtYMV]; Select a record
using the combobox [cboBusinessGroup]. And my report is filtered on both of
these selections.
 
Take a look at Access HELP for the syntax on the OpenReport command.

What happens if you select only on the basis of the combobox?

And since we can't see your underlying data, it is possible that the
BusinessGroup chosen could refer to a number of records?

Regards

Jeff Boyce
Microsoft Office/Access MVP

mlieberstein said:
Good Point. My report is generated, but all of the records in the combox
are
generated in the report. I want the record chosen in the combobox to be
the
filter.

So basically I want the user to enter the textbox [txtYMV]; Select a
record
using the combobox [cboBusinessGroup]. And my report is filtered on both
of
these selections.


mlieberstein said:
I have a report based on a query. I have a form that needs two pieces of
data to run:

· A text box [txtYMV]: User enters a yearmonthvalue (yyyymm)
· A combo box [cboBusinessGroup]: User selects a Business Group from a
table.

Once these boxes are selected, there's a command button [cmd_OpenReport]
that opens the report based on the two criteria, and here's my problem.
(Please be gentle...I'm a newbie coder) :) Below is the code I'm trying
to
use:

Private Sub cmd_OpenReport_Click()

Dim strWhere As String

If Not IsNull(Me.txtYMV) And IsNull(Me.cbxBusinessGroup) Then
strWhere = " AND [txtYMV] =""" & Me.txtYMV & """ " _
And strWhere & " AND [Business Group I] = '" & Me.cbxBusinessGroup &
"'"
End If

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, strWhere
End Sub

Any Help is much appreciated
Click to expand...
Click to expand...
 
I tried this piece of code for the combobox, and the report worked fine.

'Private Sub cmd_OpenReport_Click()

'DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, "[Business Group
I] = '" & Me.cbxBusinessGroup & "'"

The combobox above works fine with the report.
But I'm trying to have the user input a yearmonthvalue in the text box. I
was hoping to use both filters for the report.

mlieberstein said:
Good Point. My report is generated, but all of the records in the combox are
generated in the report. I want the record chosen in the combobox to be the
filter.

So basically I want the user to enter the textbox [txtYMV]; Select a record
using the combobox [cboBusinessGroup]. And my report is filtered on both of
these selections.


mlieberstein said:
I have a report based on a query. I have a form that needs two pieces of
data to run:

· A text box [txtYMV]: User enters a yearmonthvalue (yyyymm)
· A combo box [cboBusinessGroup]: User selects a Business Group from a table.

Once these boxes are selected, there's a command button [cmd_OpenReport]
that opens the report based on the two criteria, and here's my problem.
(Please be gentle...I'm a newbie coder) :) Below is the code I'm trying to
use:

Private Sub cmd_OpenReport_Click()

Dim strWhere As String

If Not IsNull(Me.txtYMV) And IsNull(Me.cbxBusinessGroup) Then
strWhere = " AND [txtYMV] =""" & Me.txtYMV & """ " _
And strWhere & " AND [Business Group I] = '" & Me.cbxBusinessGroup & "'"
End If

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, strWhere
End Sub

Any Help is much appreciated
Click to expand...
Click to expand...
 
Check Access HELP for syntax on the OpenReport command. It has examples...

Regards

Jeff Boyce
Microsoft Office/Access MVP


mlieberstein said:
I tried this piece of code for the combobox, and the report worked fine.

'Private Sub cmd_OpenReport_Click()

'DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, "[Business
Group
I] = '" & Me.cbxBusinessGroup & "'"

The combobox above works fine with the report.
But I'm trying to have the user input a yearmonthvalue in the text box. I
was hoping to use both filters for the report.

mlieberstein said:
Good Point. My report is generated, but all of the records in the combox
are
generated in the report. I want the record chosen in the combobox to be
the
filter.

So basically I want the user to enter the textbox [txtYMV]; Select a
record
using the combobox [cboBusinessGroup]. And my report is filtered on both
of
these selections.


mlieberstein said:
I have a report based on a query. I have a form that needs two pieces
of
data to run:

· A text box [txtYMV]: User enters a yearmonthvalue (yyyymm)
· A combo box [cboBusinessGroup]: User selects a Business Group from a
table.

Once these boxes are selected, there's a command button
[cmd_OpenReport]
that opens the report based on the two criteria, and here's my problem.
(Please be gentle...I'm a newbie coder) :) Below is the code I'm
trying to
use:

Private Sub cmd_OpenReport_Click()

Dim strWhere As String

If Not IsNull(Me.txtYMV) And IsNull(Me.cbxBusinessGroup) Then
strWhere = " AND [txtYMV] =""" & Me.txtYMV & """ " _
And strWhere & " AND [Business Group I] = '" & Me.cbxBusinessGroup &
"'"
End If

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, strWhere
End Sub

Any Help is much appreciated
Click to expand...
Click to expand...
Click to expand...
 
Thank you for the tip. I'll do some more research around the OpenReport
command, and combine the two filters.


--I was hoping one of the Access Gurus might provide some code on the
OpenReport/Apply Filter Event Procedure to help me with filtering using both
the text and combo box, but I'll do the research. :)

Thank you for your time.

Jeff Boyce said:
Check Access HELP for syntax on the OpenReport command. It has examples...

Regards

Jeff Boyce
Microsoft Office/Access MVP


mlieberstein said:
I tried this piece of code for the combobox, and the report worked fine.

'Private Sub cmd_OpenReport_Click()

'DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, "[Business
Group
I] = '" & Me.cbxBusinessGroup & "'"

The combobox above works fine with the report.
But I'm trying to have the user input a yearmonthvalue in the text box. I
was hoping to use both filters for the report.

mlieberstein said:
Good Point. My report is generated, but all of the records in the combox
are
generated in the report. I want the record chosen in the combobox to be
the
filter.

So basically I want the user to enter the textbox [txtYMV]; Select a
record
using the combobox [cboBusinessGroup]. And my report is filtered on both
of
these selections.


:

I have a report based on a query. I have a form that needs two pieces
of
data to run:

· A text box [txtYMV]: User enters a yearmonthvalue (yyyymm)
· A combo box [cboBusinessGroup]: User selects a Business Group from a
table.

Once these boxes are selected, there's a command button
[cmd_OpenReport]
that opens the report based on the two criteria, and here's my problem.
(Please be gentle...I'm a newbie coder) :) Below is the code I'm
trying to
use:

Private Sub cmd_OpenReport_Click()

Dim strWhere As String

If Not IsNull(Me.txtYMV) And IsNull(Me.cbxBusinessGroup) Then
strWhere = " AND [txtYMV] =""" & Me.txtYMV & """ " _
And strWhere & " AND [Business Group I] = '" & Me.cbxBusinessGroup &
"'"
End If

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, strWhere
End Sub

Any Help is much appreciated
Click to expand...
Click to expand...
Click to expand...
 
Back
Top