Query Problems

[Gibson]

I am using the following code to set up a recordset of a query. The query
SQL is below. The problem is when I try to set a variable to the value of
Field3 an error tells me InvLine is not in the collection. The first two
fields,Field1 and Field2 are there. In the locals window when I run the code
it indicates there are only 2 items in the collection, the first two fields.
Can anyone see what I'm doing wrong with this code?

Set qdef2 = db.QueryDefs("qry2")
Set parm2 = qdef2.Parameters(0)
'Set parameter value
parm2 = strField1
'Execute QueryDef to produce a recordset of qry2 which
Set rsAII2 = qdef.OpenRecordset


SELECT TblInvDet.Field1, TblInvDet.Field2, TblInvDet.Field3
FROM TblInvDet
ORDER BY Val([TblInvDet.Field2]), TblInvDet.Field3;

 

[Marshall Barton]

I am using the following code to set up a recordset of a query. The query
SQL is below. The problem is when I try to set a variable to the value of
Field3 an error tells me InvLine is not in the collection. The first two
fields,Field1 and Field2 are there. In the locals window when I run the code
it indicates there are only 2 items in the collection, the first two fields.
Can anyone see what I'm doing wrong with this code?

Set qdef2 = db.QueryDefs("qry2")
Set parm2 = qdef2.Parameters(0)
'Set parameter value
parm2 = strField1
'Execute QueryDef to produce a recordset of qry2 which
Set rsAII2 = qdef.OpenRecordset


SELECT TblInvDet.Field1, TblInvDet.Field2, TblInvDet.Field3
FROM TblInvDet
ORDER BY Val([TblInvDet.Field2]), TblInvDet.Field3;

The fields collection starts with index 0, so the third
field is rs.Fields(2)

OTOH, maybe you are using rs.field3 when it must be
rs!field3

OTOOH, maybe you should be using field3 instead of InvLine

or maybe this or maybe that.

Might've helped us figure it out if you had also posted the
code that's having the problem.

 

[Gibson]

I apologize for not including the code. I thought I had . I apparently have
been staring at this code too long!
Anyway, following is the code causing the problem. The code bombs out in the
For each loop. I have two variables that I set to Fields 1 and 2 in the
code. In the query that forms the recordset in the criteria field I have
"Alike [Variable1] and in Field 2 I have the same thing but Variable2. My
intention is to run the query and find the records where Field 1 and 2 in
the query equals variable 1 and 2 that I set. I have never done this before
so I may be a few "miles" off. Thanks for any quidance.


Set qdf = db.QueryDefs(stDocName)
Set prm = qdf.Parameters(0)
'Set parameter value
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Value)
Next prm
''Execute QueryDef to produce a recordset
Set rs = qdf.OpenRecordset

I am using the following code to set up a recordset of a query. The query
SQL is below. The problem is when I try to set a variable to the value of
Field3 an error tells me InvLine is not in the collection. The first two
fields,Field1 and Field2 are there. In the locals window when I run the
code
it indicates there are only 2 items in the collection, the first two
fields.
Can anyone see what I'm doing wrong with this code?

Set qdef2 = db.QueryDefs("qry2")
Set parm2 = qdef2.Parameters(0)
'Set parameter value
parm2 = strField1
'Execute QueryDef to produce a recordset of qry2 which
Set rsAII2 = qdef.OpenRecordset


SELECT TblInvDet.Field1, TblInvDet.Field2, TblInvDet.Field3
FROM TblInvDet
ORDER BY Val([TblInvDet.Field2]), TblInvDet.Field3;

The fields collection starts with index 0, so the third
field is rs.Fields(2)

OTOH, maybe you are using rs.field3 when it must be
rs!field3

OTOOH, maybe you should be using field3 instead of InvLine

or maybe this or maybe that.

Might've helped us figure it out if you had also posted the
code that's having the problem.

 

[Marshall Barton]

Either you're not giving me the full story or you really are
miles off ;-)

The query you posted earlier has no parameters, so I don't
see how this code can do anything useful. Maybe the query
you're talking about today is a different query??

In any case, you can not use Eval on a VBA variable or
arbitray prompt string. Ot only works for parameters that
are like Forms!formname.controlname.

I think I need to see the actual query and the code that
precedes the code you posted.
--
Marsh
MVP [MS Access]

