Need one command button to do several jobs in Access 2013

[jemy]

I have a built a database for backflow testing. The database contains 3 tables: Client Information, Testing Date and hyperlinks to actual document and Invoice information.

I have built the relationships with the tables around the client ID.

The user can select the client and click on a "Open Client Information" command button.

From the client information form I have created a command button that will open the invoice form and insert the information from the selected client form.

From the invoice form I have created a command button that saves the invoice as a pdf.

This is what I am hoping you can help with: I want the Save as pdf command button to do several tasks.

1: save as pdf, 2: save it to a temporary folder on desktop, 3: save it as file name based on FirstName, Lastname fields, and lastly open up aol email so that it can be sent to client.

I have accomplished saving it as a pdf and I entered the output file information. But I get an error saying I cannot save it while the file is open. If I delete the Output file information it allows me to save it as a pdf but I have to choose location.

I know I am asking a lot of you, but I hope you can help me

[NeoPa]

Hi Vemy.

It may turn out to be a lot overall, but let's start at the beginning and see what we struggle with and where.

I think your first (next) step is to show us what you have so far in the way of the code you're using to create and save the PDF. Please remember, when posting code, to consider all the items in Before Posting (VBA or SQL) Code.

With the code please also include information as to what goes wrong at which point in the code. If the code's posted properly in the [CODE] tags then it will also have line numbers which you can use to identify what happens where.

[jemy]

Ok at the risk of having you turn away from me because I am 65 and apparently one starts losing brain cells at 27...who knows how many I have left...I must tell you I did not do a code. I used the command button function to create at macro that would save it as a pdf. It worked but when I wanted to save it to a temporary folder on the desk top I kept getting the same error. With the command button I chose "Exportwithformatting" the object type was a form, the object name was backflow certification, and the output format was pdf.

All of this worked. but in the macro there is also a line that is "output file" which allows you to tell it where you want to store the pdf. Everytime I put in this line..and then tried to run the macro I got a msg box, telling me it did not work for one of several reasons...I did not have permission...the file was open...etc.
Is that enough to help you help me I can change it from an "embedded macro to an event procedure and give you that code and then retype that out..but please remember, when I went to college at 18 computers were just born...the class I took on coding was hinged upon showing if you saved a penny a day compounded what it would be in ten years.

[jemy]

Option Compare Database
 
Private Sub btnSaveasPDF_Click()
 
End Sub
 
Sub Form_Current()
On Error GoTo Form_Current_Err
 
    If ChildFormIsOpen() Then FilterChildForm
 
Form_Current_Exit:
    Exit Sub
 
Form_Current_Err:
    MsgBox Error$
    Resume Form_Current_Exit
 
End Sub
Sub ToggleLink_Click()
On Error GoTo ToggleLink_Click_Err
 
    If ChildFormIsOpen() Then
        CloseChildForm
    Else
        OpenChildForm
        FilterChildForm
    End If
 
ToggleLink_Click_Exit:
    Exit Sub
 
ToggleLink_Click_Err:
    MsgBox Error$
    Resume ToggleLink_Click_Exit
 
End Sub
Private Sub FilterChildForm()
 
    If Me.NewRecord Then
        Forms![inv amount1].DataEntry = True
    Else
        Forms![inv amount1].Filter = "[Cust ID] = " & Me.[Cust ID]
        Forms![inv amount1].FilterOn = True
    End If
 
End Sub
Private Sub OpenChildForm()
 
    DoCmd.OpenForm "inv amount1"
    If Not Me.[ToggleLink] Then Me![ToggleLink] = True
 
End Sub
Private Sub CloseChildForm()
 
    DoCmd.Close acForm, "inv amount1"
    If Me![ToggleLink] Then Me![ToggleLink] = False
 
End Sub
Private Function ChildFormIsOpen()
 
    ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "inv amount1") And acObjStateOpen) <> False
 
End Function

[jemy]

The code I copied and put in is the code to change the invoice into a pdf. But honestly I copied it by changing the macro into an event procedure and it did not include my attempts to save it to a particular folder

[NeoPa]

Hi Jemy.

