Creating new file name from Form Fields for External Attachments

[shannonsims]

I recently drafted the below code that allows me to track administrative documents for personnel in Access 2010 and save a PDF of the document on a shared drive in order to keep the database from bloating beyond the 2GB limit.

I'm now trying to save the attachment with a name that is based on 3 fields from the form that allow for effective sorting and filing of the PDF attachment but have no clue where to start. The field names and types are:
Employee: combo box
DocDescription: combo box
DocDate: date

An example of how I would like VBA to format the new name for the PDF is "DocDate_Employee_DocDescription.pdf"

Any guidance is appreciated as I'm a novice VBA guy.

Private Sub AdminDocPath_DblClick(Cancel As Integer)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
'1) To set the caption of the dialog box, set the Title property
fd.Title = "Select Admin Document to Upload"
'2) Set the oddly named InitialFileName property to determine the initial folder selected
fd.InitialFileName = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs"
'3) Set the InitialView property to control how your files appear on screen (as a list, icons, etc.)
fd.InitialView = msoFileDialogViewSmallIcons
'4) To set the filters (you can have as many as you like) first clear any existing ones, then add them one by one
fd.Filters.Clear
fd.Filters.Add "PDF macros", "*.pdf"
fd.Filters.Add "Excel macros", "*.xlsm"
' if there's more than one filter, you can control which one is selected by default
fd.FilterIndex = 1
' if there's more than one filter, you can control which one is selected by default
fd.FilterIndex = 1
'5) Set the ButtonName property to control the text on the OK button (the ampersand means the following letter is underlined and choosable with the ALT key)
fd.ButtonName = "Select file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "Upload Cancelled"
Else
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
'store the selected file to a variable
strSelectedFile = fd.SelectedItems(1)
'parse out the filename from the path
strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))
'build the destination
strDestination = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs\" & strFilename
'copy the file to the new folder
FileCopy strSelectedFile, strDestination
'store file as part of the record
Me.AdminDocPath = strFilename
'save hyperlink as part of record
Me.AdminDocLink = strDestination
End Sub

 

[Bilbo_Baggins_Esq]

Can't you just define some variables and then capture the value from the form?

Dim EmployeeV as string
Dim DocDescriptionV as string
Dim DocDateV as date

EmployeeV = me.Employee
DocDescriptionV = me.DocDescription
DocDateV = me.DocDate

I'm probably missing something though...

 

[shannonsims]

Bilbo,
It may be that simple....Noobie here trying to learn the language. I figured it had to be something fairly simple but didn't know how to format the code to pull it from the fields and and define the strFilename.

Where would that fit into the above coding? Appreciate the help.

 

[Bilbo_Baggins_Esq]

It looks like this code:

'parse out the filename from the path
strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))

Strips off the actual file name from the right side of the full original path of the Selected file.

After:
1. creating the above variables and,
2. assigning thier value from the form fields and,
3. striping down to just the original file name,

one example file name could be:

strFilename = strFilename & EmployeeV & DocDescriptionV & DocDateV

I just looked at my above suggestion, and I think I missed something too.
Should be:

EmployeeV = me.Employee.value
DocDescriptionV = me.DocDescription.value
DocDateV = me.DocDate.value

You might even ba able to skip the variable (step 1) altogether with this:

strFilename = strFilename & me.Employee.value & me.DocDescription.value & me.DocDate.value

Depending on the value and formatting of the date, you might have to play with that though.

 

[Bilbo_Baggins_Esq]

what ever you do to construct your file name, needs to be completed before this line:

'build the destination
strDestination = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs\" & strFilename

 

[shannonsims]

I tried the second option in the below coding and got the following error message "Method or Data Member not found" for:
strFilename = Me.AdminDocDate.Value & Me.Employee.Value & Me.AdminDocDescription.Value

I also tried pulling the AdminDocDate out since you said the formatting might cause issues, but then the debugger gave the same msg with Me.Employee.Value. Any thoughts?

Private Sub AdminDocPath_DblClick(Cancel As Integer)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
'1) To set the caption of the dialog box, set the Title property
fd.Title = "Select Admin Document to Upload"
'2) Set the oddly named InitialFileName property to determine the initial folder selected
fd.InitialFileName = "H:\Personnel"
'3) Set the InitialView property to control how your files appear on screen (as a list, icons, )
fd.InitialView = msoFileDialogViewSmallIcons
'4) To set the filters (you can have as many as you like) first clear any existing ones, then add them one by one
fd.Filters.Clear
fd.Filters.Add "PDF macros", "*.pdf"
fd.Filters.Add "Excel macros", "*.xlsm"
' if there's more than one filter, you can control which one is selected by default
fd.FilterIndex = 1
' if there's more than one filter, you can control which one is selected by default
fd.FilterIndex = 1
'5) Set the ButtonName property to control the text on the OK button (the ampersand means the following letter is underlined and choosable with the ALT key)
fd.ButtonName = "Choose PDF file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "Upload Cancelled"
Else
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
'store the selected file to a variable
strSelectedFile = fd.SelectedItems(1)
'parse out the filename from the path
strFilename = Me[COLOR="Red"].AdminDocDate[/COLOR].Value & Me.Employee.Value & Me.AdminDocDescription.Value
'build the destination
strDestination = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs"
'copy the file to the new folder
FileCopy strSelectedFile, strDestination
'store file as part of the record
Me.AdminDocPath = strFilename
' Save link in a hyperlink field
Me.AdminDocLink = strDestination
End Sub

 

