OpenRecordset problem (parameter query)

[Masoud]

In my code, I used openrecordset for opening parameter query like below but
it has error. Value of my parameter there is in my open form that manually
when I open this parameter query this works.
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryCcmaillisttranstocon").

Sql of this This parameter query is like below :
SELECT tbltransCc.TRANSMITtoCon, tbltransCc.CC, tbltransCc.Copies,
tblCc.CcAddress
FROM tblCc INNER JOIN tbltransCc ON tblCc.Cc = tbltransCc.CC
WHERE (((tbltransCc.TRANSMITtoCon)=[Forms]![frmTranstoCon]![TRANSMITtoCon]));
Please help.

 

[Alex Dybenko]

Hi,
you have to pass parameter value to your query, see "Parameter Object,
Parameters Collection Example" in Access help

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[JimBurke via AccessMonster.com]

What is the error message you're getting?

I'm assuming that this is a 'stored' query, and you went into the query,
clicked on View, VIew SQL, then copied and pasted that SQL here. And when
viweing the query in design mode, the criteria value for the 'TRANSMITtoCon'
column shows [Forms]![frmTranstoCon]![TRANSMITtoCon]. Is this right? If so,
the typical reason for getting an error on this type of query is either that
the form and/or control name is wrong, the form is not open, or the form is
open but the control has no value, or the value is not the correct type (e.g.
the column is a numeric type and the value is non-numeric). I would put a
msgbox right before opening the query to see if that form control is
specified correctly and that it has an appropriate value:

msgbox [Forms]![frmTranstoCon]![TRANSMITtoCon]
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryCcmaillisttranstocon")

If the msgbox shows a valid value for the field then I'm not sure what the
problem might be.
I will say that I have never used OpenRecordset to open queries - I've always
used the format
of rs.OpenQuery 'query name', 'connection value',...., so I don't know if
there's anything you need to do differently when using a form control value
for a criteria value when you open the query via OpenRecordset.

In my code, I used openrecordset for opening parameter query like below but
it has error. Value of my parameter there is in my open form that manually
when I open this parameter query this works.
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryCcmaillisttranstocon").

Sql of this This parameter query is like below :
SELECT tbltransCc.TRANSMITtoCon, tbltransCc.CC, tbltransCc.Copies,
tblCc.CcAddress
FROM tblCc INNER JOIN tbltransCc ON tblCc.Cc = tbltransCc.CC
WHERE (((tbltransCc.TRANSMITtoCon)=[Forms]![frmTranstoCon]![TRANSMITtoCon]));
Please help.

 

[Masoud]

hello,
i found my answer in another answers and used the code from marshal about
parameter query like below and it solved my problem.

The big difference is that when you just open the query from
the db window or use the RunSQL method, you are asking
Access to open the recordset for you. As part of that
procedd, Access will resolve the query parameters.

However, when you use DAO (or some other direct to the db
engine interface), Access is not involved, so you must
resolve the parameters before opening the recordset.

Then general code outline is:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset


Set db = CurrentDb()
Set qdf = db.QueryDefs!queryname
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name) 'different for prompts
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

--
Marsh
MVP [MS Access]

What is the error message you're getting?

I'm assuming that this is a 'stored' query, and you went into the query,
clicked on View, VIew SQL, then copied and pasted that SQL here. And when
viweing the query in design mode, the criteria value for the 'TRANSMITtoCon'
column shows [Forms]![frmTranstoCon]![TRANSMITtoCon]. Is this right? If so,
the typical reason for getting an error on this type of query is either that
the form and/or control name is wrong, the form is not open, or the form is
open but the control has no value, or the value is not the correct type (e.g.
the column is a numeric type and the value is non-numeric). I would put a
msgbox right before opening the query to see if that form control is
specified correctly and that it has an appropriate value:

msgbox [Forms]![frmTranstoCon]![TRANSMITtoCon]
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryCcmaillisttranstocon")

If the msgbox shows a valid value for the field then I'm not sure what the
problem might be.
I will say that I have never used OpenRecordset to open queries - I've always
used the format
of rs.OpenQuery 'query name', 'connection value',...., so I don't know if
there's anything you need to do differently when using a form control value
for a criteria value when you open the query via OpenRecordset.

In my code, I used openrecordset for opening parameter query like below but
it has error. Value of my parameter there is in my open form that manually
when I open this parameter query this works.
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryCcmaillisttranstocon").

Sql of this This parameter query is like below :
SELECT tbltransCc.TRANSMITtoCon, tbltransCc.CC, tbltransCc.Copies,
tblCc.CcAddress
FROM tblCc INNER JOIN tbltransCc ON tblCc.Cc = tbltransCc.CC
WHERE (((tbltransCc.TRANSMITtoCon)=[Forms]![frmTranstoCon]![TRANSMITtoCon]));
Please help.