Two reports open after button click

[Susan L]

I have a series of three reports, each to be opened by a command button. I
created the first report, and then did a Save As to create the second and
third reports.

The first command button opens the report called "Active". The second and
third buttons open not only the report specified ("Inactive" and "In
production"), but also open the "Active" report.

I have checked and rechecked the code and created a fresh command button,
but it exhibits the same behavior. I have imported all objects into a fresh
database, compiled the code, and compacted the database.

Can anyone explain what's going on?

 

[Duane Hookom]

How about sharing your code?

 

[Susan L]

Duan: Here's the code. I just recreated all buttons and the same behavior is
exhibited. I used the wizard.

This code opens only one report:
Private Sub cmdOpenActive_Click()
On Error GoTo Err_cmdOpenActive_Click

Dim stDocName As String

stDocName = "rpt_SFTP_Partners_Status_Active"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenActive_Click:
Exit Sub

Err_cmdOpenActive_Click:
MsgBox Err.Description
Resume Exit_cmdOpenActive_Click

End Sub

This code each opens the specified report – as well as the one above.
Private Sub cmdOpenInactiveSuspended_Click()
On Error GoTo Err_cmdOpenInactiveSuspended_Click

Dim stDocName As String

stDocName = "rpt_SFTP_Partners_Status_InactiveSuspended"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenInactiveSuspended_Click:
Exit Sub

Err_cmdOpenInactiveSuspended_Click:
MsgBox Err.Description
Resume Exit_cmdOpenInactiveSuspended_Click

End Sub
Private Sub cmdOpenInProd_Click()
On Error GoTo Err_cmdOpenInProd_Click

Dim stDocName As String

stDocName = "rpt_SFTP_Partners_Status_InProduction"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenInProd_Click:
Exit Sub

Err_cmdOpenInProd_Click:
MsgBox Err.Description
Resume Exit_cmdOpenInProd_Click

End Sub
Private Sub cmdOpenActInProd_Click()
On Error GoTo Err_cmdOpenActInProd_Click

Dim stDocName As String

stDocName = "rpt_SFTP_Partners_Status_ActiveInProd"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdOpenActInProd_Click:
Exit Sub

Err_cmdOpenActInProd_Click:
MsgBox Err.Description
Resume Exit_cmdOpenActInProd_Click

End Sub

 

[Duane Hookom]

Are there actually two reports opening in separate windows or is this a
matter of the records returned in one report?

If the reports have the same format and only report a different subset of
records, you should only create one report and then modify the report's
record source query criteria or the filter.

 

[Susan L]

Duane: Two reports in separate windows open. Yes, the difference in the
reports is record source, but I don't know how to do the code to do that. I
suppose it would be a sql statement in the code for the button.

Could you explain how to start.

 

[Duane Hookom]

Generally, you start with code created by the command button wizard. You
create controls on your form that allows users to enter or select criteria to
filter the records in your report. You can then use the WHERE CONDITION of
the DoCmd.OpenReport method. Let's assume you have a combo box on your form
that allows the user to select the status:
control name: cboStatus
Data Type: Text
Field Name in report's Record Source: Status

Private Sub cmdOpenActive_Click()
On Error GoTo Err_cmdOpenActive_Click

Dim stDocName As String
' add next 6 lines
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboStatus) Then
strWhere = strWhere & " AND [Status]=""" & _
Me.cboStatus & """ "
End If

stDocName = "rpt_SFTP_Partners"
DoCmd.OpenReport stDocName, acPreview, , strWhere ' added ,, strWhere

Exit_cmdOpenActive_Click:
Exit Sub

Err_cmdOpenActive_Click:
MsgBox Err.Description
Resume Exit_cmdOpenActive_Click

End Sub