Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

loop through option group 4

Status
Not open for further replies.
THWatson

THWatson

Technical User
Apr 25, 2000
2,601
CA
Using Access 2000

A form has an option group, called "fraReports" with 9 options, also a Print command button.

Behind the Print button there is Select Case and DoCmd code which prints a report that matches the option value selected.

I want to set up a command button that will "Print All" of the reports with one click. I can do that with the following code behind the cmdPrintAll button.
Code: 
Private Sub cmdPrintAll_Click()
Select Case MsgBox("Are you sure you wish to print ALL reports in the packet?", vbYesNo Or vbExclamation Or vbDefaultButton1, "Print ALL check")

    Case vbYes
GoTo PrintAllRoutine
    Case vbNo
Exit Sub

End Select
Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String
Dim stDocName5 As String
Dim stDocName6 As String
Dim stDocName7 As String
Dim stDocName8 As String
Dim stDocName9 As String

Dim parm As String
   On Error GoTo cmdPrintAll_Click_Error

PrintAllRoutine:
parm = "[tblPatInfo].[VISIT ID]='" & Forms!frmPatInfo![VisitID] & "'"

stDocName1 = "rptV1"
stDocName2 = "rptV2"
stDocName3 = "rptV3"
stDocName4 = "rptV4"
stDocName5 = "rptV5"
stDocName6 = "rptV6"
stDocName7 = "rptV7"
stDocName8 = "rptV8"
stDocName9 = "rptV9"

DoCmd.OpenReport stDocName1, acViewNormal, , parm
DoCmd.OpenReport stDocName2, acViewNormal, , parm
DoCmd.OpenReport stDocName3, acViewNormal, , parm
DoCmd.OpenReport stDocName4, acViewNormal, , parm
DoCmd.OpenReport stDocName5, acViewNormal, , parm
DoCmd.OpenReport stDocName6, acViewNormal, , parm
DoCmd.OpenReport stDocName7, acViewNormal, , parm
DoCmd.OpenReport stDocName8, acViewNormal, , parm
DoCmd.OpenReport stDocName9, acViewNormal, , parm

   On Error GoTo 0
   Exit Sub

cmdPrintAll_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPrintAll_Click of VBA Document Form_frmBloodTransfusion"


End Sub

This works...but is there a simpler way to set this up, or is the code I am using as good a method as any?

Thanks.

Tom
 
Sort by date Sort by votes
Why not:

Code: 
For i=1 to 9
DoCmd.OpenReport "rptV" & i, acViewNormal, , parm
Next
 
Upvote 0 Downvote
Remou
Thanks. You answered what I asked. Unfortunately, in an attempt to simplify the report names, I threw in a curve. (my bad)

The actual report names are more complicated than I showed. They are not...
stDocName1 = "rptV1"
stDocName2 = "rptV2"
stDocName3 = "rptV3"
stDocName4 = "rptV4"
stDocName5 = "rptV5"
stDocName6 = "rptV6"
stDocName7 = "rptV7"
stDocName8 = "rptV8"
stDocName9 = "rptV9"

They are more like
stDocName1 = "G40017-Diagnosis"
stDocName2 = "IntermittentResults"
stDocName3 = "3220-Lab"
stDocName4 = "40012-NurseActions"
stDocName5 = "40013-SedationEffects"
stDocName6 = "GuardsInstructions"
stDocName7 = "8339-Signatures"
stDocName8 = "50011-PreDischarge"
stDocName9 = "DischargeInstructions"

I apologize for my over-simplifying the situation.

Tom
 
