QueryDef is driving me nuts!

[jimbojr]

Friends,

Hope you can shed some light on this problem. I'm trying to create a recordset from a QueryDef with one parameter.

I give the user a List box (List1) to select a value that becomes the parameter for a select query that's defined in a QueryDef. Here's the code I'm executing in a Form module...

'look for upstreams EBP in each selected EBP in the listbox
    For Each idx In List1.ItemsSelected
       TargetEBP = List1.ItemData(idx)
        
        'open recordset to read upstream ebp's
        qdf.Parameters("param1") = TargetEBP
        Set rst = qdf.OpenRecordset(dbOpenForwardOnly)
      ...snip...
    Next

And, here's the contents of the QueryDef...

?qdf.SQL
SELECT Upstream.EBPID, Upstream.UpstreamEBP, Upstream.ANDGATE, Upstream.XORGATE
FROM Upstream
WHERE (((Upstream.EBPID)=[param1]));

Everytime I attempt the OpenRecordset command, I get an Error 13, 'Type Mistmatch'. I've check numerous times that Upstream.EBPID is text and that the parameter, set to TargetEBP, is a string.

Any ideas?

Jimbo

 

[ddeldeb]

I didn't test this quess, but if [Param1] is text, I think you need to place single quotes around it or SQL will treat it like a number

Good Luck Dave,,

 

[jimbojr]

Dave,

Thanks for the help.

I found the root of my problem to be a very tricky problem with the object libraries loaded in MS Access. I'm running MS Access 2000 and had references set to libraries for DAO *and* ADO. When I tried opening recordsets, I believe access was confused as to which Recordset object I was requesting, hence, the type mismatch error.

I removed unneeded ADO libraries, and selected the correct Recordset object from the correct library to use with OpenRecordset. That solved the problem.

Boy, that was a tough one to figure out. Overloading object names is very confusing.

Jim

 

[mdmilner]

You can also explicitly declare DAO, ADO when declaring your recordsets.
I always make a habit of explicity declaring it regardless of which references I am supposed to have loaded.

Just do things like.

Dim rs As DAO.Recordset

instead of just

Dim rs As Recordset

Mike