SQL in VBA Transfer Spreadsheet

[bradh_nz]

Hi

I have the below code to transfer a spreadsheet and name the spreadsheet based on an sql Query (HFile) however I cannot seem to get this to work. Any help appreciated.
-----------------------------------------
Option Compare Database
Function Export_RC()
Dim strInput As String
Dim strMsg As String

strInput = HFile

DoCmd.TransferSpreadsheet acExport, 8, "RC", "\\Chad-pl05\GIM\Systems\Issues\63 (file://\\Chad-pl05\GIM\Systems\Issues\63) - Basel Late Adjustments\2009 BASEL Update Work\" & strInput & ".xls", True, ""
End Function
-----------------------------------

Function HFile()
Dim db As Database
Dim Rs As Recordset
Dim sSQLstr As String
Set db = CurrentDb
sSQLstr = "SELECT Details.Hfile " & _
" FROM Details "
Set Rs = db.OpenRecordset(sSQLstr)
Rs.Close

End Function

[CreganTur]

When you post code be sure you wrap it in VBA tags (click the green VBA button when posting). This will format it according to VBIDE and make it easier to read.

First thing I notice is that you call HFile as a function, but never set any value to HFile. Also, you haven't declared the data type that HFile should return.

Another issue is that you're opening a recordset that will pull all values of HFile within your Details table. That could be multiple records. How are you sure that you're pulling the value you want?