Specified file name not working

[iPanterra]

Hi apologies in advance if this has been asked before.

I have created a button in a form and its function

on click ( I have gone into the macro builder)

-ExportWithFormating
Object type - Form
Object name - (???)
Output format - PDF Format (*.PDF)

now heres the thing If I leave the object name blank it automatically tries to save the form into a pdf format with a default name "FrmMachinefault.pdf

and this works perfectly.

However if I tried to specify the name in anyway even a simple "file" it comes up with an error.

the code I want to put in is a macro to pull information from the existing form

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

The result should be for example.
02_07_15_101_Foundry_Mixer_Broken_Shaft.PDF

this is what I want and what it currently does is goes to the safe as page with the correct title but when I click SAVE it comes with

"Microsoft Access cannot find the object 'ǀ1'.
Make sure the object exists and that you spell its name correctly."

I have gone through the names over and over again and it should be correct please help

[jimatqsi]

Isn't it because the object name that is being referred to is the object that should be exported, not the name of the resulting file. You could specify the name of any form or table or query.

Jim

[iPanterra]

Hi Jim, I think you are correct, I have changed it back to the correct Object name and it consistently work.

However, that doesn't fix my problem. I require it to pull information from the "opened" for the filename so when I click the export button, it will automatically pull the information I want as per the example and all I need to do is press save after I go into the location.

[zmbd]

Which version of Office are you using?
ExportWithFormatting Macro Action
You should have the option to set the OutputFile

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
   <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
      <UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000">
         <Statements>
            <Action Name="ExportWithFormatting">
               <Argument Name="ObjectType">Form</Argument>
               <Argument Name="ObjectName">frmQuery1</Argument>
               <Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument>
               <Argument Name="OutputFile">C:\Users\ZMBD\documentname.pdf</Argument>
            </Action>
         </Statements>
   </UserInterfaceMacro>
</UserInterfaceMacros>

[iPanterra]

Currently using Office 2010

I have tried putting additional information in the OutputFile however when I start to put any sorts of code in there it errors on me.

[zmbd]

Hmm... the limitations of Acc-Macro language.
What is the exact error when you attempt to build the string in the [OutputFile] parameter?
Is the string that you are attempting to use the one in the OP? (= [Closed date] & "_" & [Effected area] & "_" & [Asset] & "_" & [Title].
This will not work... the full path and correct file name, including extension (I know, says optional in the link; however, I suggest that it be included - saves on the head-pain), must be built (i.e: c:\directory1\directory2\filename.pdf). Refer to the link I posted on this function for details.

You could code a function to return the full path and file name.

You could use the tempvars (example macro here too) macro method, build your path, then use the tempvar in the output file


By way of example, the following works in my testing database (I have change the user path of course :) )

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    <UserInterfaceMacro For="Command10" Event="OnClick">
        <Statements>
            <Action Name="ExportWithFormatting">
                <Argument Name="ObjectType">Form</Argument>
                <Argument Name="ObjectName">frmQuery1</Argument>
                <Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument>
                <Argument Name="OutputFile">="C:\Users\ZMBD\Documents\" &amp; [FieldNameOnForm] &amp; ".pdf"</Argument>
            </Action>
        </Statements>
    </UserInterfaceMacro>
</UserInterfaceMacros>

If you (toggle the numbers first) select the above and were to copy and paste it into a command button named Command10 in the on_click event as an embedded macro this will run,...

Two things to note, the actual text in the output file is:
="C:\Users\ZMBD\Documents\" & [FieldNameOnForm] & ".pdf"

AND

the entire form's record set is exported not just the current record... I haven't tried with a filter set either via the GUI, Macro, or VBA so I do not know if with the Macro action the filter would only export the filtered records.

[iPanterra]

What I have tried to put in the output file as

="Z:\(P) Maintenance\Planned Maintenance\Jobs\" & [Closed date] & "_" & [Effected area] & "_" & [Asset] & "_" & [Title] &".PDF"

The error I get is

MS Access cant save the output data to the file you've selected.

[NeoPa]

Can you post the resultant string. IE. The actual name you're trying to use as opposed to the formula which creates it.

[iPanterra]

@NeoPa

I don't understand sorry, what I am trying to get as an end result would be something like this.

PDF file name:
14_07_15_Foundry_Furnace_Faulty Electrics
[Closed date]_[Effected area]_[Asset]_[Title]

So if I was to see the end address of this file it would look like this.

Z:\(P) Maintenance\Planned Maintenance\Jobs\14_07_15_Foundry_Furnace_Faulty Electrics.PDF

I hope this was what you were after.

[zmbd]

iPanterra : AFAIK: you can not use the parentheses " () " in a file name.

-It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY important to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access


Neopa: iPanterra is in the Macro editor... stuck building the string in the function, which is a pet-peeve of mine as you well know ;-)

WITH THAT SAID... there is a workaround since Acc2010 that is the tempvars collection... great way to store session level information and to pass info between the Macro side of Access to the VBA side and to use in queries... one of the more useful tools MS added to the program (IMNSHO)!

iPanterra: To build the string, refer back to the settempvars links I gave earlier. Build your string there and then use the tempvar set in the macro.

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    <UserInterfaceMacro For="Command10" Event="OnClick">
        <Statements>
            <Action Name="SetTempVar">
                <Argument Name="Name">savepath</Argument>
                <Argument Name="Expression">"C:\Users\ZMBD\Documents\" &amp; [Expr] &amp; ".pdf"</Argument>
            </Action>
            <Action Name="ExportWithFormatting">
                <Argument Name="ObjectType">Form</Argument>
                <Argument Name="ObjectName">frmQuery1</Argument>
                <Argument Name="OutputFormat">PDFFormat(*.pdf)</Argument>
                <Argument Name="OutputFile">=[TempVars]![savepath]</Argument>
            </Action>
            <Action Name="RemoveTempVar">
                <Argument Name="Name">savepath</Argument>
            </Action>
        </Statements>
    </UserInterfaceMacro>
</UserInterfaceMacros>
 

To trouble shoot
Either insert a stopmacro action prior to, or
OMIT, lines 15,16,17 - these clean up the tempvars.
Cut and paste this in your macro editor for the control... (if not using the stop action then delete the removetempvar entry).
Run your code
Press <ctrl><g>
in the immediate window
type ?[tempvars]![savepath]
You will get the resolved string
C:\Users\ZMBD\Documents\Doe.pdf

Of course I've changed the path for this post :) however, you should get the ideal.

[NeoPa]

iPanterra:
I hope this was what you were after.

Yes :-)

[iPanterra]

Zmbd

I tried your code, however im getting a compile error at the &amp; section

[zmbd]

Open the Macro editor and enter the string as given in the grey box below the codeblock in Post#6

You should be able to toggle the numbers off in Post#6 around the code block, select, copy...

Few things to note...
Open notepad, cut and paste the code there first.
Delete any leading blank lines
Change:
<UserInterfaceMacro For="Command10" Event="OnClick">
So that "Command10" is the name of your command-button
<UserInterfaceMacro For="MeSaHaveSaNewNameBetchaBetcha" Event="OnClick">

Open the command button on your form, on click event, macro builder, paste the altered code in to the window.

You should now see the correctly formatted string, etc...

[iPanterra]

I approached this from a different angle with some help, and I have managed to get this to work, saving a form to a location with file name changes depending on variables in the form. End result is

16_07_15_Foundry_Furnace_Test 101, in PDF format.

This is perfect and I appreciate all the help I have received thus far.