type mismatch in recordset definition in ms access


Keywords: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.


1 Answer: 

As suggested by dbmitch there should be explicit declaration:

Dim rst as DAO.Recordset

instead of:

Dim rst as Recordset