If I don't manage to help you it'll be because I don't have the requisite experience rather than lack of interest.

Two points to start with, from the linked article :
Always use the [CODE] tags when posting code.
Always use Option Explicit - as explained in more detail in Require Variable Declaration.

These two points will make your life easier if you continue to take advantage of the services we offer.

Now, let's start on the code. You haven't said what happens where, and we may need to get to that later, but I expect you're still a little out of your depth with it so far.

1. Private Sub btnSaveasPDF_Click()

   This seems to be where you expect the code to do its thing, but it's empty. There's no code in there.
2. There seems to be a whole bunch of code used to manage a linked form, which I imagine is irrelevant to what we're looking at.

It turns out that there isn't really any relevant code in what you've posted. Certainly nothing I can see that pertains to outputting something to PDF format.

[PhilOfWalton]

OK Jemy, lets try to get you started.

Firstly, on top of any module, you need
Option Compare Database
Option Explicit

The Option Explicit ensures that when you refer to something in your code, that "thing" exists. It could be something you have defined in your code with a Dim Statement or a control on your form.

In the example, I have a simple continuous form with people's Surname & Firstname, I select a person, click the button and print the report.

So the form looks like this:

Then you need the code

Option Compare Database
Option Explicit
 
Private Sub CreatePDF_Click()
 
    Dim Filter As String
    Dim PDFPath As String
    Dim PDFFileName As String
    Dim ReportName As String
 
    On Error GoTo CreatePDF_Click_Err
 
    Filter = "MemberID = " & MemberID
 
    ' Save the file anywhere but here it is saved in the same folder as the database with a PDF subfolder
    PDFPath = CurrentProject.Path & "\PDF"
 
    ' Check the PDF folder exists
    If Dir(CurrentProject.Path & "\PDF", vbDirectory) = "" Then         ' Foldeer doesn,t exist
        MsgBox "The folder: " & CurrentProject.Path & "\PDF" & " doesn't exist", vbCritical
        Exit Sub
    End If
 
    ' File name
    PDFFileName = "\" & MemSurName & "_" & MemFirstName & ".PDF"
 
    ReportName = "RptList"
    ' Check the report (Invoice exisits)
    If CurrentProject.AllReports(ReportName).Name = ReportName Then     ' Invoice exists
        ' this will thow an error if the invoice report doesnt exist
    End If
 
    'Let's print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
    DoCmd.OpenReport ReportName, acViewPreview, , Filter
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, PDFPath & PDFFileName, True
 
    '###################################################################################
    '#########  THE ABOVE WILL OVERWRITE AN EXISTING PDF OF THE SAME NAME  #############
    '###################################################################################
 
    'Let's close our previewed report
    DoCmd.Close acReport, ReportName
 
CreatePDF_Click_Exit:
    Exit Sub
 
CreatePDF_Click_Err:
    If Err = 2467 Then
        MsgBox "The Report: " & ReportName & " doesnt exits", vbCritical
        Resume CreatePDF_Click_Exit
    Else
        MsgBox Err & "  " & Err.Description
    End If
 
End Sub
 

Anyway, hope this helps, and come back when you get stuck.
Incidentally, I wish I were as young as you are.

Phil

[jemy]

Phil I appreciate your help. I am certain I was not clear and sent the code for the wrong thing.

Exportwithformating

Object Type: Form

Object Name: Backflow Certification

Output format: PDF format (*)


All of these worked. but the next fields was"

Output File:

This field would allow me to tell where I want to put the file

I looked on line and found samples and this is what I entered
in that field
"C:\Users\Jan\Desktop\Temporary"

In trying to do this part I want not worried about naming the file...I just wanted to have it put in the correct folder without me having to click several times to get to the folder I needed.

But when ever I tried to do this I kept getting the same error message.....
I could not save it while file was open I did not have permission etc etc.

Now if I took that Output file off...The PDF would still export and open a box were I had to select where I wanted to store it and rename it.

This is not a huge issue but every step I can save the friend I am building the database for will be one thing easier for him.

So if you have the ability to use a command button and do a marco with Exportingwith Formatting: Can you look at it and tell me why I keep getting errors?

