Share via

access 2010 vba type mismatch

Anonymous
2018-12-05T15:35:11+00:00

In CreateBloom I create and populate a table (BloomArt) with a select statement.  This works great

'-----------------------------

 Public Sub CreateBloom()

'---------------------------------

Set db = CurrentDb

Dim BloomArt As Recordset

Dim StringSQL As String    ' Not used

Dim BAString As String    ' Not used

Dim strSQL As String    ' used to create BloomArt data.

strSQL = "SELECT [ArtworkName],[ArtistName],[Venue] into BloomArt FROM [ArtworkInfoIdx] WHERE ((([Venue])='BloomArt'))"

DoCmd.RunSQL (strSQL)

rs.Close

End Sub  ' end CreateBloom

Next I want to read through the table and loop through the records and I get a type mismatch at the OpenRecordset.   Confused!

'----------------------

Public Sub BloomLoop()

'----------------------

Dim rs As Recordset

Dim BloomArt As Recordset

Set rs = CurrentDb.OpenRecordset("BloomArt")   'type mismatch

If Not (rs.EOF And rs.BOF) Then

    rs.MoveFirst

    Do Until rs.EOF = True

    rs.MoveNext

    Loop

Else

    MsgBox "there are no records in the recordset"

End If

MsgBox "Finished looping through records."

rs.Close 'Close the recordset

Set rs = Nothing 'Clean up

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-12-09T14:53:01+00:00

    I did find my answer in another web site.

    0 comments
  2. Anonymous
    2018-12-05T17:04:17+00:00

    I have tried a lot of things, I don't understand why I didn't seem to need the DAO with the recordset in  "CreateBloom ". 

    Your CreateBloom procedure doesn't ever actually create a recordset; it just executes a SQL statement.  You don't need the declaration of BloomArt in that procedure at all.

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2018-12-05T16:44:03+00:00

    Select Tools > References in the Visual Basic Editor.

    Scroll down the list of references.

    Tick the check box for 'Microsoft Office 14.0 Access database engine Object Library'.

    Click OK.

    Try Debug > Compile … (there will be a name instead of …) to see if the code compiles now.

    0 comments
  4. Anonymous
    2018-12-05T16:09:24+00:00

    There are 2 types of recordsets: ADODB and DAO. With CurrentDb, you need to use the DAO type. To make this explicit, change the line

    Dim BloomArt As Recordset

    to

    Dim BloomArt As DAO.Recordset

    I changed to "Dim BloomArt As DAO.Recordset" Now I get "User-defined type not defined"     I have tried a lot of things, I don't understand why I didn't seem to need the DAO with the recordset in  "CreateBloom ". 

    It seems like any time I try to use DAO it seems to come up with syntax error (compile error) and if I don't use it I get the Mismatch runtime error.

    It seems that I must have to do something on a higher level to be able to set he dao, but I am new to doing this in Access.    I am usually a VB programmer.

    0 comments
  5. HansV 462.6K Reputation points MVP Volunteer Moderator
    2018-12-05T15:41:29+00:00

    There are 2 types of recordsets: ADODB and DAO. With CurrentDb, you need to use the DAO type. To make this explicit, change the line

    Dim BloomArt As Recordset

    to

    Dim BloomArt As DAO.Recordset

    0 comments