2 reports based on one query

  • Thread starter Thread starter randria
  • Start date Start date
R

randria

Hello,

I have a form that supplies parameters to a query. I have 2 different
reports ( OSrpt and ONrpt ) depending on this query. I want to preview OSrpt
if the categorieID of the supplier selected by the user is equal to 1 and if
not to preview ONrpt instead.
this is the code I used but it selects only "ONrpt".

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim CategorieID As long
if CategorieID=1 Then
stDocName = "OSrpt"
DoCmd.OpenReport stDocName, acPreview
else
stDocName= "ONrpt"
DoCmd.OpenReport stDocName, acPreview
End if

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

the problem is that even if the 1st condition is true, it displays the
records on the report "ONrpt".
Many thanks.
randria
 
If this were mine, I'd add a breakpoint into the code and run it, stepping
through each line to figure out if/where it "breaks".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hello,

I have a form that supplies parameters to a query. I have 2 different
reports ( OSrpt and ONrpt ) depending on this query. I want to preview OSrpt
if the categorieID of the supplier selected by the user is equal to 1 and if
not to preview ONrpt instead.
this is the code I used but it selects only "ONrpt".

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim CategorieID As long
if CategorieID=1 Then
stDocName = "OSrpt"
DoCmd.OpenReport stDocName, acPreview
else
stDocName= "ONrpt"
DoCmd.OpenReport stDocName, acPreview
End if

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

the problem is that even if the 1st condition is true, it displays the
records on the report "ONrpt".
Many thanks.
randria
Click to expand...

Regarding ...
Dim CategorieID As long
if CategorieID=1 Then
When your code runs, CategorieID is ALWAYS 0.
No where in the above to you give it a value.

If the only difference between the 2 reports is the criteria, then you
should have just one report, and filter the data using the Where
clause argument of the OpenReport method. See VBA help on the
OpenReport method and also
Where clause + Restrict data to a subset of records.

If there is more than just the criteria difference between the 2
reports, then you must give CategoryID a value, i.e.
Dim CategoryID as Long
CategoryID = [SomeControlValue]
If CategoryID = 1 Then
.... etc...

Though if you are doing that, then it's just simpler to use:

Dim stDocName as String
If [SomeControlValue] = 1 then
stDocName = "OSrpt"
Else
stDocName = "ONrpt"
End If
DoCmd.OpenReport stDocName, acViewPreview
 
randria said:
I have a form that supplies parameters to a query. I have 2 different
reports ( OSrpt and ONrpt ) depending on this query. I want to preview OSrpt
if the categorieID of the supplier selected by the user is equal to 1 and if
not to preview ONrpt instead.
this is the code I used but it selects only "ONrpt".

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim CategorieID As long
if CategorieID=1 Then
stDocName = "OSrpt"
DoCmd.OpenReport stDocName, acPreview
else
stDocName= "ONrpt"
DoCmd.OpenReport stDocName, acPreview
End if

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
Click to expand...


If CategorieID is a control on the form, then remove the
line:
Dim CategorieID As Long
 
Hi Jeff, Fred and Marshall

Many thanks for your repiies they all gave me some useful options, I have to
have 2 reports and Fred's code solved the problem. Below is the code:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim CategorieID As long
CategorieID=[cbo0] ' this is what was missing the combo on the form.
if CategorieID=1 Then
stDocName = "OSrpt"
DoCmd.OpenReport stDocName, acPreview
else
stDocName= "ONrpt"
DoCmd.OpenReport stDocName, acPreview
End if

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
Many thanks.

fredg said:
Hello,

I have a form that supplies parameters to a query. I have 2 different
reports ( OSrpt and ONrpt ) depending on this query. I want to preview OSrpt
if the categorieID of the supplier selected by the user is equal to 1 and if
not to preview ONrpt instead.
this is the code I used but it selects only "ONrpt".

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim CategorieID As long
if CategorieID=1 Then
stDocName = "OSrpt"
DoCmd.OpenReport stDocName, acPreview
else
stDocName= "ONrpt"
DoCmd.OpenReport stDocName, acPreview
End if

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

the problem is that even if the 1st condition is true, it displays the
records on the report "ONrpt".
Many thanks.
randria
Click to expand...

Regarding ...
Dim CategorieID As long
if CategorieID=1 Then
When your code runs, CategorieID is ALWAYS 0.
No where in the above to you give it a value.

If the only difference between the 2 reports is the criteria, then you
should have just one report, and filter the data using the Where
clause argument of the OpenReport method. See VBA help on the
OpenReport method and also
Where clause + Restrict data to a subset of records.

If there is more than just the criteria difference between the 2
reports, then you must give CategoryID a value, i.e.
Dim CategoryID as Long
CategoryID = [SomeControlValue]
If CategoryID = 1 Then
.... etc...

Though if you are doing that, then it's just simpler to use:

Dim stDocName as String
If [SomeControlValue] = 1 then
stDocName = "OSrpt"
Else
stDocName = "ONrpt"
End If
DoCmd.OpenReport stDocName, acViewPreview
Click to expand...
 
Back
Top