I know my path name is correct..I checked it many times....I know I needed quotation marks at beginning and end so it would act as a string.

The emailing part I can leave off for now, as once it is put in the folder as a pdf I can easily email it from there. When I copy the marco this is the code that comes up


<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro For="Save as PDF" Event="OnClick"><Statements><Action Name="ExportWithFormatting"><Argument Name="ObjectType">Form</Argument><Argument Name="ObjectName">Backflow Certification Invoice</Argument><Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument><Argument Name="OutputFile">"C:\Users\Jan\Desktop\Temporary" </Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

[jemy]

I am sorry to post this here, as it is not so much a question but a solution to a problem many user are having:

The problem stems from needing a text box and then changing it into a calculated control that allows you to enter a formula to get totals etc.

For two days I worked on this issue and read every web site I could find about it. From the numerous posts I new I was not alone with the issue and like other tried every suggestion I found, but it still did not work.

The text box remained empty or came up with error.
The first thing I discovered is that any other textbox related to the formula, cannot be null. There has to be something in it a default value of 0 worked. But again my calculated text box worked once and then not again.

I finally found a user with the same problem who stated out of desperation, she made a copy of the form with the calculated controls, and all of sudden they worked.

I know it is not code driven or scientific, but I tried it and not only did my calculated control boxes work on the copy...they also started working on the original.

So I post there here as a possible solution in case anyone has a similar question

[PhilOfWalton]

Hi Jemy

I think you are confusing things with your second post. I shall ignore it for the moment.

The code I gave you was to create a straightforward PDF file in a folder of your choice but you can change the line

PDFPath = CurrentProject.Path & "\PDF"
 

to

PDFPath = "C:\Users\Jan\Desktop\Temporary"
 
    ' Check the PDF folder exists
    If Dir(PDFPath, vbDirectory) = "" Then   ' Folder doesn't exist
        MsgBox "The folder: " & PDFPath & " doesn't exist", vbCritical
        Exit Sub
    End If
 

I assumed you would adapt my code to suit.

The Filter should be something like

    Filter = "InvoiceID = " & InvoiceID
 

The PDF versiion of the invoice is obviously based on this filter.

I am assuming that somewhere on your form that you use to select the invoice, that the first name and last name exist.

IMO the XML stuff is totally irrelevant unless you are trying to insert the invoice into the email rather than attaching the PDF file.

This code, customised to suit your tables should give a named PDF file in whatever folder you want.

Depending on the setting of the last parameter in the DoCmd.OutputTo acOutputReport statement, True will open the PDF file once it is created, False will not open it

Phil

[jemy]

Ok I get it I will try it thank you

[jemy]

Just so you know, I wont try it until tomorrow at about 4 am my time. That is because I have been working on other aspects of the database all day and need to clear my brain so I can concentrate on getting the code you sent correct. I will let you know directly after if it works. And again thank you so much. I have been reading it over and over so I can understand...thank you

[jemy]

I am going to enter the code you gave me this morning but I hope you will clarify a few things.

1) There is a line in the code that says Filter = "memberID" etc

2) Why is this necessary in that I am using this code only on a form that is currently open.?

[NeoPa]

1. Line #13 sets a string variable up as a filter.
2. Line #34 passes that string variable as a parameter when opening the report.

[PhilOfWalton]

Just to clarify your second question, think of the recordsource of your form as a big excel sheet with 1 client per line. Think of a form as a piece of cardboard with horizontal slots in it, the same height as the rows in your Excel sheet. By moving your piece of cardboard up and down, you select the record you want. The slots in the cardboard reveal the fields you want to see.

So basically the filter "ClientID = " & ClientID is saying which record in this enormous spreadsheet is the slot in my bit ofr cardboard positioned over.

Phil

[jemy]

Thank you, please know if there is a delay in replying it is because I am on a time constraint with one week to build a lot of complicated forms and reports. I have dug out my old copy of "Access Bible" and begun to retrain myself on coding but often I feel like as soon as I go to sleep, the knowledge falls out of my ears.

Anyway, I will try the code again tomorrow/ I do appreciate your time and help