type mismatch in VB code

[Garyhou]

I have a command button which sets up an email list based on names selected from a RSVP list. I had this working for awhile but now I get a "type mismatch" code error when I try to run it. The code is shown below and the type mismatch occurs on the following line: Set RS = mydb.OpenRecordset("rsvp_query")

Any thoughts on how to correct this?

Thanks Garyhou

Private Sub emailRSVP_Click()

Dim mydb As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
Dim strTo As String
Dim strlist As String


Set mydb = DBEngine.Workspaces(0).Databases(0)

Set RS = mydb.OpenRecordset _
("rsvp_query")
lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No member email addresses found.", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
lngCount = lngCount + 1
If RS!rsvp = -1 Then strlist = strlist & RS!email_addr & ";"
strlist = Left$(strlist, Len(strlist) - 1)
Me!txtselected = strlist

RS.MoveNext
Loop

End If
RS.close
mydb.close
Set RS = Nothing
Set mydb = Nothing
Close

End Sub

 

[kjv1611]

Is this database reference a reference to your current database in Microsoft Access?

If so, I'd suggest changing your references to DAO, and change the specific database reference to CurrentDb.

So, it would then be something like this:

Private Sub emailRSVP_Click()

    Dim mydb As [blue]DAO.[/blue]Database, RS As [blue]DAO.[/blue]Recordset
    Dim strBody As String, lngCount As Long, lngRSCount As Long
    Dim strTo As String
    Dim strlist As String
    
    
    Set mydb = DBEngine.[blue]CurrentDb[/blue]

   Set RS = mydb.OpenRecordset _
      ("rsvp_query")
    lngRSCount = RS.RecordCount
    If lngRSCount = 0 Then
      MsgBox "No member email addresses found.", vbInformation
    Else
      RS.MoveLast
      RS.MoveFirst
      Do Until RS.EOF
        lngCount = lngCount + 1
        If RS!rsvp = -1 Then strlist = strlist & RS!email_addr & ";"
        strlist = Left$(strlist, Len(strlist) - 1)
        Me!txtselected = strlist

        RS.MoveNext
      Loop
    
    End If
    RS.close
    mydb.close
    Set RS = Nothing
    Set mydb = Nothing
    Close

End Sub

 

[PHV]

Dim mydb As Database, RS As [!]DAO.[/!]Recordset
...
Set mydb = [!]Application.CurrentDb[/!]
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gwinn7]

Two things.
(A) Try "SELECT * FROM rsvp_query" instead of "rsvp_query".
(B) Your code is not very elegant, or reliable, instead try something like...

Dim mydb As DAO.Database
Dim rs As DAO.Recordset
Dim strBody As String
Dim strTo As String
Dim strlist As String

Set mydb = currentdb
Set rs =
mydb.OpenRecordset("SELECT * FROM rsvp_query", dbopensnapshot)

if rs.bof = true then
MsgBox "No member email addresses found.", vbinformation
else
Do Until rs.EOF
If rs!rsvp = -1 Then
strlist = strlist & rs! email_addr & ";"
strlist = Left$(strlist, Len(strlist) - 1)
Me!txtselected = strlist
end if
rs.MoveNext
Loop

End If
rs.close
mydb.close
Set rs = Nothing
Set mydb = Nothing

-----------------------------------
Here is why...

1. Fewer variables used, less memory
2. RecordCount is only reliable when the end of the recordset has been visited. Don't bother to use it here!
3. If your recordset opens to rs.BOF=TRUE then there are NO records to process! Very easy!
4. Fewer variable assignments, faster performance!
5. For reads, use dbforwardonly or dbsnapshot, MUCH FASTER! By default, your opened your recordset as dbDynaset, which is slower.

Gary
gwinn7

 

[Garyhou]

Thanks all - I got it working!!!

 

[kjv1611]

Garyhou,

What changes did you end up making that corrected your issue(s)?

 

[Garyhou]

I went with gwinn7's recommendation. Thanks gwinn7.