Upvote 0 Downvote
And what about this ?
For Each stDocName In Array("G40017-Diagnosis", "IntermittentResults", "3220-Lab", "40012-NurseActions" _
, "40013-SedationEffects", "GuardsInstructions", "8339-Signatures", "50011-PreDischarge", "DischargeInstructions"
DoCmd.OpenReport stDocName, acViewNormal, , parm
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
how are ya THWatson . . .

How about this:
Code: 
[blue]   Dim rptName As String, idx As Integer, parm As String
   Dim Msg As String, Style As Integer, Title As String
   
   parm = "[tblPatInfo].[VISIT ID]='" & Forms!frmPatInfo![VisitID] & "'"
   
   Msg = "Are you sure you wish to print ALL reports in the packet?"
   Style = vbYesNo + vbExclamation + vbDefaultButton1
   Title = "Print ALL check"
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      Do
         idx = idx + 1
         rptName = Choose(idx, "rptV1", "rptV2", "rptV3", _
                               "rptV4", "rptV5", "rptV6", _
                               "rptV7", "rptV8", "rptV9")
         DoCmd.OpenReport rptName, acViewNormal, , parm
      Loop Until idx = 9
   End If[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see FAQ219-2884:
 
Upvote 0 Downvote
Woops . . . forgot about the actual names:
Code: 
[blue]      Do
         idx = idx + 1
         rptName = Choose(idx, "G40017-Diagnosis", _
                               "IntermittentResults", _
                               "3220-Lab", _
                               "40012-NurseActions", _
                               "40013-SedationEffects", _
                               "GuardsInstructions", _
                               "8339-Signatures", _
                               "50011-PreDischarge", _
                               "DischargeInstructions")
         DoCmd.OpenReport rptName, acViewNormal, , parm
      Loop Until idx = 9
[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see FAQ219-2884:
 
Upvote 0 Downvote
It occurs to me that this would all be a lot simpler with a table of reports which could be used as the recordsource for a multiselect list box. The user could then click the relevant reports. That way, if a report is added, removed or modified, there is no need to change any code.
 
Upvote 0 Downvote
TheAceMan1 and PHV
Both of these approaches do the job quite nicely. Thanks very much.

Remou
Your approach is quite different but sounds interesting. One of the difficulties is that the user will not want the names that appear in the multi-select list box to be exactly the same as the names of the reports.

For example, from the names above, the report name would be 40013-SedationEffects but the user wants the name to appear on the form, whether in an option group or in a multi-select list box, as 40013 - SEDATION EFFECTS

So an accommodation has to be made in the table for both the report name and the name to show in the multi-select list box.

Additionally, there are 6 different forms and a total of 52 reports over all.

I will tinker with this approach and see whether or not this would, in fact, save maintenance time over the long haul. In the meantime, thanks for the idea.

Tom
 
Upvote 0 Downvote
A listbox can have more than one column. Columns can be hidden.

Code: 
Private Sub cmdPrint_Click()
    If Me.lstReports.ItemsSelected.Count = 0 Then
        MsgBox "Please select one or more reports."
        Exit Sub
    End If
    
    'Double check using the column with 'friendly' names
    For Each itm In Me.lstReports.ItemsSelected
        strToPrint = strToPrint & vbCrLf & Me.lstReports.Column(1, itm)
    Next
    
    If MsgBox("Do you wish to print " _
        & strToPrint & "?", vbYesNo) = vbYes Then
        'Print using hidden report name column.
        For Each itm In Me.lstReports.ItemsSelected
            strDoc = Me.lstReports.Column(2, itm)
            DoCmd.OpenReport strDoc, acViewNormal, , parm
        Next
    End If
End Sub
 
Upvote 0 Downvote
Remou
Yep. Works nicely.

Here's the full code behind the command button...
Code: 
Dim strDoc As String
Dim itm As Variant
Dim parm As String
Dim strToPrint As String

parm = "[tblPatInfo].[VISIT ID]='" & Forms!frmPatInfo![VisitID] & "'"

If Me.lstReports.ItemsSelected.Count = 0 Then
        MsgBox "Please select one or more reports."
        Exit Sub
    End If
    
    'Double check using the column with 'friendly' names
    For Each itm In Me.lstReports.ItemsSelected
        strToPrint = strToPrint & vbCrLf & Me.lstReports.Column(2, itm)
    Next
    
    If MsgBox("Do you wish to print " _
        & strToPrint & "?", vbYesNo) = vbYes Then
        'Print using hidden report name column.
        For Each itm In Me.lstReports.ItemsSelected
            strDoc = Me.lstReports.Column(1, itm)
            DoCmd.OpenReport strDoc, acViewNormal, , parm
        Next
    End If

I'll see what the user thinks.

I haven't tried this approach before. Thanks for showing me something different.

Tom
 
Upvote 0 Downvote
Dan
Thanks.

I know what you say, but that doesn't work in this situation. There are 52 reports, and 6 menus representing 6 different departments. The 52 reports are divided among these 6. So we don't want to list all of the reports on each of the menus.

Tom
 
Upvote 0 Downvote
I have programmed the list box, using a Union Query as its source, so that the first item in the list is <ALL> which allows for the entire list to be selected at once.

How do I change the code below so that when <ALL> is selected the entire list is selected to print?

I have spent a while trying to get it right but have a little slip somewhere.

Code: 
Dim strDoc As String
Dim itm As Variant
Dim parm As String
Dim strToPrint As String

parm = "[tblPatInfo].[VISIT ID]='" & Forms!frmPatInfo![VisitID] & "'"

If Me.lstReports.ItemsSelected.Count = 0 Then
        MsgBox "Please select one or more reports."
        Exit Sub
    End If
    
    'Double check using the column with 'friendly' names
    For Each itm In Me.lstReports.ItemsSelected
        strToPrint = strToPrint & vbCrLf & Me.lstReports.Column(2, itm)
    Next
    
    If MsgBox("Do you wish to print " _
        & strToPrint & "?", vbYesNo) = vbYes Then
        'Print using hidden report name column.
        For Each itm In Me.lstReports.ItemsSelected
            strDoc = Me.lstReports.Column(1, itm)
            DoCmd.OpenReport strDoc, acViewNormal, , parm
        Next
    End If

Thanks.

Tom
 
Upvote 0 Downvote
You can use:

Code: 
If MsgBox("Do you wish to print " _
        & strToPrint & "?", vbYesNo) = vbYes Then
        'Print using hidden report name column.

    'Assuming that the zeroth entry is All
    If Me.lstReports.Selected(0)=true Then
        For i = 1 To Me.List2.ListCount - 1
            strDoc = Me.lstReports.Column(1, i)
            DoCmd.OpenReport strDoc, acViewNormal, , parm
        Next
    Else
        For Each itm In Me.lstReports.ItemsSelected
            strDoc = Me.lstReports.Column(1, itm)
            DoCmd.OpenReport strDoc, acViewNormal, , parm
        Next
    End If
End If
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

renigar
  • Locked
  • Question
Trying to add hyperlinks to pdfs through form entry
Replies
0
Views
504
renigar
renigar
scubafrog79
  • Locked
  • Question
Loop through a datasheet and change cell value based on two other cells
Replies
4
Views
293
dhookom
dhookom
TBOB61
  • Locked
  • Question
Changing names of text boxes to create loop
Replies
3
Views
171
Andrzejek
Andrzejek
waubain
  • Locked
  • Question
Moving through Recordset does not always see checkbox as true, even if checked.
Replies
1
Views
207
waubain
waubain
VicM
  • Locked
  • Question
Optional argument in subroutine not functioning according to MS
Replies
11
Views
439
VicM
VicM

Part and Inventory Search

Sponsor

Back
Top