More QueryDefs and Recordsets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Andersen - 21 Aug 2005 20:44 GMT

I am unable to pass a variable to a Query. How do I pass a value,
programmatically, to a Parameter?

Public Sub ProvidePointer()
Dim mydb As DAO.Database, qdf As DAO.QueryDef, strSQL As String, recv As
DAO.Recordset
Dim CustPointer As Long

CustPointer = 99377
Set mydb = CurrentDb()

DoCmd.Close acQuery, "ArchiveQ"
DoCmd.DeleteObject acQuery, "ArchiveQ"

strSQL = "SELECT * FROM Archive WHERE ([CustPointer] =
[Archive.CUST_NUM]);"

Set qdf = mydb.CreateQueryDef("ArchiveQ", strSQL)
Set recv = qdf.OpenRecordset("ArchiveQ", dbOpenDynaset)
Debug.Print recv.Fields("cust_num")
End Sub

This gives me a "Datatype conversion error" at the point I try to set recv.
Both CustPointer and [CUST_NUM] are type long.

Bill Andersen

Reply to this Message

Douglas J. Steele - 21 Aug 2005 21:00 GMT

You need the value to be in the string, not the name of the variable. The
Jet engine knows nothing about variables that you've defined in your code.

Try:

strSQL = "SELECT * FROM Archive WHERE [Archive.CUST_NUM] =" & CustPointer

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am unable to pass a variable to a Query. How do I pass a value,
> programmatically, to a Parameter?
[quoted text clipped - 24 lines]
>
> Bill Andersen

Reply to this Message

Douglas J. Steele - 21 Aug 2005 21:14 GMT

Just noticed that you had the brackets incorrect. It should be:

strSQL = "SELECT * FROM Archive WHERE [Archive].[CUST_NUM] =" & CustPointer

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> You need the value to be in the string, not the name of the variable. The
> Jet engine knows nothing about variables that you've defined in your code.
[quoted text clipped - 31 lines]
>>
>> Bill Andersen

Reply to this Message

Bill Andersen - 21 Aug 2005 21:46 GMT

I followed your suggestion but I still get a "Data type conversion error"
when I try to open a recordset from the Query.

Bill Andersen

> Just noticed that you had the brackets incorrect. It should be:
>
[quoted text clipped - 35 lines]
> >>
> >> Bill Andersen

Reply to this Message

Douglas J. Steele - 21 Aug 2005 23:17 GMT

Presumably you have a query named ArchiveQ now. What happens when you try to
run it?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I followed your suggestion but I still get a "Data type conversion error"
> when I try to open a recordset from the Query.
[quoted text clipped - 45 lines]
>> >>
>> >> Bill Andersen

Reply to this Message

Bill Andersen - 22 Aug 2005 00:09 GMT

It asks for CustPointer.

Bill Andersen

> Presumably you have a query named ArchiveQ now. What happens when you try to
> run it?
[quoted text clipped - 48 lines]
> >> >>
> >> >> Bill Andersen

Reply to this Message

Douglas J. Steele - 22 Aug 2005 01:18 GMT

That means you haven't changed your code correctly, as it should have a
value, rather than the word "CustPointer" in it.

What did you change your VBA code to? What is the SQL stored in ArchiveQ?

On the other hand, David's eyes were better than mine, and all you need is

Set recv = qdf.OpenRecordset(dbOpenDynaset)

However, if your SQL associated with the query is wrong, that's still not
going to work.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> It asks for CustPointer.
>
[quoted text clipped - 56 lines]
>> >> >>
>> >> >> Bill Andersen

Reply to this Message

David C. Holley - 22 Aug 2005 04:22 GMT

Actually, I've never really used QueryDefs personally - just when I'm
adapting someone else's code and then not really having a solid grasp as
to why the syntax was the way it was, especially setting those d****
parameters. However, it now all makes sense.

> That means you haven't changed your code correctly, as it should have a
> value, rather than the word "CustPointer" in it.
[quoted text clipped - 7 lines]
> However, if your SQL associated with the query is wrong, that's still not
> going to work.

Reply to this Message

David C. Holley - 22 Aug 2005 02:57 GMT

Oh, that's not apparent by the code. Take it then that the code posted
isn't the full procedure?

> It asks for CustPointer.
>
[quoted text clipped - 52 lines]
>>>>>>
>>>>>>Bill Andersen

Reply to this Message

David C. Holley - 21 Aug 2005 23:52 GMT

The .OpenRecordSet method of a QUERYDEF object is slightly different
from a RECORDSET object in that .OpenRecordSet for a QueryDef does not
have a recordset paramater the parameters are actually

Set recordset = object.OpenRecordset (type, options, lockedits)

Therefore the problem is that you're using "ArchiveQ" for the TYPE
parameter which is where things are getting wacked out. Change the
statement to

Set recv = qdf.OpenRecordset(dbOpenDynaset)

And it should work.But...

If you're hardcoding the value, there's no need to set the value in the
line : CustPointer = 99377
You can simply change the code to read

strSQL = "SELECT * FROM Archive WHERE [Archive].[CUST_NUM] = 99377;"

Also, is the query used by other objects in the database? If you're just
snooping around the records returned by the statement, you don't need to
create a new queryDef and then open it. Use the .OpenRecordset method to
retrieve the records as in
Set rs = CurrentDb.OpenRecordset(strSQL, [type], [options], [lockedits])

David H

> I followed your suggestion but I still get a "Data type conversion error"
> when I try to open a recordset from the Query.
[quoted text clipped - 40 lines]
>>>>
>>>>Bill Andersen

Reply to this Message

Bill Andersen - 22 Aug 2005 15:48 GMT

This works!

Public Sub ProvidePointer()
Dim mydb As DAO.Database, qdf As DAO.QueryDef, strSQL As String, recv As
DAO.Recordset
Dim CustPointer As Long

CustPointer = 99377
Set mydb = CurrentDb()

DoCmd.Close acQuery, "ArchiveQ"
DoCmd.DeleteObject acQuery, "ArchiveQ"

strSQL = "SELECT * FROM Archive WHERE [Archive].[CUST_NUM]= " &
CustPointer

Set qdf = mydb.CreateQueryDef("ArchiveQ", strSQL)

Set recv = qdf.OpenRecordset(dbOpenDynaset)

Debug.Print recv.Fields("cust_num"), recv.Fields("invoice_no")

End Sub

Thank you for your help.

Bill Andersen

> The .OpenRecordSet method of a QUERYDEF object is slightly different
> from a RECORDSET object in that .OpenRecordSet for a QueryDef does not
[quoted text clipped - 68 lines]
> >>>>
> >>>>Bill Andersen

Reply to this Message

a_ok2me - 09 Nov 2006 17:37 GMT

Hi,

I have a multiselect list box and I want to pass the selected values as a
new criteria for my query.

The code you provided (below) creates a sample new query with one
destination field and updates the criteria, but how would you update a
criteria for an existing query without having to program all the codes to
recreate the entire query? Can it not be done this easily? - Thanks

Reply to this Message

Douglas J. Steele - 09 Nov 2006 21:44 GMT

Sorry, with a multi-select listbox, you have no choice but to regenerate the
SQL in code.

Check http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for
one approach to generating the SQL.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi,
>
[quoted text clipped - 44 lines]
>> >
>> > Bill Andersen

Reply to this Message

a_ok2me - 15 Nov 2006 19:51 GMT

Doug - Thanks.

> Sorry, with a multi-select listbox, you have no choice but to regenerate the
> SQL in code.
[quoted text clipped - 50 lines]
> >> >
> >> > Bill Andersen

Reply to this Message