Get Export File name from Unbound Text Box

[FinChase]

I have a query that I want to allow users to automatically export to Excel. I have a macro set up that uses the TransferSpreadsheet function and sets the path. However, I'd like to further automate it by using a unique name for each spreadsheet saved as I will have several users exporting files to the same folder.



I would like to have the user enter the name in a text box, such as Doe, John. This is simply entered on a form that is used to provide criteria for the queries that are run. Nothing from the form is written to any of the tables. Is there a way to pull the data in the unbound text box and use that as the file name. In my example, I'd like to see the file name be Doe, John.xls.

Any help is appreciated!

[FinChase]

Never mind. I figured out an answer, using the code below, in case anyone else needs it:

Private Sub cmdKS_Export_Click()
On Error GoTo Err_cmdKS_Export_Click
Dim strMyPath As String
Dim strEEName As String
strMyPath = "Q:\Client Name\Open Enrollment\2012\Rates\Retirees\Retiree Estimates Database\Archive\"
strEEName = strMyPath & Me.txtLName & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryKS_All Plan Options_CalcCost", strEEName, True, ""

Exit_cmdKS_Export_Click:
Exit Sub
Err_cmdKS_Export_Click:
MsgBox Err.Description
Resume Exit_cmdKS_Export_Click

End Sub