I apologize for not including the code. I thought I had . I apparently have
been staring at this code too long!
Anyway, following is the code causing the problem. The code bombs out in the
For each loop. I have two variables that I set to Fields 1 and 2 in the
code. In the query that forms the recordset in the criteria field I have
"Alike [Variable1] and in Field 2 I have the same thing but Variable2. My
intention is to run the query and find the records where Field 1 and 2 in
the query equals variable 1 and 2 that I set. I have never done this before
so I may be a few "miles" off. Thanks for any quidance.


Set qdf = db.QueryDefs(stDocName)
Set prm = qdf.Parameters(0)
'Set parameter value
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Value)
Next prm
''Execute QueryDef to produce a recordset
Set rs = qdf.OpenRecordset

I am using the following code to set up a recordset of a query. The query
SQL is below. The problem is when I try to set a variable to the value of
Field3 an error tells me InvLine is not in the collection. The first two
fields,Field1 and Field2 are there. In the locals window when I run the
code
it indicates there are only 2 items in the collection, the first two
fields.
Can anyone see what I'm doing wrong with this code?

Set qdef2 = db.QueryDefs("qry2")
Set parm2 = qdef2.Parameters(0)
'Set parameter value
parm2 = strField1
'Execute QueryDef to produce a recordset of qry2 which
Set rsAII2 = qdef.OpenRecordset


SELECT TblInvDet.Field1, TblInvDet.Field2, TblInvDet.Field3
FROM TblInvDet
ORDER BY Val([TblInvDet.Field2]), TblInvDet.Field3;

"Marshall Barton" wrote
The fields collection starts with index 0, so the third
field is rs.Fields(2)

OTOH, maybe you are using rs.field3 when it must be
rs!field3

OTOOH, maybe you should be using field3 instead of InvLine

or maybe this or maybe that.

Might've helped us figure it out if you had also posted the
code that's having the problem.

 

[Gibson]

We were both right. I was miles off! After your last post, and a few hours
of sleep, I made some changes including putting in the parameters and it
works fine. Thanks for your help and patience.

Eddy

Either you're not giving me the full story or you really are
miles off ;-)

The query you posted earlier has no parameters, so I don't
see how this code can do anything useful. Maybe the query
you're talking about today is a different query??

In any case, you can not use Eval on a VBA variable or
arbitray prompt string. Ot only works for parameters that
are like Forms!formname.controlname.

I think I need to see the actual query and the code that
precedes the code you posted.
--
Marsh
MVP [MS Access]

I apologize for not including the code. I thought I had . I apparently
have
been staring at this code too long!
Anyway, following is the code causing the problem. The code bombs out in
the
For each loop. I have two variables that I set to Fields 1 and 2 in the
code. In the query that forms the recordset in the criteria field I have
"Alike [Variable1] and in Field 2 I have the same thing but Variable2. My
intention is to run the query and find the records where Field 1 and 2 in
the query equals variable 1 and 2 that I set. I have never done this
before
so I may be a few "miles" off. Thanks for any quidance.


Set qdf = db.QueryDefs(stDocName)
Set prm = qdf.Parameters(0)
'Set parameter value
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Value)
Next prm
''Execute QueryDef to produce a recordset
Set rs = qdf.OpenRecordset

Gibson wrote:
I am using the following code to set up a recordset of a query. The
query
SQL is below. The problem is when I try to set a variable to the value
of
Field3 an error tells me InvLine is not in the collection. The first two
fields,Field1 and Field2 are there. In the locals window when I run the
code
it indicates there are only 2 items in the collection, the first two
fields.
Can anyone see what I'm doing wrong with this code?

Set qdef2 = db.QueryDefs("qry2")
Set parm2 = qdef2.Parameters(0)
'Set parameter value
parm2 = strField1
'Execute QueryDef to produce a recordset of qry2 which
Set rsAII2 = qdef.OpenRecordset


SELECT TblInvDet.Field1, TblInvDet.Field2, TblInvDet.Field3
FROM TblInvDet
ORDER BY Val([TblInvDet.Field2]), TblInvDet.Field3;

"Marshall Barton" wrote
The fields collection starts with index 0, so the third
field is rs.Fields(2)

OTOH, maybe you are using rs.field3 when it must be
rs!field3

OTOOH, maybe you should be using field3 instead of InvLine

or maybe this or maybe that.

Might've helped us figure it out if you had also posted the
code that's having the problem.