recordset type mismatch

[xsnd47]

I have code:

Private Sub RemoveVialsButton_Click()

Dim frm As Form, ctl As Control
Dim varItm As Variant, sel As Variant
Dim id As Variant, rec As Recordset
Dim db As Database, recnum

Set frm = Forms!Remove
Set ctl = frm!VialSearchResultListBox

'For Each varItm In ctl.ItemsSelected
'ctl.ItemData(varItm))
'Next varItm

If Not IsNull(ctl) Then
sel = ctl.ItemsSelected(0)
Else
MsgBox ("No vials selected.")
End If

id = ctl.ItemData(sel)

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT * FROM Inventory WHERE Inventory.ID = " + id)
MsgBox (rec.RecordCount)

rec.Close

Set rec = Nothing
Set db = Nothing

End Sub

and I get a "Run-time error:'13' Type mismatch" on this line:
Set rec = db.OpenRecordset("SELECT * FROM Inventory WHERE Inventory.ID = " + id)

I'm not sure where I've gone wrong. Any suggestions?
Thanks for the help.

 

[mdmilner]

hmmm

It looks like you're doing DAO not ADO.
Check your references under Tools-References and either make sure that DAO 3.6 (or whichever version of Access you have) is first OR explictly declare your objects as DAO.

Dim db = DAO.Database
Dim rec = DAO.Recordset

Is your ListBox a Multi-Select Listbox or do you have a single item?

If the above isn't it - is your form open when you excecute this code?
If it isn't open, Inventory.ID isn't populated.

Your syntax for concatenating the SQL is correct, but Access may want the & character instead of +. It shouldn't, but it might.

my edit-
Just noticed you must only have a single item selected in your listbox since you're only passing a single value to the ID WHERE Parameter. There's easier syntax to reference all this.

Private Sub RemoveVialsButton_Click() 
Dim db As DAO.Database 
Dim rec As DAO.Recordset 

Set frm = Forms!Remove 

If len(Me.SearchResultListBox.Value) >0 Then
   Set db = CurrentDb()
   Set rec = db.OpenRecordset("SELECT * FROM Inventory WHERE Inventory.ID = " & Me.SearchResultListBox.Value) 
Else
   MsgBox ("No vials selected.") 
End if

MsgBox (rec.RecordCount) 

rec.Close 

Set rec = Nothing 
Set db = Nothing 

End Sub

Hopefully I didn't typo anything or introduce a syntax error.

Mike

 

[xsnd47]

that worked perfectly. thanks.