472,959 Members | 1,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,959 software developers and data experts.

Need one command button to do several jobs in Access 2013

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
15 1220
32,547 Expert Mod 16PB
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
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. Private Sub btnSaveasPDF_Click()
  5. End Sub
  7. Sub Form_Current()
  8. On Error GoTo Form_Current_Err
  10.     If ChildFormIsOpen() Then FilterChildForm
  12. Form_Current_Exit:
  13.     Exit Sub
  15. Form_Current_Err:
  16.     MsgBox Error$
  17.     Resume Form_Current_Exit
  19. End Sub
  20. Sub ToggleLink_Click()
  21. On Error GoTo ToggleLink_Click_Err
  23.     If ChildFormIsOpen() Then
  24.         CloseChildForm
  25.     Else
  26.         OpenChildForm
  27.         FilterChildForm
  28.     End If
  30. ToggleLink_Click_Exit:
  31.     Exit Sub
  33. ToggleLink_Click_Err:
  34.     MsgBox Error$
  35.     Resume ToggleLink_Click_Exit
  37. End Sub
  38. Private Sub FilterChildForm()
  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
  47. End Sub
  48. Private Sub OpenChildForm()
  50.     DoCmd.OpenForm "inv amount1"
  51.     If Not Me.[ToggleLink] Then Me![ToggleLink] = True
  53. End Sub
  54. Private Sub CloseChildForm()
  56.     DoCmd.Close acForm, "inv amount1"
  57.     If Me![ToggleLink] Then Me![ToggleLink] = False
  59. End Sub
  60. Private Function ChildFormIsOpen()
  62.     ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "inv amount1") And acObjStateOpen) <> False
  64. End Function
May 27 '17 #4
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
32,547 Expert Mod 16PB
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
1,430 Expert 1GB
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
  4. Private Sub CreatePDF_Click()
  6.     Dim Filter As String
  7.     Dim PDFPath As String
  8.     Dim PDFFileName As String
  9.     Dim ReportName As String
  11.     On Error GoTo CreatePDF_Click_Err
  13.     Filter = "MemberID = " & MemberID
  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"
  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
  24.     ' File name
  25.     PDFFileName = "\" & MemSurName & "_" & MemFirstName & ".PDF"
  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
  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
  37.     '###################################################################################
  38.     '#########  THE ABOVE WILL OVERWRITE AN EXISTING PDF OF THE SAME NAME  #############
  39.     '###################################################################################
  41.     'Let's close our previewed report
  42.     DoCmd.Close acReport, ReportName
  44. CreatePDF_Click_Exit:
  45.     Exit Sub
  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
  55. End Sub
Anyway, hope this helps, and come back when you get stuck.
Incidentally, I wish I were as young as you are.

May 28 '17 #7
Phil I appreciate your help. I am certain I was not clear and sent the code for the wrong thing.


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

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
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
1,430 Expert 1GB
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"
Expand|Select|Wrap|Line Numbers
  1. PDFPath = "C:\Users\Jan\Desktop\Temporary"
  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
I assumed you would adapt my code to suit.

The Filter should be something like
Expand|Select|Wrap|Line Numbers
  1.     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

May 29 '17 #10
Ok I get it I will try it thank you
May 30 '17 #11
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
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
32,547 Expert Mod 16PB
  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
1,430 Expert 1GB
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.

May 30 '17 #15
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

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

Similar topics

by: Jillian Calderon | last post by:
I put a command button on the form and directed it the pivot table I created. I saved it and opened the form, but the command button doesn't lead to the pivot table. How can I get the command...
by: Jillian Calderon | last post by:
I put a command button on the form and directed it to the pivot table I created. I saved it and opened the form, but the command button doesn't lead to the pivot table. How can I get the...
by: Michael Shaw | last post by:
I am just starting out trying to create my first data access page (DAP) using Access 2000. I have created a default index.html page plus one other "Members" page. I have placed a command button...
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
by: Hothead | last post by:
Hi, Iím having difficulty getting a report in ms access 2003 to print (physically print to printer) once Iíve clicked a command button in a form. Whatís the best way to do this? I tried...
by: David Anderson | last post by:
I recently began working with Access 2007, but I do have a limited experience with prior versions. I have a form with a button whose visibility I would like to make conditional on the Month value...
by: Palaniappan | last post by:
how to add an item into the textbox by using command button in ms access in the form from the table?
by: mhegazy | last post by:
I have an access form having a command button which will perform a certain action onclick for the current record. I need to disable the button function by navigating the records before the current...
by: spdragoo | last post by:
OK, so I'm helping my wife out & trying to develop an Access 2013 database that will be used on Windows PCs. They're setting up a special tutoring lab at her college where remedial students can take...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.