Error 13 - Type Mismatch on Recordset

[PatAccess]

Hello Guys,

I am trying to get a button to export the values in a listbox to excel. After extensive research on this forum and others, I came up with the code below. However, I am getting Error 13 on this line: Set rs = db.CreateQueryDef("Query1", strSQLFolder)

What am I doing wrong? :banghead:

Private Sub cmdExportListBoxValues_Click()
Dim db As DAO.Database
Dim rs As DAO.QueryDefs
Dim strSQLFolder As String

strSQLFolder = "SELECT FolderName,Categories,ThemeName FROM QryPhotosLocationRecords WHERE FolderName='" & Me.cboFolders.Column(1) & "' ORDER BY Categories"

Set db = CurrentDb
On Error Resume Next
db.QueryDefs.Delete ("Query1")
On Error GoTo 0

Set rs = db.CreateQueryDef("Query1", strSQLFolder)

Me.ltboxLocation.RowSource = "Query1"
DoCmd.TransferSpreadsheet acImport, , , "Query1"

End Sub

Thank you

 

[Minty]

The possibly obvious question is what is the data in Me.cboFolders.Column(1)

add the following and look in the immediate window after the error appears;

[COLOR="Red"]Debug.print strSQLFolder[/COLOR]
Set rs = db.CreateQueryDef("Query1", strSQLFolder)

Edit : And shouldn't that be acExport in the last line if you are trying to export it ???

 

[theDBguy]

Hi. Also, you could be getting that error due to your variable declaration of:


Dim rs As DAO.QueryDefs

Try using this instead:

Dim rs As DAO.QueryDef

(without an "s" at the end)

 

[PatAccess]

The possibly obvious question is what is the data in Me.cboFolders.Column(1)

add the following and look in the immediate window after the error appears;

[COLOR="Red"]Debug.print strSQLFolder[/COLOR]
Set rs = db.CreateQueryDef("Query1", strSQLFolder)

Here's what comes up in the immediate window: SELECT FolderName,Categories,ThemeName FROM QryPhotosLocationRecords WHERE FolderName='CollegeWmMary' ORDER BY Categories

 

[PatAccess]

Hi. Also, you could be getting that error due to your variable declaration of:


Dim rs As DAO.QueryDefs

Try using this instead:

Dim rs As DAO.QueryDef

(without an "s" at the end)

Now it is say that this action (DoCmd.TransferSpreadsheet acImport, , , "Query1") requires a table name?

 

[The_Doc_Man]

My best advice is to put a breakpont on the line "Set db = CurrentDB" and then do a Debug.Print of your variable strSQLFolder to see if it has a problem with quotes or something like that, because offhand, I don't see a major error. But since you DO have a substitution in that string, it might be hosed up in some way.

There is also the fact that you have an "On Error Resume Next", which would prevent you from seeing if you had an error in any of the precursor steps. That might also contribute to the problem.

EDIT: Sheesh, looks like everyone jumped on this one at the same time.

 

[theDBguy]

Now it is say that this action (DoCmd.TransferSpreadsheet acImport, , , "Query1") requires a table name?

Yes, you cannot import external data into a query. You'll have to use a table.

 

[PatAccess]

My best advice is to put a breakpont on the line "Set db = CurrentDB" and then do a Debug.Print of your variable strSQLFolder to see if it has a problem with quotes or something like that, because offhand, I don't see a major error. But since you DO have a substitution in that string, it might be hosed up in some way.

There is also the fact that you have an "On Error Resume Next", which would prevent you from seeing if you had an error in any of the precursor steps. That might also contribute to the problem.

EDIT: Sheesh, looks like everyone jumped on this one at the same time.

When I remove the "On Error Resume Next" the error is now that "Query1" already exist and when I step through the the code, I see nothing for "Query1" but strSQLFolder shows the correct value. I'm very confused now

 

[PatAccess]

Yes, you cannot import external data into a query. You'll have to use a table.

I actually made a mistake so I changed it to acExport but still the same. I changed to QueryDefs as Microsoft required but still with error messages. I'm trying to read more on it.

 

[theDBguy]

I actually made a mistake so I changed it to acExport but still the same. I changed to QueryDefs as Microsoft required but still with error messages. I'm trying to read more on it.

Hi. Since we can't see what you're looking at to be able to tell what's happening, are you able to post a demo version of your db?

 

[The_Doc_Man]

When I remove the "On Error Resume Next" the error is now that "Query1" already exist

Was that error thrown from the attempt to delete the querydef or the attempt to make the new querydef? If from the .CreateQueryDef then rather obviously the attempt to delete the prior incarnation of said QueryDef didn't work. A breakpoint immediately after the attempt to delete the querydef would let you examine the Err object (if it exists) and see if there is an error after that QueryDefs.Delete operation.