Hello there,
This code wont let me declare rs as a recordset, it will only work if I declare it as a variant. It gives a Type Mismatch error.
I have used this code in other projects as a recordset. (I think)
What have I missed?
Is there a problem if I leave it as a variant anyway?

It is merely grabbing a record from Access to display in a Excel user form, selecting from a listbox on the same form.
Excel 2003, Access 2003, VBA.

HTML Code:
Public Sub WholeOfVolly()
'Populates the viewing form with all the chosen volunteers data

Dim a As Integer, i As Integer, dbid As Integer, wholename As String
Dim db As Database, qry As String, rs As Variant

'Get database record ID, a, from selected row
For a = 0 To frmVollyView.ListBox2.ListCount - 1
    If frmVollyView.ListBox2.Selected(a) Then
        dbid = frmVollyView.ListBox2.Column(3, a)
    End If
Next

'Populate text boxes with chosen volly data
    Worksheets("sheet1").Range("a1").Value = Application.ActiveWorkbook.Path & "\"
    wholename = Worksheets("sheet1").Range("a1").Value & Worksheets("sheet1").Range("a2").Value
Set db = OpenDatabase(wholename, False, False, "MS Access; pwd=xxxx")
    qry = "select * from tblVolunteerDetails where ID = " & dbid
Set rs = db.OpenRecordset(qry)
If rs.RecordCount <> 0 Then
With frmVollyView
        .lblThId.Caption = "ID: " & rs.Fields("thid")
        .lblName.Caption = "" & rs.Fields("title") & " " & rs.Fields("givenname") & " " & rs.Fields("surname")
        .lblRegion.Caption = "" & rs.Fields("region")
        .lblThStatus.Caption = "" & rs.Fields("thstatus")
        .lblAdd1.Caption = "" & rs.Fields("raddline1")
        .lblAdd2.Caption = "" & rs.Fields("raddline2")
        .lblAdd3.Caption = "" & rs.Fields("raddtown")
        .lblAdd4.Caption = "" & rs.Fields("raddstate") & " " & rs.Fields("raddpc")
        .lblAdd5.Caption = "" & rs.Fields("paddline1")
        .lblAdd6.Caption = "" & rs.Fields("paddline2")
        .lblAdd7.Caption = "" & rs.Fields("paddtown")
        .lblAdd8.Caption = "" & rs.Fields("paddstate") & " " & rs.Fields("paddpc")
        .lblPhnNum1.Caption = "" & rs.Fields("phone1code") & " " & rs.Fields("phone1num")
        .lblPhnNum2.Caption = "" & rs.Fields("phone2code") & " " & rs.Fields("phone2num")
        .lblMobNum1.Caption = "" & rs.Fields("mobile1")
        .lblMobNum2.Caption = "" & rs.Fields("mobile2")
        .lblEml1.Caption = "" & rs.Fields("email1")
        .lblEml2.Caption = "" & rs.Fields("email2")

End With
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub