Stumped by a Type mismatch error

  • Thread starter Thread starter Len B
  • Start date Start date
L

Len B

Relevant VBA snippets -

DIM dbsTadPJ as Database, rstPJ as Recordset, stSQLExport as String
stSQLExport = "SELECT SupplierInvoice ....
stSQLExport = stSQLExport & "FROM Purchases INNER JOIN ...
stSQLExport = stSQLExport & "GROUP BY ...

Set dbsTadPJ = CurrentDb()
Set rstPJ = dbsTadPJ.OpenRecordset(stSQLExport, dbOpenDynaset) ****

Line **** generates Type mismatch error. I have used this syntax
successfully elsewhere.

By using a breakpoint and the intermediate window, I got the SQL string
produced by the concatenation process into the clipboard. I then pasted
it into a new query which produced the 13 records I was expecting.

As far as I can see I am attempting to assign a recordset to a variable
of type recordset. Aren't I? Any hints what to try now.
 
Len said:
DIM dbsTadPJ as Database, rstPJ as Recordset, stSQLExport as String
Click to expand...

You've referenced 2 libraries with the same object name, so you have to
tell Access which library your code is using. It defaults to the first
one on the list. In your case, that's the ADODB library, which you
aren't using in this procedure. Change:

Dim rstPJ as Recordset

to:

Dim rstPJ as DAO.Recordset
 
That did the trick. Thanks.

--
Len
______________________________________________________
remove nothing for valid email address.
| Len B wrote:
|
| > DIM dbsTadPJ as Database, rstPJ as Recordset, stSQLExport as String
|
| You've referenced 2 libraries with the same object name, so you have to
| tell Access which library your code is using. It defaults to the first
| one on the list. In your case, that's the ADODB library, which you
| aren't using in this procedure. Change:
|
| Dim rstPJ as Recordset
|
| to:
|
| Dim rstPJ as DAO.Recordset
 
Back
Top