ExportingWithFormating with Variable file path and name (1 Viewer)

NeedHelpASAP · Jul 25, 2019

Im wondering if it is possible to use the ExportWithFormating macro with an Output File dependent on a combo box and date picker located on separate forms. the expression I have right now isnt working

=P:...\Merch Database" & [Forms]![frmReports]![Combo256] & "\Copy of EmpRouteMatrix" & [Forms]![frmReports]![Combo256] & ".pdf"

(didnt include the entire file name as its pretty long but the first combo is the variable folder name and the second is connected to a date picker)

Is this possible in a macro or am I in over my head?

theDBguy · Jul 25, 2019

Hi. Welcome to the forum! Yes, this should be possible provided you didn't make any spelling errors and the forms are currently open when you run the macro.

NeedHelpASAP · Jul 25, 2019

I double checked the spelling and it all looks good, I did notice I had the same combo box in the expression twice, here is the updated

="P:...\Merch Database\" & [Forms]![frmReports]![Combo256] & "\Copy of EmpRouteMatrix" & [Forms]![USER PAGE]![Text67] & ".pdf"

I am still getting an error saying it cant save the output data, any thought to what might have been wrong?

theDBguy · Jul 25, 2019

NeedHelpASAP said:
I double checked the spelling and it all looks good, I did notice I had the same combo box in the expression twice, here is the updated

="P:...\Merch Database" & [Forms]![frmReports]![Combo256] & "\Copy of EmpRouteMatrix" & [Forms]![USER PAGE]![Text67] & ".pdf"

I am still getting an error saying it cant save the output data, any thought to what might have been wrong?

Add a Message Box action to verify the file name is correct and valid (no illegal characters).

NeedHelpASAP · Jul 25, 2019

The message box appears with the correct file location and name but I am still getting the output error. This would create a new file, would that cause the error?

GinaWhipp · Jul 25, 2019

What is the error message you are getting? Oh, an include the number if there is one.

NeedHelpASAP · Jul 25, 2019

the error says:

"Microsoft Access can't save the output data to the file you've selected"

no error number or anything like that. :banghead:

theDBguy · Jul 25, 2019

NeedHelpASAP said:
the error says:

"Microsoft Access can't save the output data to the file you've selected"

no error number or anything like that. :banghead:

Hi. Can you post the macro code? Thanks.

NeedHelpASAP · Jul 25, 2019

theDBguy said:
Hi. Can you post the macro code? Thanks.

here is the macro im trying to run

GinaWhipp · Jul 25, 2019

Hmm, in addition to the code are you sure you have the correct permissions to said folder?

NeedHelpASAP · Jul 25, 2019

yes, i created the folder

Gasman · Jul 25, 2019

Can you show us the MSGBOX output please?

theDBguy · Jul 25, 2019

NeedHelpASAP said:
here is the macro im trying to run

Hi. Thanks. Just a quick test though, try enclosing the name of the report in square brackets.

NeedHelpASAP · Jul 25, 2019

So I was messing around with it and realized that the date in the filename is coming up as "mm/dd/yyyy" and the / are the problem. Any way to change it to a - or a .??

theDBguy · Jul 25, 2019

NeedHelpASAP said:
So I was messing around with it and realized that the date in the filename is coming up as "mm/dd/yyyy" and the / are the problem. Any way to change it to a - or a .??

Hi. Yes, try using the Format() function.

arnelgp · Jul 25, 2019

also if you include a textbox/combobox with date and has "/" as delimiter in them, it won't save. try formatting it using "." or "-" or "_" as delimeter:

Code:

& Format(yourControl, "dd_mm_yyyy")

also you might want to check if the pdf already exists, therefore delete it first

Code:

if dir("thePathAndFileName.pdf") <> "" Then Kill ("ThePathAndFileName.pdf")

ExportingWithFormating with Variable file path and name (1 Viewer)

NeedHelpASAP

New member

theDBguy

I’m here to help

NeedHelpASAP

New member

theDBguy

I’m here to help

NeedHelpASAP

New member

GinaWhipp

AWF VIP

NeedHelpASAP

New member

theDBguy

I’m here to help

NeedHelpASAP

New member

Attachments

GinaWhipp

AWF VIP

NeedHelpASAP

New member

Gasman

Enthusiastic Amateur

theDBguy

I’m here to help

NeedHelpASAP

New member

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread