Open report using value list and query with command button

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

Guest

I am trying to filter a report with several different queries that are used
in a list box based on a value list.

I think the problem may be in the RunReport private sub but I'm not sure.
When I click the report button, no matter which value I have selected in the
list, it gives me the entire list. The name of my report is ActiveJobList.
I'm sure it's something simple I'm missing here but I'm new to VB and Access.

Here is my code. Thanks!



code:--------------------------------------------------------------------------------

Private Sub cmdReport_Click()
On Error GoTo Oops
Select Case ListAll.ListIndex
Case -1: MsgBox "Please choose the report to run."
Case 0: SourceQuery = "JobListAll"
Case 1: SourceQuery = "JobListChurch"
Case 2: SourceQuery = "JobListDesign"
Case 3: SourceQuery = "JobListMSP"
Case 4: SourceQuery = "JobListMSTP"
Case 5: SourceQuery = "JobListWillie"
Case 6: SourceQuery = "JobListSalem"
Case 7: SourceQuery = "Backlog"

End Select
RunReport
Exit Sub
Oops:
MsgBox "Error running the chosen report." & vbCrLf & Err.Number & " - "
& Err.Description
End Sub


Private Sub RunReport()
On Error GoTo Oops
If SourceQuery <> "" Then
DoCmd.OpenReport "ActiveJobList", acViewPreview
End If
Exit Sub
Oops:
Err.Raise Err.Number, "ActiveJobList.RunReport", Err.Description
End Sub
 
One idea would to be to use the SourceQuery variable that you have set. It
does not look like you do that in the RunReport method.

You can either change the Record Source property of the report, or use the
filter parameter of the OpenReport method to filter your results.


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


message
I am trying to filter a report with several different queries that are used
in a list box based on a value list.

I think the problem may be in the RunReport private sub but I'm not sure.
When I click the report button, no matter which value I have selected in the
list, it gives me the entire list. The name of my report is ActiveJobList.
I'm sure it's something simple I'm missing here but I'm new to VB and
Access.

Here is my code. Thanks!



code:-----------------------------------------------------------------------
---------

Private Sub cmdReport_Click()
On Error GoTo Oops
Select Case ListAll.ListIndex
Case -1: MsgBox "Please choose the report to run."
Case 0: SourceQuery = "JobListAll"
Case 1: SourceQuery = "JobListChurch"
Case 2: SourceQuery = "JobListDesign"
Case 3: SourceQuery = "JobListMSP"
Case 4: SourceQuery = "JobListMSTP"
Case 5: SourceQuery = "JobListWillie"
Case 6: SourceQuery = "JobListSalem"
Case 7: SourceQuery = "Backlog"

End Select
RunReport
Exit Sub
Oops:
MsgBox "Error running the chosen report." & vbCrLf & Err.Number & " - "
& Err.Description
End Sub


Private Sub RunReport()
On Error GoTo Oops
If SourceQuery <> "" Then
DoCmd.OpenReport "ActiveJobList", acViewPreview
End If
Exit Sub
Oops:
Err.Raise Err.Number, "ActiveJobList.RunReport", Err.Description
End Sub
 
naiveprogrammer said:
I am trying to filter a report with several different queries that are used
in a list box based on a value list.

I think the problem may be in the RunReport private sub but I'm not sure.
When I click the report button, no matter which value I have selected in the
list, it gives me the entire list. The name of my report is ActiveJobList.
I'm sure it's something simple I'm missing here but I'm new to VB and Access.

Here is my code. Thanks!



code:--------------------------------------------------------------------------------

Private Sub cmdReport_Click()
On Error GoTo Oops
Select Case ListAll.ListIndex
Case -1: MsgBox "Please choose the report to run."
Case 0: SourceQuery = "JobListAll"
Case 1: SourceQuery = "JobListChurch"
Case 2: SourceQuery = "JobListDesign"
Case 3: SourceQuery = "JobListMSP"
Case 4: SourceQuery = "JobListMSTP"
Case 5: SourceQuery = "JobListWillie"
Case 6: SourceQuery = "JobListSalem"
Case 7: SourceQuery = "Backlog"

