Report selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a parametised report. In Reports I select a report and then have to
type in a no., this queries a table and it then reports with correct
selection of data and I can print it. This is rubbish, User will need a form
whit a combo list of report headings (this is a field I hold)
How do I do this please. I am new !
 
Martyn,

If I understand you correctly that you wish to allow your users to select
one or more criteria for a report, the way I do it is to present the users
combo boxes that list the criteria selections on an unbound form.

In the AfterUpdate event procedure of each combo box, a general procedure,
WriteFilterString, is called that loops through all of the form controls, and
builds an SQL statement, writing it to an invisible textbox on the form,
txtFilterString.

When the user presses the Preview Report button, the report either runs the
report with no criteria (if txtFilterString is null) or uses the built string
as the optional Filter parameter of the OpenReport method. Another button
allows the user to clear all criteria.

Somehow, you need to embed in the code which table *field* is associated
which each combo box value. I do it by naming the control "txt" +
<fieldname>. The code then strips off the first 3 characters to get the
field name. Alternatively, you could use the control's Tag property.

Hope that helps.
Sprinks

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "
End If

‘ Handle other control types if they need to be handled differently
here
' To use a textbox value as a wildcard, use the Like operator:

' Me!txtFilterString = Me!txtFilterString & _
' "[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
' & "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "
'

End Select

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Private Sub YourComboOrTextBox_AfterUpdate()
‘ AfterUpdate code for each parameter
Call WriteFilterString
End Sub

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim strFilter As String

strDocName = "YourReport"
strFilter = ""

' If no criteria selected, preview entire report
If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else

‘Otherwise, use filter the report by the built string
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub


Private Sub cmdClearSelection_Click()
' Reset all controls to blank
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Or _
ctl.ControlType = acOptionGroup) Then
ctl.Value = Null
End If
Next ctl
End Sub
 
god this seems a nightmare for a begineer! Think I may have confused you I
hope. All I want to do to start with is pick a report in a form from about a
dozen or so I have created from a drop down list.
Once I have done that I may wish to pick just one item from a dropdown list
to run one of these reports with a default being ALL

Sprinks said:
Martyn,

If I understand you correctly that you wish to allow your users to select
one or more criteria for a report, the way I do it is to present the users
combo boxes that list the criteria selections on an unbound form.

In the AfterUpdate event procedure of each combo box, a general procedure,
WriteFilterString, is called that loops through all of the form controls, and
builds an SQL statement, writing it to an invisible textbox on the form,
txtFilterString.

When the user presses the Preview Report button, the report either runs the
report with no criteria (if txtFilterString is null) or uses the built string
as the optional Filter parameter of the OpenReport method. Another button
allows the user to clear all criteria.

Somehow, you need to embed in the code which table *field* is associated
which each combo box value. I do it by naming the control "txt" +
<fieldname>. The code then strips off the first 3 characters to get the
field name. Alternatively, you could use the control's Tag property.

Hope that helps.
Sprinks

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "
End If

‘ Handle other control types if they need to be handled differently
here
' To use a textbox value as a wildcard, use the Like operator:

' Me!txtFilterString = Me!txtFilterString & _
' "[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
' & "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "
'

End Select

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Private Sub YourComboOrTextBox_AfterUpdate()
‘ AfterUpdate code for each parameter
Call WriteFilterString
End Sub

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim strFilter As String

strDocName = "YourReport"
strFilter = ""

' If no criteria selected, preview entire report
If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else

‘Otherwise, use filter the report by the built string
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub


Private Sub cmdClearSelection_Click()
' Reset all controls to blank
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Or _
ctl.ControlType = acOptionGroup) Then
ctl.Value = Null
End If
Next ctl
End Sub



martyn said:
I have a parametised report. In Reports I select a report and then have to
type in a no., this queries a table and it then reports with correct
selection of data and I can print it. This is rubbish, User will need a form
whit a combo list of report headings (this is a field I hold)
How do I do this please. I am new !
Click to expand...
Click to expand...
 
Martyn,

OK, but hang onto the code for when you want more run-time control of your
reports. ;)

Assuming the first combo box is text representing the name of a report, and
the second is text representing the name of a stored query, pass the name of
the query to the OpenReport method's Filter parameter. The example assumes
combo box names of cboReport and cboCriteria, respectively.

Dim stDocName As String
Dim stCriteria As String

stDocName = Me![cboReport]
stCriteria = Me![cboCriteria]

DoCmd.OpenReport stDocName, acPreview, stCriteria

If not all of the queries apply to all of the reports, you could create a
table, ReportQueries:

ReportQueries
-------------------
ReportQueryID AutoNumber (Primary Key)
ReportName Text
QueryName Text

You could then use the report selection in combo box one to filter which
queries show up in the 2nd. Search the forum on "Cascading Combo Boxes" for
how to do this.

Hope that helps.
Sprinks


martyn said:
god this seems a nightmare for a begineer! Think I may have confused you I
hope. All I want to do to start with is pick a report in a form from about a
dozen or so I have created from a drop down list.
Once I have done that I may wish to pick just one item from a dropdown list
to run one of these reports with a default being ALL

