Problem with naming an Export File

[ddrew]

Hi, I am exporting a single record to a spreadsheet. The code I have works fine. The problem is I want to have the spreadsheet contain the information that is in one of the forms textboxes, in this case a dogs name.

I have created a query to get the dogs name ewhich works but I dont know how to get that query to run and then be put in the file name.

The code that I have is below

Private Sub btn_ExportDog_Click()
    Dim strday As String    'The date
    Dim sDest As String    'Where the file will be copied to
    Dim sSource As String    'The name of the file to be copied
    Dim strDogName As String 'The name of the dog that is geting exported
    Dim strBackUp As String

    strBackUp = "c:\GPandDetectionDogTrainingLog\BackUp\"

    If Len(Dir(strBackUp, vbDirectory)) = 0 Then
        MkDir strBackUp
    End If

    Shell "EXPLORER.EXE " & strBackUp, vbNormalFocus

    strday = Format(Now(), "yyyy_mm_dd")
    sSource = "c:\GPandDetectionDogTrainingLog\BackUp\GPandDetectionDogTrainingLogBackUp.xls"
    strDogName = "qry_DogName"

    sDest = "c:\GPandDetectionDogTrainingLog\BackUp\GPandDetectionDogTrainingLogBackUp " & strDogName & strday & ".xls"
    
    'Copies the files to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_ExportDog", "c:\GPandDetectionDogTrainingLog\BackUp\GPandDetectionDogTrainingLogBackUp.xls", True

    'Copies and renames the file
    FileCopy sSource, sDest

End Sub

 

[ddrew]

Fixed!

    strDogName = Forms![frm_Profile]![DogName]