type mismatch in recordset definition in ms access

Question

Here is my code:

Dim thisCase As Integer
Dim thisIssueID As Integer
Dim provCase As Integer
Dim rst As Recordset

thisCase = Forms!frmCases.CaseID
thisIssueID = Forms!frmCases![frmissues subform].Form!IssueID

Set rst = CurrentDb.OpenRecordset("select tblissues.caseid from tblcases inner join tblissues on tblcases.caseid = tblIssues.caseid where tblIssues.IssueID = " & thisIssueID) 'CStr(Forms!frmCases![frmissues subform].Form!IssueID))
rst.MoveLast
rst.MoveFirst

Keeps bringing back "Type Mismatch" for the "Set rst =" line. I've run the recordset definition as a query (after removing double quotes and &) and it ran.

both fields being equated are the same datatype (ok autonumber and number). But the query works. I tried using + instead of & and that didn't help.

Any ideas?

Also, in the code, I generated the following string to use instead:

select tblissues.caseid from tblcases inner join tblissues on tblcases.caseid = tblIssues.caseid where tblIssues.IssueID = 24255

which works in a query, but when I put the variable containing the string into the recordset definition, I still get "Type Mismatch". If I try to put single quotes around the value at the end, I get "Data Type Mismatch", so evidently it's some else than a data type mismatch.

Couple suggestions. If you're expecting a DAO.Recordset you should declare it as such. Otherwise if ADO is the default, you need to add "NEW" to the ADO Recordset declaration. Also you can add a 'Debug.Print thisIssueID' before running set recordset command to make sure you're getting what you expect, — dbmitch
I was being lazy, and I've got much more experience with the DAO recordset, I forgot the differences. — David

David David · Accepted Answer · 2018-02-27T21:23:58

As suggested by dbmitch there should be explicit declaration:

Dim rst as DAO.Recordset

instead of:

Dim rst as Recordset

type mismatch in recordset definition in ms access

1 Answers