Lit box problems...

I've got an unbound listbox on a form, and for the item that's currently
selected from the listbox, I'm trying to find out how many exist in the
underlying table (i.e. the same as rst.recordcount)

The field I'm using is called CN and is an integer

I'm having problems with the following code and getting a 'Type mismatch'
error message.

Anyone know what I'm doing wrong?

Thanks for any help.

-------------code snippet--------------
For Each varItem In Me.list_main.ItemsSelected

strWhere = "CN = " & Me.list_main.ItemData(varItem) & ""

Next varItem

strSQL = "SELECT CN FROM tblRegistration "
strSQL = strSQL & "WHERE " & strWhere

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
------------------------------

Hi,
If it's an integer, use this:
strWhere = "CN = " & Me.list_main.ItemData(varItem)

HTH
Dan Artuso, MVP

"Mo" wrote in message ...
I've got an unbound listbox on a form, and for the item that's currently
selected from the listbox, I'm trying to find out how many exist in the
underlying table (i.e. the same as rst.recordcount)

The field I'm using is called CN and is an integer

I'm having problems with the following code and getting a 'Type mismatch'
error message.

Anyone know what I'm doing wrong?

Thanks for any help.

-------------code snippet--------------
For Each varItem In Me.list_main.ItemsSelected

strWhere = "CN = " & Me.list_main.ItemData(varItem) & ""

Next varItem

strSQL = "SELECT CN FROM tblRegistration "
strSQL = strSQL & "WHERE " & strWhere

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
------------------------------

Hello Dan,

I've tried that particular thing before and I get the same error message
'Type mismatch'.

Any other ideas?

"Dan Artuso" wrote in message
...
Hi,
If it's an integer, use this:
strWhere = "CN = " & Me.list_main.ItemData(varItem)

HTH
Dan Artuso, MVP

OK Dan, sorted. Changed this:

Dim db As Database
Dim rst As Recordset

to this:

Dim db As DAO.Database
Dim rst As DAO.Recordset

and it now works. Wierd!!

"Dan Artuso" wrote in message
...
Hi,
If it's an integer, use this:
strWhere = "CN = " & Me.list_main.ItemData(varItem)

HTH
Dan Artuso, MVP

Hi,
It's not wierd at all. If you have a reference to both ADO and DAO, they both have
recordset objects. If you don't use the DAO qualifier (or any qualifier) Access will pick the
library that is referenced first, in this case ADO. Hence the type mismatch.

When posting, you should always give the line that is causing the error!

Dan Artuso, MVP

"Mo" wrote in message ...
OK Dan, sorted. Changed this:

Dim db As Database
Dim rst As Recordset

to this:

Dim db As DAO.Database
Dim rst As DAO.Recordset

and it now works. Wierd!!

"Dan Artuso" wrote in message
...
Hi,
If it's an integer, use this:
strWhere = "CN = " & Me.list_main.ItemData(varItem)

HTH
Dan Artuso, MVP