Build Where Clause in OnOpen of Report

  • Thread starter Thread starter Nick X
  • Start date Start date
N

Nick X

Hi all,
I have an Access db that is used by another piece of software (which I have
no access to that code) as a delivery system for reports. I am able to
manipulate the Access portion and have been able to open the reports based on
a selection in an Option Group . Now I am trying to use Allen Browne's "Open
the report filtered to the items selected in the list box.", but i can't
quite figure out how to twist it into this situation (I have gotten it to
work before). Remember, the reports are launched from a third party software
that I do not control. So i I need to get this to work in the OpenReport
procedure. thanks!

Private Sub Report_Open(Cancel As Integer)

DoCmd.OpenForm "frmSelectFunc", , , , , acDialog
If Not IsLoaded("frmSelectFunc") Then
Cancel = True
End If

DoCmd.Maximize

End Sub

Private Sub cmdPrevFunc_Click()

'On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Report by Priority Ranking"

'Loop through the ItemsSelected in the list box.
With Me.lstFuncClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(2, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
'DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Me.Visible = False

'Exit_Handler:
' Exit Sub

'Err_Handler:
' If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
' MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
' End If
' Resume Exit_Handler
End Sub
 
Now I am trying to use Allen Browne's "Open
the report filtered to the items selected in the list box.", but i can't
quite figure out how to twist it into this situation
Click to expand...

What exactly are you trying to twist the situation into? Are you pulling
criteria from a list box or just various data from the form? If its just
data from various controls, you can pass an SQL string via OpenArgs and set
the report's recordsource in the Open event (I think openargs are only
available in reports in 2002 and up).

Private Sub btnOpenReport_Click()
Dim strSQL As String
strSQL = "SELECT * FROM <tablename> WHERE " _
& "([field] = """ & Me.SomeTextControl & """) AND " _
& "([field] = " & Me.SomeNumericControl & ")"
DoCmd.OpenReport "reportname", , , , , strSQL
End Sub

and in your reports open statement...

Private Sub Reportname_Open(Cancel As Integer)
If Len(Me.OpenArgs) <> 0 Then
Me.Recordsource = Me.OpenArgs
End If
End Sub


This type of scenario can also be accomplished using the WhereCondition
argument in DoCmd.OpenReport:

Private Sub btnOpenReport_Click
Dim strWhere As String
strWhere = "([field] = """ & Me.SomeTextControl & """) And " _
& "([field] = " & Me.SomeNumericControl & ")"

DoCmd.OpenReport "reportname", , , strWhere
End Sub


I'm not exactly sure what it is you need to accomplish, but maybe this will
shed a little bit of light.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Nick X said:
Hi all,
I have an Access db that is used by another piece of software (which I have
no access to that code) as a delivery system for reports. I am able to
manipulate the Access portion and have been able to open the reports based on
a selection in an Option Group . Now I am trying to use Allen Browne's "Open
the report filtered to the items selected in the list box.", but i can't
quite figure out how to twist it into this situation (I have gotten it to
work before). Remember, the reports are launched from a third party software
that I do not control. So i I need to get this to work in the OpenReport
procedure. thanks!

Private Sub Report_Open(Cancel As Integer)

DoCmd.OpenForm "frmSelectFunc", , , , , acDialog
If Not IsLoaded("frmSelectFunc") Then
Cancel = True
End If

DoCmd.Maximize

End Sub

Private Sub cmdPrevFunc_Click()

'On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Report by Priority Ranking"

'Loop through the ItemsSelected in the list box.
With Me.lstFuncClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(2, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
'DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Me.Visible = False

'Exit_Handler:
' Exit Sub

'Err_Handler:
' If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
' MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
' End If
' Resume Exit_Handler
End Sub
Click to expand...
 
Thanks for your response. Sorry, as so often happens, I got side tracked
from doing the fun stuff and had to do some actual work.

What is happening is that the report is already in the process of opening
(called from third party software, no access to code). When I get a minute I
will give this a try. It does make sense, I just need to clone myself so i
can actuallywork on all my projects at once.

Thanks again,
Nick

Jack Leach said:
Now I am trying to use Allen Browne's "Open
the report filtered to the items selected in the list box.", but i can't
quite figure out how to twist it into this situation
Click to expand...

What exactly are you trying to twist the situation into? Are you pulling
criteria from a list box or just various data from the form? If its just
data from various controls, you can pass an SQL string via OpenArgs and set
the report's recordsource in the Open event (I think openargs are only
available in reports in 2002 and up).

Private Sub btnOpenReport_Click()
Dim strSQL As String
strSQL = "SELECT * FROM <tablename> WHERE " _
& "([field] = """ & Me.SomeTextControl & """) AND " _
& "([field] = " & Me.SomeNumericControl & ")"
DoCmd.OpenReport "reportname", , , , , strSQL
End Sub

and in your reports open statement...

Private Sub Reportname_Open(Cancel As Integer)
If Len(Me.OpenArgs) <> 0 Then
Me.Recordsource = Me.OpenArgs
End If
End Sub


This type of scenario can also be accomplished using the WhereCondition
argument in DoCmd.OpenReport:

Private Sub btnOpenReport_Click
Dim strWhere As String
strWhere = "([field] = """ & Me.SomeTextControl & """) And " _
& "([field] = " & Me.SomeNumericControl & ")"

DoCmd.OpenReport "reportname", , , strWhere
End Sub


I'm not exactly sure what it is you need to accomplish, but maybe this will
shed a little bit of light.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Nick X said:
Hi all,
I have an Access db that is used by another piece of software (which I have
no access to that code) as a delivery system for reports. I am able to
manipulate the Access portion and have been able to open the reports based on
a selection in an Option Group . Now I am trying to use Allen Browne's "Open
the report filtered to the items selected in the list box.", but i can't
quite figure out how to twist it into this situation (I have gotten it to
work before). Remember, the reports are launched from a third party software
that I do not control. So i I need to get this to work in the OpenReport
procedure. thanks!

Private Sub Report_Open(Cancel As Integer)

DoCmd.OpenForm "frmSelectFunc", , , , , acDialog
If Not IsLoaded("frmSelectFunc") Then
Cancel = True
End If

DoCmd.Maximize

End Sub

Private Sub cmdPrevFunc_Click()

'On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Report by Priority Ranking"

'Loop through the ItemsSelected in the list box.
With Me.lstFuncClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(2, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
'DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Me.Visible = False

'Exit_Handler:
' Exit Sub

'Err_Handler:
' If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
' MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
' End If
' Resume Exit_Handler
End Sub
Click to expand...
Click to expand...
 
Back
Top