[shannonsims]

I stand corrected Bilbo. It will move through the Me.Employee.Value but hangs up on the Me.AdminDocDate.Value and Me.AdminDocDescription.Value. Unsure what could be causing it.

 

[Bilbo_Baggins_Esq]

Me.AdminDocDate.Value

A control named "AdminDocDate" with a .value attribute would have to exist on the current form (me.) in order for you to pull the .value from it.

In general, just using any full date as part of a file name is probably a bad idea (assuming "AdminDocDate" even exists on the form).

You can pull parts of a date such as
Year(me.DocDate.value) 'returns the year
Month(me.DocDate.value) 'returns the month, etc

 

[Bilbo_Baggins_Esq]

You have to make sure of the actual names of the controls in your form.

To be clear, the actual name may not be what the label in the form says.

 

[Bilbo_Baggins_Esq]

Also, come to think of it, I'm not sure you can even asign data with a date data type to a string type.

If it is mandatory to apply some part(s) of the date to the file name, you would probably HAVE to strip parts of the date out

Dim DateString as string

DateString  = Year(ProposedDate) & "_" & Month(ProposedDate) & "_" Day(ProposedDate)

or

Dim DateString as string

DateString = Year(Me.DateControl.Value) & "_" & Month(Me.DateControl.Value) & "_" Day(Me.DateControl.Value)

 

[shannonsims]

And again you've pointed me to the obvious mistake. I had the Name and Control Source mixed up in properties.It did have an error about not being able to find the strDestination, but (as you pointed out earlier) it was caused by date format issues (ie. 4/9/2012). Breaking it out by Year, Month and Day fixed that issue as it did away with the /'s. Hopefully I'll be finalizing the code tomorow and will post for anyone else's benefit. Thanks a bunch Bilbo.

 

[shannonsims]

Bilbo,

Below is the completed code. On your advice, I added the Dim DateString code and defined the variable (which worked flawlessly) to name the newly saved file "YYYY_MM_DD_Employee_AdminDocDescription" as pulled from the unput on the entry form.

The only issue I'm having now is that since I edited the strFileName to

strFilename = DateString & "_" & Me.Employee.Value & "_" & Me.AdminDocDescription.Value

it no longer retains the file extension when it saves the file. Any thoughts as to what I'm missing?

Many thanks for the help you've provided, I've made more progress in 2-3 days than I had in previous 2-3 weeks.

Private Sub AdminDocPath_DblClick(Cancel As Integer)
Dim DateString As String
DateString = Year(Me.AdminDocDate.Value) & "_" & Month(Me.AdminDocDate.Value) & "_" & Day(Me.AdminDocDate.Value)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
'1) To set the caption of the dialog box, set the Title property
fd.Title = "Select Admin Document to Upload"
'2) Set the oddly named InitialFileName property to determine the initial folder selected
fd.InitialFileName = "H:\Personnel"
'3) Set the InitialView property to control how your files appear on screen (as a list, icons, )
fd.InitialView = msoFileDialogViewSmallIcons
'4) To set the filters (you can have as many as you like) first clear any existing ones, then add them one by one
fd.Filters.Clear
fd.Filters.Add "PDF macros", "*.pdf"
fd.Filters.Add "Excel macros", "*.xlsm"
' if there's more than one filter, you can control which one is selected by default
fd.FilterIndex = 1
' if there's more than one filter, you can control which one is selected by default
fd.FilterIndex = 1
'5) Set the ButtonName property to control the text on the OK button (the ampersand means the following letter is underlined and choosable with the ALT key)
fd.ButtonName = "Choose PDF file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "Upload Cancelled"
Else
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
'store the selected file to a variable
strSelectedFile = fd.SelectedItems(1)
'parse out the filename from the path
strFilename = DateString & "_" & Me.Employee.Value & "_" & Me.AdminDocDescription.Value
'build the destination
strDestination = "C:\Users\swsims\Desktop\E-Pers Test\Admin Docs\" & strFilename
'copy the file to the new folder
FileCopy strSelectedFile, strDestination
'store file as part of the record
Me.AdminDocPath = strFilename
' Save link in a hyperlink field
Me.AdminDocLink = strDestination
End Sub

 

[Bilbo_Baggins_Esq]

are the output files always of the same type?

 

[shannonsims]

Yes. I'm restricting the file attachments to PDF only.

 

[Bilbo_Baggins_Esq]

just add this to the end of your file name creation line:

& ".pdf"

Should result in this:

'Build the new file name from form field values.
strFilename = DateString & "_" & Me.Employee.Value & "_" & Me.AdminDocDescription.Value & ".pdf"

Also note the updated remarked line to more accurately describe what is happening.
You are no longer parsing out any file name, you are building it from the form values

 

[shannonsims]

That took care of it. Thanks a million Bilbo.