Using combo box to control which report to output/print to PDF?

[DB0]

Hi guys! Hoping to get some help here with my issue. I am sure there is a simple solution to what I'm trying to do but I haven't been able to figure it out.

I've created a form for users to select various types of reports and have them export as PDFs. Originally, there was only one form so everything worked fine. Now, I want users to be able to select different form types and so I've created a combo box that allows them to choose.

I have the following code attached to the print button's onClick event:

If Report_Type.Value = 1 Then
strDocName = "report1"
ElseIf Report_Type.Value = 2 Then
strDocName = "report2"
Else
strDocName = "report3"
End If


When I run it now, I get an error. Any ideas?


For reference, I am using the ConvertReportToPDF module from Lebans to print the reports to PDF. Hope this made sense!

[pbaldy]

It would help to know the error message, and what line it's on.

Also, that on it's own won't do anything, so presumably there's more.

[DB0]

Yes, of course! :P

The error I am getting is:

"Error when attempting to export
The Object Type argument for the action is blank or invalid"

[pbaldy]

Well, I don't see a problem with the posted code, but that isn't where the export is being done. I've used Leban's code, but how are you calling it?

[DB0]

Hmm, okay, I think this is must be where it's calling the Leban's code:

Call ExportRptAsPDF(stDocName, repName)

[pbaldy]

In my copy of Leban's code, that's not a function. I suspect that's a custom function. Can you post the db here?

[DB0]

Here is the VBA portion of the code I am using , hopefully this helps:

Private Sub DoPDF(lFolder As String)

Dim rs As Recordset

Dim today As String
Dim rCount As Long

Dim location As String
Dim retVal As Boolean
Dim repName As String
Dim admName As String
Dim requestedBy As String
Dim fullName As String
Dim objName As String
Dim idx As Integer
Dim stDocName As String
Dim rptSource As String
Dim RS1 As Recordset
Dim RS2 As Recordset

location = lFolder

' MsgBox ("sending output to: " + location)

If location = "" Then
MsgBox ("Please ensure you have selected a writable location")
Exit Sub
Else

If Not testSubDirectories(location) Then
Exit Sub
End If


'Call CreateInheritance

Set RS1 = CurrentDb.OpenRecordset("select count(*) from tbl_verificationObjectFamily ;", dbReadOnly)
If RS1.Fields(0) = "0" Then GoTo DoPDF_NoData

rptSource = "V_SV_Verify_TypeA_Parents"

lblBox.Caption = "Opening " & rptSource

Set rs = CurrentDb.OpenRecordset(rptSource)
rCount = 0

today = Format(Date, "yyyymmdd")

If rs.EOF Then
MsgBox ("Nothing to do - please select an objectSet")
Else


Call InitNotifyValues

rs.MoveFirst
Do While Not rs.EOF

If (IsNull(rs!nameAdm) Or rs!nameAdm = "") Then
MsgBox ("Record " & rs!ReferenceNumber & " has no admin contact. Skipping this record.")
Else
rCount = rCount + 1
lblBox.Caption = rs!ReferenceNumber

