VBA Report Module Auto populate "save as" name (1 Viewer)

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
Hi all,


I need help with the following. I have a number of forms that have several fields, 2 of these specific forms are used for a report, report and subreport. I need the file dialog box for exporting the report as a pdf to auto-populate the report name based on 2 fields on these forms. so far I have managed to export the report into the pdf without auto-populating the desired file name. Whenever I use my code below it auto-populates the name as I want it to but the code throws an error and because of my error handler code it closes the database.

Please be advised that my code is not pretty, I graduated from Google and been able to get this far by doing a lot of searching.

first code is the one that auto-populates the name but throws a code and does not export as a pdf, while also closing the database

second code is the one that does export and throws no errors, but does not auto-populate the file name when exporting

Code:
Private Sub Command22_Click()
 
  Dim fileName As String, strFullpath As String
  Dim answer As Integer
  Dim fileName2 As String
  Dim strFile As String
  Dim intPos As Integer
  Dim strRptName As String
  
    On Error GoTo error1
    DoCmd.OpenForm "Specific_Information_frm"
    'filename for PDF file*
    strRptName = Forms![Specific_Information_frm]![WBS Number] & " " & "Ultrasonic Report"
    
    strFullpath = BrowseFile


    If strFullpath <> "" Then
        ' get folder and file names
        intPos = InStrRev(strFullpath, "\")
        strFolder = Left(strFullpath, intPos - 1)
        strFile = Mid(strFullpath, intPos + 1)
  'folder path where pdf file will be saved *
 
 
    End If
  DoCmd.OutputTo objecttype:=acOutputReport, objectName:=strRptName, outputformat:=acFormatPDF, outputFile:=strFullpath
  DoCmd.Close acForm, "Specific_Information_frm"
  DoCmd.Close acReport, "C-Scan_rpt"
    MsgBox prompt:="PDF File succesfully exported to desired location ", buttons:=vbInformation, Title:="Report Exported as PDF"
  Exit Sub
 
error1:
    MsgBox prompt:="An Error has occurred. Closing Database Now", buttons:=vbCritical
    DoCmd.Quit
 
End Sub




Code:
Private Sub Command22_Click()
 
  Dim fileName As String, strFullpath As String
  Dim answer As Integer
  Dim fileName2 As String
  Dim strFile As String
  Dim intPos As Integer
 
    On Error GoTo error1
    DoCmd.OpenForm "Specific_Information_frm"
  'filename for PDF file*
  fileName = "C-Scan Report"
  fileName2 = Forms![Specific_Information_frm]![WBS Number]
   strFullpath = BrowseFile


    If strFullpath <> "" Then
        ' get folder and file names
        intPos = InStrRev(strFullpath, "\")
        strFolder = Left(strFullpath, intPos - 1)
        strFile = Mid(strFullpath, intPos + 1)
  'folder path where pdf file will be saved *
 
 
    End If
  DoCmd.OutputTo objecttype:=acOutputReport, objectName:=Me.Name, outputformat:=acFormatPDF, outputFile:=strFullpath
  DoCmd.Close acForm, "Specific_Information_frm"
  DoCmd.Close acReport, "C-Scan_rpt"
    MsgBox prompt:="PDF File succesfully exported to desired location ", buttons:=vbInformation, Title:="Report Exported as PDF"
  Exit Sub
 
error1:
    MsgBox prompt:="An Error has occurred. Closing Database Now", buttons:=vbCritical
    DoCmd.Quit
 
End Sub
 

cheekybuddha

AWF VIP
Local time
Today, 13:50
Joined
Jul 21, 2014
Messages
2,237
DoCmd.Quit is a bit harsh on an error!

I think you are mixing up your arguments (but I am not totally sure!)

objectName should be the name of the report which I guess is "C-Scan_rpt"

outputFile should include the name of the file you wish to create, not just the path where you wish to save it.

What happens if you try:
Code:
Private Sub Command22_Click()

  Dim fileName As String, strFullpath As String
  Dim answer As Integer
  Dim reportName As String
  Dim strFile As String
  Dim intPos As Integer

  On Error GoTo error1
  DoCmd.OpenForm "Specific_Information_frm"
  strFullpath = BrowseFile
  If strFullpath <> "" Then
    reportName = "C-Scan_rpt"
    DoCmd.OutputTo objecttype:=acOutputReport, objectName:=reportName, outputformat:=acFormatPDF, outputFile:=strFullpath
    DoCmd.Close acReport, "C-Scan_rpt"
    MsgBox prompt:="PDF File succesfully exported to desired location ", buttons:=vbInformation, Title:="Report Exported as PDF"
  End If
  DoCmd.Close acForm, "Specific_Information_frm"
  Exit Sub

error1:
    MsgBox prompt:="An Error has occurred. Closing Database Now", buttons:=vbCritical
'    DoCmd.Quit

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
Well to start with.... what is the error.?

Comment out the Quit (that is a bit over the top for an error).
Walk though the code with the debugger. Set a breakpoint and walk through with F8.
Where is the button.? Is the report actuall the same name as the form?
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
DoCmd.Quit is a bit harsh on an error!

I think you are mixing up your arguments (but I am not totally sure!)

objectName should be the name of the report which I guess is "C-Scan_rpt"

outputFile should include the name of the file you wish to create, not just the path where you wish to save it.

What happens if you try:
Code:
Private Sub Command22_Click()

  Dim fileName As String, strFullpath As String
  Dim answer As Integer
  Dim reportName As String
  Dim strFile As String
  Dim intPos As Integer

  On Error GoTo error1
  DoCmd.OpenForm "Specific_Information_frm"
  strFullpath = BrowseFile
  If strFullpath <> "" Then
    reportName = "C-Scan_rpt"
    DoCmd.OutputTo objecttype:=acOutputReport, objectName:=reportName, outputformat:=acFormatPDF, outputFile:=strFullpath
    DoCmd.Close acReport, "C-Scan_rpt"
    MsgBox prompt:="PDF File succesfully exported to desired location ", buttons:=vbInformation, Title:="Report Exported as PDF"
  End If
  DoCmd.Close acForm, "Specific_Information_frm"
  Exit Sub

error1:
    MsgBox prompt:="An Error has occurred. Closing Database Now", buttons:=vbCritical
'    DoCmd.Quit

End Sub



buddha- this does not do anything but close my forms... don't know what is really going on....
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
Well to start with.... what is the error.?

Comment out the Quit (that is a bit over the top for an error).
Walk though the code with the debugger. Set a breakpoint and walk through with F8.
Where is the button.? Is the report actuall the same name as the form?


gasman- don't know where to start... don't know how to see what the actual error is.. I just know the database closes... I don't know how to do any of the things you mentioned. the export button sits on the "report header" section of the report. Like I mentioned on my original post.. I have 2 forms "C-scan_frm, and Specific_information_frm that this report is using... I created the report using my Specific info form and used a subreport for the c-scan form, as this will grow depending on number of inputs
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
Start with little steps.

What is BrowseFile meant to return.? A file name or a folder.?
After Error1: add

Code:
MsgBox Err.Number & " : " & Err.Description

https://excelmacromastery.com/vba-error-handling/

Best to start learning debugging as well, as it will help enormously.
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
Start with little steps.

What is BrowseFile meant to return.? A file name or a folder.?
After Error1: add

Code:
MsgBox Err.Number & " : " & Err.Description

https://excelmacromastery.com/vba-error-handling/

Best to start learning debugging as well, as it will help enormously.

this is what I am getting now
1589378079774.png
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
Start with little steps.

What is BrowseFile meant to return.? A file name or a folder.?
After Error1: add

Code:
MsgBox Err.Number & " : " & Err.Description

https://excelmacromastery.com/vba-error-handling/

Best to start learning debugging as well, as it will help enormously.


and now that you brought it up.. I don't know what the browsefile is supposed to return... lol must of been a leftover of something I tried to fix this issue
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
Ok, that is a start.
What is the name of the report? first it was "C-Scan Report" now "C-Scan_rpt" You have to get the names correct?

What code are you actually using now?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
and now that you brought it up.. I don't know what the browsefile is supposed to return... lol must of been a leftover of something I tried to fix this issue
Right. You really need to get some basics of debugging, else this could go on and on for ever.
Review some of these https://www.youtube.com/results?search_query=access+vba+debugging+ They can only help.

For now hardcode a full path name for the file like "C:\Temp\Testfile.pdf"
Get the report name correct first.
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
Ok, that is a start.
What is the name of the report? first it was "C-Scan Report" now "C-Scan_rpt" You have to get the names correct?

What code are you actually using now?


Code:
Private Sub Command22_Click()
 
  Dim fileName As String, strFullpath As String
  Dim answer As Integer
  Dim fileName2 As String
  Dim strFile As String
  Dim intPos As Integer
  Dim strRptName As String
 
    On Error GoTo error1
    DoCmd.OpenForm "Specific_Information_frm"
    'filename for PDF file*
    strRptName = Forms![Specific_Information_frm]![WBS Number] & " " & "Ultrasonic Report"
    
    If strFullpath <> "" Then
        ' get folder and file names
        intPos = InStrRev(strFullpath, "\")
        strFolder = Left(strFullpath, intPos - 1)
        strFile = Mid(strFullpath, intPos + 1)
  'folder path where pdf file will be saved *
 
 
    End If
  DoCmd.OutputTo objecttype:=acOutputReport, objectName:=strRptName, outputformat:=acFormatPDF, outputFile:=strFullpath
  DoCmd.Close acForm, "Specific_Information_frm"
  DoCmd.Close acReport, "C-Scan_rpt"
    MsgBox prompt:="PDF File succesfully exported to desired location ", buttons:=vbInformation, Title:="Report Exported as PDF"
  Exit Sub
 
error1:
    MsgBox Err.Number & " : " & Err.Description
 
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
And what would be a likely value for strRptName ? "2456 Ultrasonic Report" ?
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
gasman-

so when the code has the "strFullpath = Browsefile" argument I get error # 2059
if I remove it I get error # 3021 doing a little digging I think I need that argument in there
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
OK, comment out the browsfile code and just use
Code:
DoCmd.OutputTo objecttype:=acOutputReport, objectName:=strRptName, outputformat:=acFormatPDF

Click to the left of that line and a red dot should be shown. Press your button and then go to the code. Now hover over strRptName and see what it has. If it has a value then press F5. See what is produced. You will be prompted for a location, default file name will be that of the report.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
strRptName should be a mix of 2 fields.... just like you have it there
However I cannot see you having a report for each WBS Number?, that does not make any sense.?
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
OK, comment out the browsfile code and just use
Code:
DoCmd.OutputTo objecttype:=acOutputReport, objectName:=strRptName, outputformat:=acFormatPDF

Click to the left of that line and a red dot should be shown. Press your button and then go to the code. Now hover over strRptName and see what it has. If it has a value then press F5. See what is produced. You will be prompted for a location, default file name will be that of the report.


I did all that everything happened just like you said... after I pressed F5 and the file name was what I was looking for "the mix of the 2 fields" I clicked ok and got that same 2059 error
 

alekkz

Registered User.
Local time
Today, 06:50
Joined
Oct 14, 2016
Messages
27
However I cannot see you having a report for each WBS Number?, that does not make any sense.?


so a little background... the database will be used to track and report defects found while inspecting aerospace parts... this database needs to be controlled document per "Quality policies" the reason why we need to break it up into each WBS # having its own report is because each part can have up to 100s of defects with each defect having several pictures taken of it.. "and saved on the database, I know we shouldn't save pics to databases" but for our purpose its easier to have a master database and have each part we produce use the master database and produce a report specifically for that part and that part only... when a new part comes a long... we would just open this 'master database" which would be empty and fill out the info needed.

hopefully this makes sense
 

cheekybuddha

AWF VIP
Local time
Today, 13:50
Joined
Jul 21, 2014
Messages
2,237
It seems as if "the mix of the 2 fields" is the name of the file that you want the report to be saved as.

But the objectName argument needs to be the name of the report as you see it in the list of reports in your object tree.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:50
Joined
Sep 21, 2011
Messages
14,041
Yes, that is the way I would expect it to work, but now you are talking about a filename NOT the report.?
The reportname should stay the same and the filename should change depending on what is in those two fields.

You really need to take a break and look at a few of those videos to get an idea as to how it is meant to work.?
Then you will be in a better position to take this on.

It's a bit like asking me to enter into a F1 race, just because I have a driving licence. :)
 

Users who are viewing this thread

Top Bottom