Save file in specific location (1 Viewer)

[Bumi66]

I'm trying to save a file in a specific location on my hard drive.
The location should be m:\Order acknowledgment\Customer name\Order number.pdf

The customer name is in a field in my form "match" this varies
also the Order number is in a field in the form under "Order number"

This is my Code but it doesn't work


Private Sub Command240_Click()
Dim Match As String

DoCmd.OutputTo acOutputReport, "Order Confirmation", acFormatPDF, "m:\Order acknowledgement\" " str.Match "\" & Me.Order number & ".pdf", True

End Sub

What did I do wrong I'm new to VBA please help

 

[Minty]

Create a couple of strings and break this down into sensible steps so you can see what is happening.

Dim sTopPath as String
Dim sCust       as String
Dim sOrdNo   as String
Dim sFullPath as string

sTopPath ="M:\Order acknowledgement\"
sCust = Me.CustomerName   ' Where Customername is the name of the control on your form
sOrdNo = Me.OrderNumber 

sFullPath = sTopPath & sCust & "\" & sOrdNo & ".pdf"

Debug.print sFullPath            ' You can see this in the immediate window in the VBA editor - press ctrl + G to view it

DoCmd.OutputTo acOutputReport, "Order Confirmation", acFormatPDF, sFullPath , True

Always put concatenated strings into a separate string to help with debugging.

 

[theDBguy]

Hi. The first thing I would recommend is to add Option Explicit at the top of your Module.

 

[Bumi66]

Create a couple of strings and break this down into sensible steps so you can see what is happening.

Dim sTopPath as String
Dim sCust       as String
Dim sOrdNo   as String
Dim sFullPath as string

sTopPath ="M:\Order acknowledgement\"
sCust = Me.CustomerName   ' Where Customername is the name of the control on your form
sOrdNo = Me.OrderNumber

sFullPath = sTopPath & sCust & "\" & sOrdNo & ".pdf"

Debug.print sFullPath            ' You can see this in the immediate window in the VBA editor - press ctrl + G to view it

DoCmd.OutputTo acOutputReport, "Order Confirmation", acFormatPDF, sFullPath , True

Always put concatenated strings into a separate string to help with debugging.

 

[Pat Hartman]

There are a number of syntax errors and I don't have enough information to actually fix them but you should be able to. Begin by defining the name as a variable. It makes debugging easier.
Dim strFileName As String

strFileName = "m:\Order acknowledgement\" & """" & Me.Match & """" & Me.[Order number] & ".pdf"
DoCmd.OutputTo acOutputReport, "Order Confirmation", acFormatPDF, strFileName, True

Now you can set a break on the docmd. line and print the string into the debug window so you can see if it is correct. People's names can sometimes contain single quotes so they need to be enclosed in double quotes. One way to concatenate the double quotes is with four double quotes. Another way is to define a constant which is what i usually do since I think it makes the concatenation more obvious. put the constant into a standard module, NOT in the form's class module since you want it to be available for all forms/reports.

Public Const QUOTE = """"

strFileName = "m:\Order acknowledgement\" & QUOTE & Me.Match & QUOTE & Me.[Order number] & ".pdf"

 

[Bumi66]

Thank you so much, you made my day, It works

Create a couple of strings and break this down into sensible steps so you can see what is happening.

Dim sTopPath as String
Dim sCust       as String
Dim sOrdNo   as String
Dim sFullPath as string

sTopPath ="M:\Order acknowledgement\"
sCust = Me.CustomerName   ' Where Customername is the name of the control on your form
sOrdNo = Me.OrderNumber

sFullPath = sTopPath & sCust & "\" & sOrdNo & ".pdf"

Debug.print sFullPath            ' You can see this in the immediate window in the VBA editor - press ctrl + G to view it

DoCmd.OutputTo acOutputReport, "Order Confirmation", acFormatPDF, sFullPath , True

Always put concatenated strings into a separate string to help with debugging.

 

[Bumi66]

Thank you so much it works, you made my day

 

[Minty]

Thank you so much, you made my day, It works

You should take note of the instruction to add Option Explicit to all your code modules.

And now would be a good time to remove all the spaces from your field names, and give you controls sensible names.
Have a read about a naming convention, and reserved words and special characters.

 

[Ranman256]

Never use drive letter paths.
Always use UNC server paths: \\server\folder\folder\file.txt

 

[Pat Hartman]

I forgot to comment on hardcoding the path at all. I agree with Ranman256 that UNC format is better when you are on a LAN and that is what I use for all shared documents. But, I usually have a table that holds certain defaults and one of them is the path for saving documents. If you have multiple types of documents and you want to segregate them, you might need several entries in this table. If the paths will be different by user, you need UserID as part of the PK or unique index on the table.

strFileName = DLookup("Path", "tblDocumentFolders", "UserName = '" & Environ("UserName") & "' AND DocType ='OrderAck'")
strFileName = strFileName & """" & Me.Match & """" & Me.[Order number] & ".pdf"

The path returned by the domain function will be "m:\Order acknowledgement\" or preferably, ""\\ServerName\Order acknowledgement\"

Just make sure that the folder names always get saved with a trailing slash. Otherwise, you'll have to deal with that EVERY TIME you use the field.

There are also more sophisticated options should you want to have the option to use a different folder at runtime. I tend to not do that for standard documents because it just gives the user an opportunity to make a mistake.