admName = Replace(rs!nameAdm, " ", "_")
requestedBy = Replace(rs!requestedBy, " ", "_")
fullName = Replace(rs!svName, " ", "_")
fullName = Replace(fullName, "/", "-")
fullName = Replace(fullName, "\", "-")
fullName = Replace(fullName, ":", "-")
fullName = Replace(fullName, "*", "-")
fullName = Replace(fullName, "?", "-")
fullName = Replace(fullName, "<", "-")
fullName = Replace(fullName, ">", "-")
fullName = Replace(fullName, "|", "-")
'fullName = Replace(fullName, "(", "-")
'fullName = Replace(fullName, ")", "-")
fullName = Replace(fullName, "&", "and")
idx = InStr(fullName, "_~") - 1
If idx > 0 Then
objName = Left(fullName, idx)
Else
idx = InStr(fullName, "~") - 1
If idx > 0 Then
objName = Left(fullName, idx)
Else
objName = fullName
End If
End If


If Not SetNotifyValues(requestedBy, "Service verification reports ready", lFolder & "\" & requestedBy) Then
MsgBox "You will need to add an entry in tbl_NotifyByEmail for " & requestedBy & vbCrLf & _
"and manually send them an email notification for this run.", vbOKOnly
End If


repName = lFolder & "\" & requestedBy & "\" & Left(admName & "_" & rs!ReferenceNumber & "_" & objName, 115)
repName = repName & "_" & today & ".pdf"
lblBox.Caption = "Opening record: " & rs!ReferenceNumber

Forms![frm_Verification_Reports]![refNumBox] = rs!ReferenceNumber
stDocName = "rpt_Verification_" & Left(rs!ReferenceNumber, 2)


'retVal = ConvertReportToPDF(stDocName, , repName)
Call ExportRptAsPDF(stDocName, repName)
End If

rs.MoveNext
Loop


Call SendEMail
'lblBox.Caption = rCount & " records output to " & lFolder & "."

End If
End If

DoPDF_End:
Set RS1 = Nothing
Set RS2 = Nothing
Set rs = Nothing
Exit Sub

DoPDF_Err:
If Err.Number > 0 Then
MsgBox Prompt:=Err.Description, Buttons:=vbCritical & vbOKOnly, _
TITLE:="Error Number " & Err.Number & " Occurred"
Resume DoPDF_End
End If

DoPDF_NoData:
MsgBox ("No object family data - please run View Record List first")
GoTo DoPDF_End

End Sub

[pbaldy]

The first line from your code here is the Lebans original, which is commented out. The second calls a function I don't know about:

'retVal = ConvertReportToPDF(stDocName, , repName)
Call ExportRptAsPDF(stDocName, repName)

Not having seen the custom function, I'll simply point out the difference in the arguments, in case that's the problem.

[DB0]

Sorry, I see now. Looks like I had stopped using Lebans code and am now using something else (below). Everything still works, except when I try to add the combo box and use the IIf statements it doesn't seem to do anything.

Option Compare Database
Option Explicit

Public Function ExportRptAsPDF(ByVal strRpt As String, strSPPath As String) As Boolean
On Error GoTo ErrHandler

ExportRptAsPDF = False
DoCmd.OutputTo acOutputReport, strRpt, "PDFFormat(*.pdf)", strSPPath, False, "", 0, acExportQualityPrint
ExportRptAsPDF = True

ExitSub:
Exit Function
ErrHandler:
MsgBox "Error when attempting to export " & strRpt & vbCrLf & Err.Description & vbCrLf & vbCrLf & strSPPath, vbCritical + vbOKOnly, ExportRptAsPDF
Resume ExitSub
End Function

[pbaldy]

Perhaps because you set one variable then use another?

strDocName
stDocName

unless that's a cut/paste error.

[DB0]

The code above I posted earlier actually works, but then when I change this line

from: stDocName = "rpt_Verification_" & Left(rs!ReferenceNumber, 2)

to:

If Report_Type.Value = 1 Then
strDocName = "report1"
ElseIf Report_Type.Value = 2 Then
strDocName = "report2"
Else
strDocName = "report3"
End If

It stops working and I get the export error. Hmm, any more ideas? I keep playing around with the syntax to see if that helps but I'm driving myself in circles!

[pbaldy]

Did you see post 10? You're using a different variable name. If that turns out to be the problem, this may help in the long run:

http://www.baldyweb.com/OptionExplicit.htm

[DB0]

Thanks, I was wondering about that too. I've corrected/swapped the variable name but it still doesn't work.

Hmm, and there's nothing wrong with how I'm referencing the combo box is there? I.e. "If Report_Type.Value = 1 Then"

I checked to make sure the name matched the combobox name and it is correct. Thanks so much for all your help so far by the way!!

[pbaldy]

I always disambiguate like

Me.ComboName

but it will typically work like you have it. I take it you can't post the db? You can set a breakpoint and check the values:

http://www.baldyweb.com/Debugging.htm