The action or method requires a table name argument

[sandylou]

i created a script which when put into a module run fine by itself. when i make it a function, and call it from a form in access i receive the following error:

'The action or method requires a table name argument'

here is the code:

Option Compare Database
Option Explicit

'**********************************************************************************************
'Define your query here
Private Const conQuery = "myquery"
'*********************************************************************************************
Public Function createExcelSheet()
'**********************************************************************************************
Dim rst As ADODB.Recordset
Dim rstDetail As ADODB.Recordset
Dim varSheetName As String
Dim strSqlDetail As String
Dim varNumRow As Long
Dim varNumCol As Long
Dim varCurRow As Long
Dim varFieldValue As String
Dim varHeader As String
Dim varSumTitle As String
Dim i As Integer
' Excel object variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

'**********************************************************************************************
On Error GoTo HandleErr

'Define our variables
varSheetName = "sheetname"
varSumTitle = "sumName"
'this will tell us what row to start the next recordset on
varCurRow = 2
varHeader = "myheader"

' Create Excel Application object
Set xlApp = New Excel.Application

' Create a new workbook
Set xlBook = xlApp.Workbooks.Add

'Get rid of all but one worksheet
xlApp.DisplayAlerts = False
For i = xlBook.Worksheets.Count To 2 Step -1
xlBook.Worksheets(i).Delete
Next i
xlApp.DisplayAlerts = True

' Capture reference to first worksheet
Set xlSheet = xlBook.ActiveSheet
xlSheet.Name = varSheetName
With xlSheet
'Create recordset
Set rstDetail = New ADODB.Recordset
strSqlDetail = "SELECT * FROM " & conQuery & _
" ORDER BY [Name], [Start PPE Date], [End PPE Date]"
rstDetail.Open strSqlDetail, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
'move to the first record in the recordset
'this is the number of records we have per employee, this number changes
varNumRow = rstDetail.RecordCount
'this number is consistent through the entire sets
varNumCol = rstDetail.Fields.Count
'want this to start on the same row of the name
For i = 0 To varNumCol - 1
varFieldValue = rstDetail.Fields(i).Name
.Cells(1, i + 1).Value = varFieldValue
.Cells(1, i + 1).Font.Bold = True
.Cells(1, i + 1).HorizontalAlignment = xlCenter
Next i

Range((Cells(varCurRow, 1)), (Cells(varCurRow, 1))).CopyFromRecordset rstDetail
For i = 1 To varNumCol
.Columns(i).AutoFit
Next i
End With
'Give Name to the sheet along with Page setup values
With xlSheet.PageSetup
.CenterHeader = varHeader
.PrintTitleRows = "$1:$1"
.PaperSize = xlPaperLegal
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 200
End With
'Display the Excel Sheet
xlApp.Visible = True


ExitHere:
On Error Resume Next
' Clean up
rstDetail.Close
Set rstDetail = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
xlApp.Quit
Exit Function

HandleErr:
MsgBox Err & ": " & Err.Description, , "Error in CreateExcelForms"
Resume ExitHere
Resume
End Function

 

[sfreeman]

Declare your Const "myquery' as public not private...

see if that helps at all.


Sam_F
"90% of the problem is asking the right question.

 

[sandylou]

No, I still get the error. Thanks though.