DoCmd.OpenReport xxxxx , acViewPreview question

[John de Beer]

Good Evening from San Diego

I use a multi-select listbox to select Cooking cycles that I want to
either print out to a report or view as a report.

I pass a global variable (SQL string) to the Report_Open event
(me.recordsource) Everything works well when I mulit-select for
printing. however when I want to just view the report it hangs up on
the first instance of viewing, the first cooking cycle collected.

I can F8 repeatedly thorugh the code but only the first instance is
available for viewing. Any ideas to help me out?

Thanks

John
------------------------------------------------
' the code in question

Public gstrRptSQL As String
Sub PrintReports()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim strCycleIndx As String

Set dbs = CurrentDb()

Dim intI As Integer
Dim intIndex As Integer

With Form_frmGifNames.lstSeelctCycleIndex
intIndex = 0

For intI = 0 To .ListCount - 1
If .Selected(intI) Then
intIndex = intIndex + 1
strCycleIndx = .ItemData(intI)

gstrRptSQL = ""
gstrRptSQL = gstrRptSQL & "SELECT tblEventLogs.* "
gstrRptSQL = gstrRptSQL & "FROM qryCycleNoEventLogs INNER JOIN
tblEventLogs ON "
gstrRptSQL = gstrRptSQL & "(qryCycleNoEventLogs.ControllerNo =
tblEventLogs.ControllerNo) "
gstrRptSQL = gstrRptSQL & "AND (qryCycleNoEventLogs.CycleNo =
tblEventLogs.CycleNo) "
gstrRptSQL = gstrRptSQL & "WHERE
(((qryCycleNoEventLogs.CycleIndx)= '"
gstrRptSQL = gstrRptSQL & strCycleIndx & "')); "

DoCmd.OpenReport "rptEventLogReport", acViewPreview

End If
Next
End With

End Sub

 

[Marshall Barton]

I use a multi-select listbox to select Cooking cycles that I want to
either print out to a report or view as a report.

I pass a global variable (SQL string) to the Report_Open event
(me.recordsource) Everything works well when I mulit-select for
printing. however when I want to just view the report it hangs up on
the first instance of viewing, the first cooking cycle collected.

I can F8 repeatedly thorugh the code but only the first instance is
available for viewing. Any ideas to help me out?

Thanks

John
------------------------------------------------
' the code in question

Public gstrRptSQL As String
Sub PrintReports()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim strCycleIndx As String

Set dbs = CurrentDb()

Dim intI As Integer
Dim intIndex As Integer

With Form_frmGifNames.lstSeelctCycleIndex
intIndex = 0

For intI = 0 To .ListCount - 1
If .Selected(intI) Then
intIndex = intIndex + 1
strCycleIndx = .ItemData(intI)

gstrRptSQL = ""
gstrRptSQL = gstrRptSQL & "SELECT tblEventLogs.* "
gstrRptSQL = gstrRptSQL & "FROM qryCycleNoEventLogs INNER JOIN
tblEventLogs ON "
gstrRptSQL = gstrRptSQL & "(qryCycleNoEventLogs.ControllerNo =
tblEventLogs.ControllerNo) "
gstrRptSQL = gstrRptSQL & "AND (qryCycleNoEventLogs.CycleNo =
tblEventLogs.CycleNo) "
gstrRptSQL = gstrRptSQL & "WHERE
(((qryCycleNoEventLogs.CycleIndx)= '"
gstrRptSQL = gstrRptSQL & strCycleIndx & "')); "

DoCmd.OpenReport "rptEventLogReport", acViewPreview

End If
Next
End With

End Sub

You can only open a single instance of a report using the
OpenReport method. Executing subsequent OpenReports just
gives the focus to the already open report.

Normally, what's done is to include all the criteria in one
where clause and open a single report that contains all of
the selected items. Grouping on the item and using
ForceNewPage on the group will separate each item to a new
page so it mostly looks like separate reports.

 

[John de Beer]

Thanks, very clear.

jdb