Alrighty, I have my Excel code worked out ... YAY!
Now on to the pdf code.
I can post my code here, if needed, but I'll start with a run down in case it's something super obvious (to all except me!) Nevermind, I posted the code below.
I have a unit roster report. I really don't want to duplicate this report because I don't want to have to remember to make changes to two reports.
The unit roster report uses VBA in the detail section and in the footer section. It also opens in print preview when I click on the "Unit Roster" button.
I put together code that creates a recordset of UnitIDs and then cycles through those UnitIDs and creates a pdf in a specific location with a filename based on the type of unit.
The code that creates and cycles through the recordset is a click event on a form while the code for the unit roster report is behind the format event of the detail and the footer.
When I click the "export to PDF" button I created, the Unit Roster report hangs on a line referencing the UnitID. the error I get is Run-time error 2427: You entered an expression that has no value. I'm pretty sure it was because of where the variables were declared. I then move the declarations to a different part of the code and now the Unit Roster report doesn't work at all ... before it worked on its own and hung when using the recordset. Clear as mud, right???
Here is my Unit Roster code:
The footer section VBA is setting up a "page x of y" that changes on each change of unitID
Code:
Option Compare DatabaseOption Explicit
'The following were moved down. Unit roster as a stand alone report worked when the declarations were up here
'Dim GrpArrayPage(), GrpArrayPages()
'Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
'Dim GrpPage As Integer, GrpPages As Integer
'************ Code Start *************
' This code was originally written by James H Brooks.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' James H Brooks
'
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [Type] = "A" Then
txtCampCount.Visible = False
ElseIf [Type] = "DA" Then
txtCampCount.Visible = False
Else
txtCampCount.Visible = True
End If
If [Type] = "G" Then
txtTroop.Visible = True
Else
txtTroop.Visible = False
End If
If [Type] <> "G" Then
txtAge.Visible = True
Else
txtAge.Visible = False
End If
End Sub
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
Dim GrpArrayPage(), GrpArrayPages() 'was at top, under Option Explicit
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant 'was at top
Dim GrpPage As Integer, GrpPages As Integer ' was at top
If Me.Pages = 0 Then
ReDim Preserve GrpArrayPage(Me.Page + 1)
ReDim Preserve GrpArrayPages(Me.Page + 1)
GrpNameCurrent = Me![UnitID] 'when trying the pdf export, this is where it hangs with error 2427:expression with no value
If GrpNameCurrent = GrpNamePrevious Then
GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
GrpPages = GrpArrayPage(Me.Page)
For i = Me.Page - ((GrpPages) - 1) To Me.Page
GrpArrayPages(i) = GrpPages
Next i
Else
GrpPage = 1
GrpArrayPage(Me.Page) = GrpPage
GrpArrayPages(Me.Page) = GrpPage
End If
Else
Me!ctlGrpPages = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page) 'when running the unit roster in a standard way,
'the report hangs here with an error 9: subscript out of range
End If
GrpNamePrevious = GrpNameCurrent
'************ Code End *************
End Sub
The Export to PDF code is:
Code:
Private Sub cmdRoster2pdf_Click()
Dim strPath As String
Dim FileName As String
Dim UnitID As String
Dim rstUnitPDF As DAO.Recordset
Dim strRST_SQL_PDF As String 'SQL statement to set up the recordset
Dim dbs As DAO.Database
If Dir("C:\Users\Susie\Documents\Day Camp 2018 Laptop", vbDirectory) <> "" Then
strPath = "C:\Users\Susie\Documents\Day Camp 2018 Laptop\"
Else
strPath = "C:\Users\Gilsons\Documents\DayCamp 2018\"
End If
Set dbs = CurrentDb
'set up the SQL to extract the recordset … pull unique unit IDs from RegInfo
strRST_SQL_PDF = "SELECT DISTINCT UnitID from RegInfo WHERE Delete = False;"
'open the Unit ID recordset
Set rstUnitPDF = dbs.OpenRecordset(strRST_SQL_PDF, dbOpenDynaset, dbReadOnly)
'check to see where in the recordset has data in it
If rstUnitPDF.EOF = False And rstUnitPDF.BOF = False Then
'move to the beginning of the recordset
rstUnitPDF.MoveFirst
'Start the loop and run as long as the unit ID isn't at the end of the recordset
Do While rstUnitPDF.EOF = False
'pull unit title using UnitID; this will be used to name the sheet
FileName = DLookup("Unit", "qry UnitInfo", "UnitID=" & rstUnitPDF!UnitID.Value)
Debug.Print FileName 'for testing purposes, print the sheet name
'Send the Unit Roster report to pdf based on Unit ID
Debug.Print strPath & FileName & ".pdf" 'for testing purposes, print the full file name
DoCmd.OpenReport "rpt Unit Roster", acViewPreview, , [UnitID] = rstUnitPDF!UnitID.Value
DoCmd.OutputTo acOutputReport, "rpt Unit Roster", acFormatPDF, strPath & FileName & ".pdf", False
DoCmd.Close acReport, "rpt Unit Roster", acSaveNo
rstUnitPDF.MoveNext
'go back to start of loop with new UnitID
Loop
'Exit out of the If Then when the recordset is at the end of the file
End If
'close the recordset and erase it
rstUnitPDF.Close
Set rstUnitPDF = Nothing
dbs.Close
Set dbs = Nothing
MsgBox "The PDF Files were created!", vbInformation Or vbOKOnly, "PDF Files Created"
End Sub
Any thoughts?
Thank you!
Susie
Kansas