Run Time 2495

[LB79]

Hello,

Im having a problem with RunTime error 2495 – The action or method requires a table name argument.
My code is supposed to run in a form, call a public function in a module to select a save location, then return to the form code to export a file.
I have the public code as a lot of forms will be using it (if I can get past this hurdle).

I've pasted my code in below.

Thanks for any help.

Call ExpFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, MyQry, DestFile 'Debug highlights this row
End If


Option Compare Database
Public FileName As String
Public FilePath As String
Public DestFile As Variant
Public Sub ExpFile()
Dim FSelect As Office.FileDialog
Dim Mth As String
Dim Dte As String
Dim Tme As String
Dim NewFile As String
Mth = Format(Now, "YYYYMM")
Dte = Format(Now, "YYYYMMDD")
Tme = Format(Now, "HH-MM-SS")
MyFile = ""
NewFile = ""
'Select Save Loction
Set FSelect = Application.FileDialog(msoFileDialogSaveAs)
With FSelect
.AllowMultiSelect = False
.InitialFileName = FileName
.Title = "ATMT: Please select a location"
If .Show = True Then
For Each DestFile In .SelectedItems
DestFile = .SelectedItems(1)
Next
End If
End With
End Sub

 

[JamesMcS]

So does expfile return a value into destfile? What happens when you print destfile in the immediate window? If it's blank, that'll be the problem and it'll be something in the function.

 

[boblarson]

Also, it looks like you didn't give all of the code. But not only that it is very confusing because your code has stuff in places it shouldn't -

Like Public mixed with Dim statements, Option Explicit not in the General Declarations section, etc.

 

[JamesMcS]

And, doesn't it have to be a function instead of a sub to return a value?

 

[boblarson]

And, doesn't it have to be a function instead of a sub to return a value?

Yes, but the OP is using public variables. I think your statement though made me see that it was a sub. But the post of the code was so bad (not using code tags) that it was hard to make out.

I'm wondering where MyQry is being set. We don't see that any place but DestFile looks like it is not going to return the proper thing either.

 

[JamesMcS]

Ah, I did think that after posting... but yes the error message OP is getting will come up, I think, is destfile is blank.

I guessed myqry was a poorly named query that had already been saved, as it wasn't declared in the code. Would it need to be a string containing the SQL code, if it's not saved elsewhere?

 

[boblarson]

Would it need to be a string containing the SQL code, if it's not saved elsewhere?

Nope, it would need to be a name of a saved query or it could be a querydef but it would need the name not the querydef itself. A SQL string would not do in this case.

 

[JamesMcS]

Well then, the OP has some stuff to be getting on with - and I've learned something too! Happy days!