More QueryDefs and Recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Just noticed that you had the brackets incorrect. It should be:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
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
Click to expand...
Click to expand...
 
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


Douglas J. Steele said:
Just noticed that you had the brackets incorrect. It should be:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
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
Click to expand...
Click to expand...
Click to expand...
 
Presumably you have a query named ArchiveQ now. What happens when you try to
run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
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


Douglas J. Steele said:
Just noticed that you had the brackets incorrect. It should be:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
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
Click to expand...
Click to expand...
Click to expand...
 
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


Bill said:
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:

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

--
Doug Steele, Microsoft Access MVP

(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.

Try:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




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
Click to expand...
Click to expand...
Click to expand...
 
It asks for CustPointer.

Bill Andersen


Douglas J. Steele said:
Presumably you have a query named ArchiveQ now. What happens when you try to
run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
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


Douglas J. Steele said:
Just noticed that you had the brackets incorrect. It should be:

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

--
Doug Steele, Microsoft Access MVP

(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.

Try:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
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
Click to expand...
Click to expand...
Click to expand...
 
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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
It asks for CustPointer.

Bill Andersen


Douglas J. Steele said:
Presumably you have a query named ArchiveQ now. What happens when you try
to
run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
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:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
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
Click to expand...
Click to expand...
Click to expand...
 
Oh, that's not apparent by the code. Take it then that the code posted
isn't the full procedure?

Bill said:
It asks for CustPointer.

Bill Andersen


:

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

--
Doug Steele, Microsoft Access MVP

(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.

Bill Andersen


:


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

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

--
Doug Steele, Microsoft Access MVP

(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.

Try:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message

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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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

David C. Holley said:
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


Bill said:
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:

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

--
Doug Steele, Microsoft Access MVP

(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.

Try:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




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
Click to expand...
Click to expand...
Click to expand...
 
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


Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
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
Click to expand...
Click to expand...
 
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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


a_ok2me said:
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


Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bill Andersen said:
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
Click to expand...
Click to expand...
Click to expand...
 
Doug - Thanks.

Douglas J. Steele said:
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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


a_ok2me said:
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


Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top