By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Need one command button to do several jobs in Access 2013

P: 10
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
May 27 '17 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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.
May 27 '17 #2

P: 10
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.
May 27 '17 #3

P: 10
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub btnSaveasPDF_Click()
  4.  
  5. End Sub
  6.  
  7. Sub Form_Current()
  8. On Error GoTo Form_Current_Err
  9.  
  10.     If ChildFormIsOpen() Then FilterChildForm
  11.  
  12. Form_Current_Exit:
  13.     Exit Sub
  14.  
  15. Form_Current_Err:
  16.     MsgBox Error$
  17.     Resume Form_Current_Exit
  18.  
  19. End Sub
  20. Sub ToggleLink_Click()
  21. On Error GoTo ToggleLink_Click_Err
  22.  
  23.     If ChildFormIsOpen() Then
  24.         CloseChildForm
  25.     Else
  26.         OpenChildForm
  27.         FilterChildForm
  28.     End If
  29.  
  30. ToggleLink_Click_Exit:
  31.     Exit Sub
  32.  
  33. ToggleLink_Click_Err:
  34.     MsgBox Error$
  35.     Resume ToggleLink_Click_Exit
  36.  
  37. End Sub
  38. Private Sub FilterChildForm()
  39.  
  40.     If Me.NewRecord Then
  41.         Forms![inv amount1].DataEntry = True
  42.     Else
  43.         Forms![inv amount1].Filter = "[Cust ID] = " & Me.[Cust ID]
  44.         Forms![inv amount1].FilterOn = True
  45.     End If
  46.  
  47. End Sub
  48. Private Sub OpenChildForm()
  49.  
  50.     DoCmd.OpenForm "inv amount1"
  51.     If Not Me.[ToggleLink] Then Me![ToggleLink] = True
  52.  
  53. End Sub
  54. Private Sub CloseChildForm()
  55.  
  56.     DoCmd.Close acForm, "inv amount1"
  57.     If Me![ToggleLink] Then Me![ToggleLink] = False
  58.  
  59. End Sub
  60. Private Function ChildFormIsOpen()
  61.  
  62.     ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "inv amount1") And acObjStateOpen) <> False
  63.  
  64. End Function
May 27 '17 #4

P: 10
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
May 27 '17 #5

NeoPa
Expert Mod 15k+
P: 31,419
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. Expand|Select|Wrap|Line Numbers
    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.
May 27 '17 #6

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub CreatePDF_Click()
  5.  
  6.     Dim Filter As String
  7.     Dim PDFPath As String
  8.     Dim PDFFileName As String
  9.     Dim ReportName As String
  10.  
  11.     On Error GoTo CreatePDF_Click_Err
  12.  
  13.     Filter = "MemberID = " & MemberID
  14.  
  15.     ' Save the file anywhere but here it is saved in the same folder as the database with a PDF subfolder
  16.     PDFPath = CurrentProject.Path & "\PDF"
  17.  
  18.     ' Check the PDF folder exists
  19.     If Dir(CurrentProject.Path & "\PDF", vbDirectory) = "" Then         ' Foldeer doesn,t exist
  20.         MsgBox "The folder: " & CurrentProject.Path & "\PDF" & " doesn't exist", vbCritical
  21.         Exit Sub
  22.     End If
  23.  
  24.     ' File name
  25.     PDFFileName = "\" & MemSurName & "_" & MemFirstName & ".PDF"
  26.  
  27.     ReportName = "RptList"
  28.     ' Check the report (Invoice exisits)
  29.     If CurrentProject.AllReports(ReportName).Name = ReportName Then     ' Invoice exists
  30.         ' this will thow an error if the invoice report doesnt exist
  31.     End If
  32.  
  33.     '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.
  34.     DoCmd.OpenReport ReportName, acViewPreview, , Filter
  35.     DoCmd.OutputTo acOutputReport, "", acFormatPDF, PDFPath & PDFFileName, True
  36.  
  37.     '###################################################################################
  38.     '#########  THE ABOVE WILL OVERWRITE AN EXISTING PDF OF THE SAME NAME  #############
  39.     '###################################################################################
  40.  
  41.     'Let's close our previewed report
  42.     DoCmd.Close acReport, ReportName
  43.  
  44. CreatePDF_Click_Exit:
  45.     Exit Sub
  46.  
  47. CreatePDF_Click_Err:
  48.     If Err = 2467 Then
  49.         MsgBox "The Report: " & ReportName & " doesnt exits", vbCritical
  50.         Resume CreatePDF_Click_Exit
  51.     Else
  52.         MsgBox Err & "  " & Err.Description
  53.     End If
  54.  
  55. End Sub
  56.  
Anyway, hope this helps, and come back when you get stuck.
Incidentally, I wish I were as young as you are.

Phil
May 28 '17 #7

P: 10
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>
May 29 '17 #8

P: 10
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
May 29 '17 #9

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. PDFPath = CurrentProject.Path & "\PDF"
  2.  
to
Expand|Select|Wrap|Line Numbers
  1. PDFPath = "C:\Users\Jan\Desktop\Temporary"
  2.  
  3.     ' Check the PDF folder exists
  4.     If Dir(PDFPath, vbDirectory) = "" Then   ' Folder doesn't exist
  5.         MsgBox "The folder: " & PDFPath & " doesn't exist", vbCritical
  6.         Exit Sub
  7.     End If
  8.  
I assumed you would adapt my code to suit.

The Filter should be something like
Expand|Select|Wrap|Line Numbers
  1.     Filter = "InvoiceID = " & InvoiceID
  2.  
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
May 29 '17 #10

P: 10
Ok I get it I will try it thank you
May 30 '17 #11

P: 10
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
May 30 '17 #12

P: 10
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.?
May 30 '17 #13

NeoPa
Expert Mod 15k+
P: 31,419
  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.
May 30 '17 #14

PhilOfWalton
Expert 100+
P: 1,430
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
May 30 '17 #15

P: 10
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
May 31 '17 #16

Post your reply

Sign in to post your reply or Sign up for a free account.