Sprinks said:
Martyn,

If I understand you correctly that you wish to allow your users to select
one or more criteria for a report, the way I do it is to present the users
combo boxes that list the criteria selections on an unbound form.

In the AfterUpdate event procedure of each combo box, a general procedure,
WriteFilterString, is called that loops through all of the form controls, and
builds an SQL statement, writing it to an invisible textbox on the form,
txtFilterString.

When the user presses the Preview Report button, the report either runs the
report with no criteria (if txtFilterString is null) or uses the built string
as the optional Filter parameter of the OpenReport method. Another button
allows the user to clear all criteria.

Somehow, you need to embed in the code which table *field* is associated
which each combo box value. I do it by naming the control "txt" +
<fieldname>. The code then strips off the first 3 characters to get the
field name. Alternatively, you could use the control's Tag property.

Hope that helps.
Sprinks

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "
End If

‘ Handle other control types if they need to be handled differently
here
' To use a textbox value as a wildcard, use the Like operator:

' Me!txtFilterString = Me!txtFilterString & _
' "[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
' & "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "
'

End Select

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Private Sub YourComboOrTextBox_AfterUpdate()
‘ AfterUpdate code for each parameter
Call WriteFilterString
End Sub

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim strFilter As String

strDocName = "YourReport"
strFilter = ""

' If no criteria selected, preview entire report
If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else

‘Otherwise, use filter the report by the built string
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub


Private Sub cmdClearSelection_Click()
' Reset all controls to blank
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Or _
ctl.ControlType = acOptionGroup) Then
ctl.Value = Null
End If
Next ctl
End Sub



martyn said:
I have a parametised report. In Reports I select a report and then have to
type in a no., this queries a table and it then reports with correct
selection of data and I can print it. This is rubbish, User will need a form
whit a combo list of report headings (this is a field I hold)
How do I do this please. I am new !
Click to expand...
Click to expand...
Click to expand...
 
many thanks, I will look into this

Sprinks said:
Martyn,

OK, but hang onto the code for when you want more run-time control of your
reports. ;)

Assuming the first combo box is text representing the name of a report, and
the second is text representing the name of a stored query, pass the name of
the query to the OpenReport method's Filter parameter. The example assumes
combo box names of cboReport and cboCriteria, respectively.

Dim stDocName As String
Dim stCriteria As String

stDocName = Me![cboReport]
stCriteria = Me![cboCriteria]

DoCmd.OpenReport stDocName, acPreview, stCriteria

If not all of the queries apply to all of the reports, you could create a
table, ReportQueries:

ReportQueries
-------------------
ReportQueryID AutoNumber (Primary Key)
ReportName Text
QueryName Text

You could then use the report selection in combo box one to filter which
queries show up in the 2nd. Search the forum on "Cascading Combo Boxes" for
how to do this.

Hope that helps.
Sprinks


martyn said:
god this seems a nightmare for a begineer! Think I may have confused you I
hope. All I want to do to start with is pick a report in a form from about a
dozen or so I have created from a drop down list.
Once I have done that I may wish to pick just one item from a dropdown list
to run one of these reports with a default being ALL

Sprinks said:
Martyn,

If I understand you correctly that you wish to allow your users to select
one or more criteria for a report, the way I do it is to present the users
combo boxes that list the criteria selections on an unbound form.

In the AfterUpdate event procedure of each combo box, a general procedure,
WriteFilterString, is called that loops through all of the form controls, and
builds an SQL statement, writing it to an invisible textbox on the form,
txtFilterString.

When the user presses the Preview Report button, the report either runs the
report with no criteria (if txtFilterString is null) or uses the built string
as the optional Filter parameter of the OpenReport method. Another button
allows the user to clear all criteria.

Somehow, you need to embed in the code which table *field* is associated
which each combo box value. I do it by naming the control "txt" +
<fieldname>. The code then strips off the first 3 characters to get the
field name. Alternatively, you could use the control's Tag property.

Hope that helps.
Sprinks

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "
End If

‘ Handle other control types if they need to be handled differently
here
' To use a textbox value as a wildcard, use the Like operator:

' Me!txtFilterString = Me!txtFilterString & _
' "[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
' & "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "
'

End Select

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Private Sub YourComboOrTextBox_AfterUpdate()
‘ AfterUpdate code for each parameter
Call WriteFilterString
End Sub

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim strFilter As String

strDocName = "YourReport"
strFilter = ""

' If no criteria selected, preview entire report
If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else

‘Otherwise, use filter the report by the built string
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub


Private Sub cmdClearSelection_Click()
' Reset all controls to blank
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Or _
ctl.ControlType = acOptionGroup) Then
ctl.Value = Null
End If
Next ctl
End Sub



:

I have a parametised report. In Reports I select a report and then have to
type in a no., this queries a table and it then reports with correct
selection of data and I can print it. This is rubbish, User will need a form
whit a combo list of report headings (this is a field I hold)
How do I do this please. I am new !
Click to expand...
Click to expand...
Click to expand...
 
Back
Top