Switchboard & functions

[KHogwood-Thompson]

I have the following code in a function:

Function cmdExport()


Dim dbD As DAO.Database
Dim rsJOBREF As DAO.Recordset
Dim strFilespec As String
Dim lngJOBREF As String
Dim strSheet As String
Dim strSQL As String

Const SQL1 = "SELECT * INTO [Excel 8.0;HDR=Yes;Database="
Const SQL2 = "FROM CVR WHERE JOBNO = "

Set dbD = CurrentDb()
Set rsJOBREF = dbD.OpenRecordset("SELECT JOBNO FROM CVR GROUP BY JOBNO" _
, dbOpenSnapshot)
strFilespec = "G:\MSData\Kevin Hogwood\Pollards\CVR\New Data.xls"

Do Until rsJOBREF.EOF
'Get JOBREF
lngJOBREF = rsJOBREF.Fields("JOBNO").Value
strSheet = CStr(lngJOBREF)

'Assemble the SQL query to export one jobref
strSQL = SQL1 & strFilespec & ";].[" & strSheet & "] " _
& SQL2 & "'" & lngJOBREF & "'" & ";"

'export it
dbD.Execute strSQL, dbFailOnError
rsJOBREF.MoveNext
Loop

rsJOBREF.Close

End Function


I have a macro that has the RunCode action and the following as the function
name:

cmdExport ()

When I try to run this macro I get the following error:

The express yoo#u entered has a function name that Microsoft Access can't
find.

Does anyone know why this is happening?

 

[Alex Dybenko]

Hi,
try to set a stop at:
dbD.Execute strSQL, dbFailOnError

then go to debug window and print strSQL

?strSQL

paste it in a new query SQL and try to run it, perhaps something wrong with
it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Allan Murphy]

Your code should be in a PROCEDURE and not a FUNCTION.

A FUNCTION returns a value to the variable that called it

Replace Function cmdExport() with Sub cmdExport() and replace End Function
with End Sub

Allan

 

[John Spencer]

"Your code should be in a PROCEDURE and not a FUNCTION"

Not necessarily true.

For instance, You can't call a SUB from a button in the menu bar.
You can't use a sub directly in an event property, you must use VBA to
call the sub. There are situations where it is preferable to be able to
enter
=MyGeneralFunction()
in multiple controls at once.

Also, even though this could be a Sub, there is no harm in it being a
function.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================