Help batch printing multiple certificates

[Victor Dacey]

Hello,

I have an individual who completes his study. He has multiple licenses and I want to print all licenses in a batch so I can mail them to the individual.

Currently, I am doing each license by themselves and hand sorting to a pile, then putting X number of certificates in a envelope.

The following code is working SOMETIMES:

 
Private Sub Command124_Click()
    Dim stState As String
    Dim stLicense As String
    Dim stCert As String
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
 
    Set rst = New ADODB.Recordset
    Set conn = CurrentProject.Connection
 
    rst.Open "Select * from tblTempCourse", conn, adOpenStatic
    Forms![frmReports]![LabelType] = 3
 
Do While Not rst.EOF
 
    Forms![frmReports]![AdvCert] = " "
    Forms![frmReports]![TypeLicense] = " "
    Forms![frmReports]![StName] = " "
 
    stLicense = rst.Fields("StateName").Value
    Forms![frmReports]![TypeLicense] = stLicense
 
    stState = rst.Fields("CreditState").Value
    Forms![frmReports]![StName] = stState
 
    stCert = rst.Fields("LicenseType").Value
    Forms![frmReports]![AdvCert] = stCert
 
If stLicense = stCert Then
    If stState = "AK" Then
    DoCmd.OpenReport "rptAK", acPreview
    ElseIf stState = "AL" Then
    DoCmd.OpenReport "rptAL", acPreview
    ElseIf stState = "AR" Then
    DoCmd.OpenReport "rptAR", acPreview
    ElseIf stState = "CA" Then
    DoCmd.OpenReport "rptCA", acPreview
    ElseIf stState = "CO" Then
    DoCmd.OpenReport "rptCO", acPreview
    ElseIf stState = "DC" Then
    DoCmd.OpenReport "rptDC", acPreview
    ElseIf stState = "DE" Then
    DoCmd.OpenReport "rptDE", acPreview
    ElseIf stState = "FL" Then
    DoCmd.OpenReport "rptFL", acPreview
    ElseIf stState = "FB" Then
    DoCmd.OpenReport "rptFB", acPreview
    ElseIf stState = "GA" Then
    DoCmd.OpenReport "rptGA", acPreview
    ElseIf stState = "HI" Then
    DoCmd.OpenReport "rptHI", acPreview
    ElseIf stState = "IA" Then
    DoCmd.OpenReport "rptIA", acPreview
    ElseIf stState = "IL" Then
    DoCmd.OpenReport "rptIL", acPreview
    ElseIf stState = "KS" Then
    DoCmd.OpenReport "rptKS", acPreview
    ElseIf stState = "KY" Then
    DoCmd.OpenReport "rptKY", acPreview
    ElseIf stState = "LA" Then
    DoCmd.OpenReport "rptLA", acPreview
    ElseIf stState = "MA" Then
    DoCmd.OpenReport "rptMA", acPreview
    ElseIf stState = "MD" Then
    DoCmd.OpenReport "rptMD", acPreview
    ElseIf stState = "ME" Then
    DoCmd.OpenReport "rptME", acPreview
    ElseIf stState = "MN" Then
    DoCmd.OpenReport "rptMN", acPreview
    ElseIf stState = "MO" Then
    DoCmd.OpenReport "rptMO", acPreview
    ElseIf stState = "MS" Then
    DoCmd.OpenReport "rptMS", acPreview
    ElseIf stState = "MT" Then
    DoCmd.OpenReport "rptMT", acPreview
    ElseIf stState = "NC" Then
    DoCmd.OpenReport "rptNC", acPreview
    ElseIf stState = "ND" Then
    DoCmd.OpenReport "rptND", acPreview
    ElseIf stState = "NE" Then
    DoCmd.OpenReport "rptNE", acPreview
    ElseIf stState = "NH" Then
    DoCmd.OpenReport "rptNH", acPreview
    ElseIf stState = "NJ" Then
    DoCmd.OpenReport "rptNJ", acPreview
    ElseIf stState = "NM" Then
    DoCmd.OpenReport "rptNM", acPreview
    ElseIf stState = "NV" Then
    DoCmd.OpenReport "rptNV", acPreview
    ElseIf stState = "NY" Then
    DoCmd.OpenReport "rptNY", acPreview
    ElseIf stState = "OH" Then
    DoCmd.OpenReport "rptOH", acPreview
    ElseIf stState = "OK" Then
    DoCmd.OpenReport "rptOK", acPreview
    ElseIf stState = "OR" Then
    DoCmd.OpenReport "rptOR", acPreview
    ElseIf stState = "PA" Then
    DoCmd.OpenReport "rptPA", acPreview
    ElseIf stState = "RI" Then
    DoCmd.OpenReport "rptRI", acPreview
    ElseIf stState = "SC" Then
    DoCmd.OpenReport "rptSC", acPreview
    ElseIf stState = "SD" Then
    DoCmd.OpenReport "rptSD", acPreview
    ElseIf stState = "TN" Then
    DoCmd.OpenReport "rptTN", acPreview
    ElseIf stState = "TX" Then
    DoCmd.OpenReport "rptTX", acPreview
    ElseIf stState = "UT" Then
    DoCmd.OpenReport "rptUT", acPreview
    ElseIf stState = "VA" Then
    DoCmd.OpenReport "rptVA", acPreview
    ElseIf stState = "VT" Then
    DoCmd.OpenReport "rptVT", acPreview
    ElseIf stState = "WA" Then
    DoCmd.OpenReport "rptWA", acPreview
    ElseIf stState = "WV" Then
    DoCmd.OpenReport "rptWV", acPreview
    ElseIf stState = "WY" Then
    DoCmd.OpenReport "rptWY", acPreview
    'Else
    'MsgBox "No Report for that State."
    End If
