Export from MS access to PDF with set variables from a the opened form

[iLott]

Hi

I have been at this for almost 3 weeks now and I'm having great difficulties trying to get this right and working. First let me explain what I am doing and what I am trying to do.

Firstly:
I am making a Maintenance database using MS access software, what I have are tables, forms and no queries or report existing so far. The forms and tables all work correctly.

After the form (Job request) has been completed I need to save/export into PDF so I am able to hyperlink it against its existing asset card elsewhere.

now what I have done is Created button, on event "on click" I have tried to go to macro builder

ExportWithFormatting
Objet Type: Form
Object Name: FrmMachineFault/GenMaint
Output format: PDF
Output file:
Auto Start: No
Template File:
Encoding:
Output Quality: Print

when I click this button it opens to save to and it works perfectly but I have to manually type the file name when it gets to the Save to section.

What I want it to do is define the name by the existing fields in the open form. Example Closed date, Effected area, Asset and title.

What I am hoping for is something like this.

10/07/15_Foundry_Furnace_LPG_Leak
(Closed date)_(Effected Area)_(Asset)_(Tittle)

Is this possible?

 

[vbaInet]

Are you really sure that you want to use all those fields as a file name? It would be rather long.

You first need to get rid of characters that a file name cannot contain, which are:
\ / : * ? " < > |

Then concatenate the fields to form the file name.

 

[iLott]

Hi

At this point in time all those fields are needed to ensure that they are unique.
However in the future this may be subject to change.

How do I get rid of \ / when I select the [closed date] it automatically generates the date 10/7/15

Edit: Ok I have just re-tested it and when I try =[Closed Date] the results is this 10_07_15

so the final result should look like this


10_07_15_Foundry_Furnace_LPG_Leak
(Closed date)_(Effected Area)_(Asset)_(Title)

The original coding I used is this.

=[Closed date] & "_" & [Effected area] & "_" & [Asset] & "_" & [Title]

and

=[Closed date] & "_" & [Effected area] & "_" & [Asset] & "_" & [Title] & ".PDF"

These both resulted in a failure.

 

[vbaInet]

Because one of them contains an invalid file name character. Double-check it.

Do some research, you should find something that can be used to remove the invalid file name characters.

 

[iLott]

Apologies, I am very new to VB script and MS access.

In these fields [Closed Date], [Effected Area], [Asset] & [Title]

the only one I see that has any illegal letter would be in the closed date field,

Currently in the form the closed date field is shown as 10/07/15.

Would this cause what you are talking about making the file name Invalid?

Even though it automatically changes the / into a _?

 

[vbaInet]

You've not applied any code to change "/" to "_". That's why I mentioned that you should scour the Internet for code that "removes invalid characters from file name + vba". It's been answered several so you should find something. If you're struggling to piece it together show me the code.

 

[vbaInet]

Looks like you're not getting far with your search? I've written a small function:

Public Function StripInvalidChars(TheString As String) As String
    Dim varChars As Variant
    Dim x        As Integer
    
    Const INVALID_NAME_CHARS As String = "\ / : * ? "" < > |"
    
    varChars = Split(INVALID_NAME_CHARS, " ")
    
    For x = LBound(varChars) To UBound(varChars)
        TheString = Replace(TheString, varChars(x), "")
    Next
    
    StripInvalid = TheString
End Function

Create a Module and place the above in there.

To apply:

=Format([Closed date], "dd_mm_yy") & "_" & StripInvalidChars([Effected area] & "_" & [Asset] & "_" & [Title]) & ".PDF"

 

[iLott]

Looks like you're not getting far with your search? I've written a small function:

Public Function StripInvalidChars(TheString As String) As String
    Dim varChars As Variant
    Dim x        As Integer
 
    Const INVALID_NAME_CHARS As String = "\ / : * ? "" < > |"
 
    varChars = Split(INVALID_NAME_CHARS, " ")
 
    For x = LBound(varChars) To UBound(varChars)
        TheString = Replace(TheString, varChars(x), "")
    Next
 
    StripInvalid = TheString
End Function

Create a Module and place the above in there.

To apply:

=Format([Closed date], "dd_mm_yy") & "_" & StripInvalidChars([Effected area] & "_" & [Asset] & "_" & [Title]) & ".PDF"

Apologies, I will try what you have suggested ASAP, I have been crook the last few days and haven't been able to reply to your message nor try or research what you had suggested.

 

[vbaInet]

Let us know how it goes.

 

[iLott]

Its not liking me, several errors come up when I try to type the format.

