Opening a Filtered Report from a Form Button

[dcornett63]

Hello,
After a user inputs the data on a userform, they must print out a report
with that data for manager approval. To make it easier for the user, I have
placed a button on the form that will launch the report. However, the form
needs to be filtered by two other fields: [tbl CU Code]![CU #] and [tbl Bid
Award]![Req #]. Can you please show me how to include the filter criteria in
my code?

My button code is below

_____________________________________________________________
Private Sub Enc_Btn_Click()
On Error GoTo Err_Enc_Btn_Click

Dim stDocName As String

stDocName = "Encumbrance Notice"
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acPreview

Exit_Enc_Btn_Click:
Exit Sub

Err_Enc_Btn_Click:
MsgBox Err.Description
Resume Exit_Enc_Btn_Click

End Sub
______________________________________________________

 

[Duane Hookom]

You haven't give us much to go on such as:
Are both of the fields in the reports record source?
Are both values in the current form?
There is a possible solution at
http://www.mvps.org/access/reports/rpt0002.htm.

 

[dcornett63]

I'm sorry, I just took that as a given but can see how it may not be. The
answer is yes to both.

Using the info from the link that you suggested, I have added to my code,
but obviously I'm still doing something wrong. (see below) It still isn't
filtering the report for just that record.

________________________________________________________

Private Sub Enc_Btn_Click()
On Error GoTo Err_Enc_Btn_Click

Dim stDocName As String
Dim strWhere As String

stDocName = "Encumbrance Notice"
If Me.Dirty Then Me.Dirty = False
strWhere = "[Enter CU #]=" & Me![CU #]
strWhere = "[Enter Req #]=" & Me![Req #]
DoCmd.OpenReport stDocName, acPreview, strWhere

Exit_Enc_Btn_Click:
Exit Sub

Err_Enc_Btn_Click:
MsgBox Err.Description
Resume Exit_Enc_Btn_Click

End Sub

_________________________________________________________

You haven't give us much to go on such as:
Are both of the fields in the reports record source?
Are both values in the current form?
There is a possible solution at
http://www.mvps.org/access/reports/rpt0002.htm.

--
Duane Hookom
Microsoft Access MVP

Hello,
After a user inputs the data on a userform, they must print out a report
with that data for manager approval. To make it easier for the user, I have
placed a button on the form that will launch the report. However, the form
needs to be filtered by two other fields: [tbl CU Code]![CU #] and [tbl Bid
Award]![Req #]. Can you please show me how to include the filter criteria in
my code?

My button code is below

_____________________________________________________________
Private Sub Enc_Btn_Click()
On Error GoTo Err_Enc_Btn_Click

Dim stDocName As String

stDocName = "Encumbrance Notice"
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acPreview

Exit_Enc_Btn_Click:
Exit Sub

Err_Enc_Btn_Click:
MsgBox Err.Description
Resume Exit_Enc_Btn_Click

End Sub
______________________________________________________

 

[Duane Hookom]

I would remove the criteria from the report's record source and use the Where
Condition. Assuming both fields are numeric and the field names in the report
are the same as the control names in the form try something like:

Private Sub Enc_Btn_Click()

On Error GoTo Err_Enc_Btn_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "1=1 "
stDocName = "Encumbrance Notice"
If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me![CU #]) Then
strWhere = strWhere & " AND [CU #]=" & Me![CU #]
End If
If Not IsNull(Me![Req #]) Then
strWhere = strWhere & " AND [Req #]=" & Me![Req #]
End If
'note strWhere follows the 3rd comma, not the 2nd
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Enc_Btn_Click:
Exit Sub

Err_Enc_Btn_Click:
MsgBox Err.Description
Resume Exit_Enc_Btn_Click

End Sub

--
Duane Hookom
Microsoft Access MVP

I'm sorry, I just took that as a given but can see how it may not be. The
answer is yes to both.

Using the info from the link that you suggested, I have added to my code,
but obviously I'm still doing something wrong. (see below) It still isn't
filtering the report for just that record.

________________________________________________________

Private Sub Enc_Btn_Click()
On Error GoTo Err_Enc_Btn_Click

Dim stDocName As String
Dim strWhere As String

stDocName = "Encumbrance Notice"
If Me.Dirty Then Me.Dirty = False
strWhere = "[Enter CU #]=" & Me![CU #]
strWhere = "[Enter Req #]=" & Me![Req #]
DoCmd.OpenReport stDocName, acPreview, strWhere

Exit_Enc_Btn_Click:
Exit Sub

Err_Enc_Btn_Click:
MsgBox Err.Description
Resume Exit_Enc_Btn_Click

End Sub

_________________________________________________________

You haven't give us much to go on such as:
Are both of the fields in the reports record source?
Are both values in the current form?
There is a possible solution at
http://www.mvps.org/access/reports/rpt0002.htm.

--
Duane Hookom
Microsoft Access MVP

Hello,
After a user inputs the data on a userform, they must print out a report
with that data for manager approval. To make it easier for the user, I have
placed a button on the form that will launch the report. However, the form
needs to be filtered by two other fields: [tbl CU Code]![CU #] and [tbl Bid
Award]![Req #]. Can you please show me how to include the filter criteria in
my code?

My button code is below

_____________________________________________________________
Private Sub Enc_Btn_Click()
On Error GoTo Err_Enc_Btn_Click

Dim stDocName As String

stDocName = "Encumbrance Notice"
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acPreview

Exit_Enc_Btn_Click:
Exit Sub

Err_Enc_Btn_Click:
MsgBox Err.Description
Resume Exit_Enc_Btn_Click

End Sub
______________________________________________________