End If
 
    'stCert = rst.Fields("LicenseType").Value
    'Forms![frmReports]![AdvCert] = stCert
 
If stLicense <> stCert Or stState = stLicense Then
 
    If stCert = "CFP" Then
    DoCmd.OpenReport "rptCFP", acPreview
    ElseIf stCert = "ChFC" Then
    DoCmd.OpenReport "rptChFC", acPreview
    ElseIf stCert = "CPA" Then
    DoCmd.OpenReport "rptCPA", acPreview
    ElseIf stCert = "CRC" Then
    DoCmd.OpenReport "rptCRC", acPreview
    ElseIf stCert = "CASL" Then
    DoCmd.OpenReport "rptCASL", acPreview
    ElseIf stCert = "RHU" Then
    DoCmd.OpenReport "rptRHU", acPreview
    ElseIf stCert = "CLU" Then
    DoCmd.OpenReport "rptCLU", acPreview
    ElseIf stCert = "CRPC" Then
    DoCmd.OpenReport "rptCRPC", acPreview
    ElseIf stCert = "CRPS" Then
    DoCmd.OpenReport "rptCRPS", acPreview
    ElseIf stCert = "CIMA" Then
    DoCmd.OpenReport "rptCIMA", acPreview
    ElseIf stCert = "CMFC" Then
    DoCmd.OpenReport "rptCMFC", acPreview
    ElseIf stCert = "LUTC" Then
    DoCmd.OpenReport "rptLUTC", acPreview
    'Else
    'MsgBox "No Report for that Certificate."
    End If
End If
 
rst.MoveNext
Loop
 
    Forms![frmReports]![AdvCert] = " "
    Forms![frmReports]![TypeLicense] = " "
    Forms![frmReports]![StName] = " "
 
    rst.Close
    Set rst = Nothing
    conn.Close
    Set conn = Nothing
 
End Sub
 
 

Basically, I would like the print job to get the individual, print a state, then any other certs, and then move to next person.

Thanks for your help.