its not liking the ( ) and the ,

When I tried the line without it the whole line goes yellow.

 

[vbaInet]

What whole line? Be more descriptive.

 

[iLott]

Appologies, this line

=Format([Closed date], "dd_mm_yy") & "_" & StripInvalidChars([Effected area] & "_" & [Asset] & "_" & [Title]) & ".PDF"

 

[vbaInet]

Where's it being used?

 

[iLott]

I have tried a couple of forms for try and get this to work as per below

Attempt 1

Public Function StripInvalidChars(TheString As String) As String
Dim varChars As Variant
Dim x As Integer

Const INVALID_NAME_CHARS As String = "\ / : * ? "" < > |"

varChars = Split(INVALID_NAME_CHARS, " ")

For x = LBound(varChars) To UBound(varChars)
TheString = Replace(TheString, varChars(x), "")
Next

StripInvalid = TheString
End Function

Private Sub Save_to_PDF_Click()

DoCmd.outputto acoutputform, FrmMachineFault_GenMaint, acFormatPDF, Z:\(P) Maintenance\Planned Maintenance\Jobs\& " [Closed date], "dd_mm_yy") & "_" & StripInvalidChars([Effected area] & "_" & [Asset] & "_" & [Title]) & ".PDF", False

End Sub

2nd Attempt

Public Function StripInvalidChars(TheString As String) As String
Dim varChars As Variant
Dim x As Integer
Const INVALID_NAME_CHARS As String = "\ / : * ? "" < > |"
varChars = Split(INVALID_NAME_CHARS, " ")

For x = LBound(varChars) To UBound(varChars)
TheString = Replace(TheString, varChars(x), "")
Next

StripInvalid = TheString
End Function
Private Sub Save_to_PDF_Click()
Dim myPath As String
Dim myFormName As String
DoCmd.OpenForm "FrmMachineFault_GenMaint", acNormal
myPath = "Z:\(P) Maintenance\Planned Maintenance\Jobs"
myFormName = “[Closed date], "dd_mm_yy") & "_" & StripInvalidChars([Effected area] & "_" & [Asset] & "_" & [Title]) & ".PDF"
DoCmd.OutputTo acOutputForm, "", acFormatPDF, "myPath & myFormName, False"
End Sub

 

[vbaInet]

In the future, always mention all of the following:
* the full error message
* which line it highlights
* and where the code was placed.

Private Sub Save_to_PDF_Click()
    Dim myPath As String
    Dim myFormName As String
    
    myPath = "Z:\(P) Maintenance\Hamilton Jet Planned Maintenance\Jobs\"
    myPath = myPath & Format(Me.[Closed date], "dd_mm_yy")
    myPath = myPath & "_" & StripInvalidChars(Me.[Effected area] & "_" & Me.[Asset] & "_" & Me.[Title]) & ".PDF"
    
    DoCmd.OutputTo acOutputForm, "FrmMachineFault_GenMaint", acFormatPDF, myPath, False
End Sub

... by the way, reports are better for printing.

 

[iLott]

This is not a Report, its a form of job request sheet. The reason why I didn't use a print function was because the only time a hard copy is needed is when I photo copy it into pdf so then I can log it in its asset card.

To prevent all that I thought maybe it would be best to just save to PDF off the bat.

I am extremely thankful for your help and patience by the way.

 

[vbaInet]

You're printing to a pdf, hence the word "print". One doesn't only print to a printer.

Whether you print to a pdf or print to a printer, a report is best placed for this type of job. Have you tested the code I sent?

 

[iLott]

I have just tested the code, the file saves into PDF with no error,
however the file name is only showing the date.

The result is.

09_07_15_

 

[vbaInet]

It would indicate that none of those textboxes are returning values. And I hope you didn't remove the "Me." reference that I put in there?

 

[iLott]

Ok I have got it to work exactly how I want it. I didn't remove the Me. and did not make any alteration to get that result.

however I did make a slight tweak and it seems to work. this is the end result of the code you have given me.

Private Sub Save_to_PDF_Click()
Dim myPath As String
Dim myFormName As String

myPath = "Z:\(P) Maintenance\Planned Maintenance\Jobs\"
myPath = myPath & Format(Me.[Closed Date], "dd_mm_yy")
myPath = myPath & "_" & (Me.[Effected area] & "_" & Me.[Asset] & "_" & Me.[Title]) & ".PDF"

DoCmd.OutputTo acOutputForm, "FrmMachineFault_GenMaint", acFormatPDF, myPath, False
End Sub


Thank you 1000 times for your patience and assistance.