End Select
RunReport
Exit Sub
Oops:
MsgBox "Error running the chosen report." & vbCrLf & Err.Number & " - "
& Err.Description
End Sub


Private Sub RunReport()
On Error GoTo Oops
If SourceQuery <> "" Then
DoCmd.OpenReport "ActiveJobList", acViewPreview
End If
Exit Sub
Oops:
Err.Raise Err.Number, "ActiveJobList.RunReport", Err.Description
End Sub
Click to expand...


It looks like you are changing the record source of the report. If so, then
you can use the OpenArgs property to pass the name of the query to the report
and use the "OnLoad" form event to change the record source.

Your report should have the record source set to the "JobListAll" query.
Then put the code below in the click event of the button "cmdReport".

You don't need 2 subs. Here is the modified code. I did a little testing,
but it should work.

'******* BEGIN CODE ****
Private Sub cmdReport_Click()
On Error GoTo Oops
Dim strOpenArgs As String
' append an empty string to handle NULLs
strOpenArgs = Me.ListAll & ""

If Len(Trim(strOpenArgs)) > 0 Then
If strOpenArgs = "JobListAll" Then
' this selects all records
strOpenArgs = ""
End If

'next line for debugging
'MsgBox strOpenArgs

' open the report
DoCmd.OpenReport "ActiveJobList", acViewPreview, , , , strOpenArgs
Else
' didn't select an item in the list box
MsgBox "Please choose the report to run."
Me.ListAll.SetFocus
' uncomment the next line to auto select the first item
'Me.ListAll.Selected(0) = True
End If

Exit Sub
Oops:
MsgBox "Error running the chosen report." & vbCrLf & Err.Number & " - "
& Err.Description
End Sub

'******* END CODE *******


Watch for line wrap at the last msgbox......

HTH
 
Oops, forgot the code for the report ... :-(

Add this code to the OnLoad event of the report "ActiveJobList"

'****** BEGIN CODE ***
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs
Me.Requery
End Sub
'****** END CODE ****


Steve S
 
I spoke too soon... :-( It all works great except in the OnLoad event of
the report "ActiveJobList".

'****** BEGIN CODE ***
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs
Me.Requery
End Sub
'****** END CODE ****

I get a compile error saying "Method or data member not found". I wonder if
my Globals in my Modules has anything to do with this? I actually inherited
this project. I don't know that much about Modules so I was afraid to get
rid of it. Here is my module global code:

***CODE***
Option Compare Database

Private mSourceQuery As String

Public Property Get SourceQuery() As String
SourceQuery = mSourceQuery
End Property

Public Property Let SourceQuery(ByVal s As String)
mSourceQuery = s
End Property

***END CODE***
 
naiveprogrammer said:
I spoke too soon... :-( It all works great except in the OnLoad event of
the report "ActiveJobList".

'****** BEGIN CODE ***
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs
Me.Requery
End Sub
'****** END CODE ****

I get a compile error saying "Method or data member not found". I wonder if
my Globals in my Modules has anything to do with this? I actually inherited
this project. I don't know that much about Modules so I was afraid to get
rid of it. Here is my module global code:

***CODE***
Option Compare Database

Private mSourceQuery As String

Public Property Get SourceQuery() As String
SourceQuery = mSourceQuery
End Property

Public Property Let SourceQuery(ByVal s As String)
mSourceQuery = s
End Property

***END CODE***
Click to expand...

Looks like I had a bad case of "D A". Reports don't have a "Form_Load"
event....
should have been "Report_Open".

Try this:

'****** BEGIN CODE ***
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Me.OpenArgs
Me.Requery
End Sub
'****** END CODE ****


Just to make sure I understood right .... you do have 8 queries (JobListAll,
JobListChurch, JobListDesign, ...) for this report?
 
Back
Top