Creating subroutine with Function using sql recordsets

[BobT]

I am trying to create a button on a form that when clicked will access a
field in a table which contains a file name. I am using the FileCopy
statement to copy this file to a different location and renaming it at the
same time. I am receiving a type error on the function call.

Private Sub Command0_Click()

Sub SQLX()

Dim db1 As Database
Dim qdfTemp As QueryDef
Dim rstImagePointer As Recordset

Set qdfTemp = db1.CreateQueryDef("")

' Open Recordset using temporary QueryDef object and
' print report.
SQLOutput "SELECT Test FROM Table1", qdfTemp
'& _
' "WHERE Country = 'USA' " & _
' "ORDER BY LastName" , qdfTemp

' Open Recordset using temporary QueryDef object and
' print report.
'SQLOutput "SELECT * FROM Employees " & _
' "WHERE Country = 'UK' " & _
' "ORDER BY LastName", qdfTemp

End Sub

Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

Dim rstImagePointer As Recordset

' Set SQL property of temporary QueryDef object and open
' a Recordset.

qdfTemp.SQL = strSQL
Set rstImagePointer = qdfTemp.OpenRecordset
Debug.Print !Test

With rstImagePointer
' Enumerate Recordset.
Do While Not .EOF
FileCopy !Test, "c:\dell\test2.txt"
.MoveNext
Loop
.Close
End With

End Function

 

[Douglas J. Steele]

Try changing your declarations from

Dim rstImagePointer As Recordset

to

Dim rstImagePointer As DAO.Recordset