It's been several months since I programmed in VBA (and
then in Access 2000), but it seems to me that this used to
work. Has DAO changed somehow with Access 2002?

Private Sub btnConvert_Click()

Dim Rst As Recordset
Dim Qdf As QueryDef
Dim Parm As Parameter
Dim ORst As Recordset
Dim Db As Database

Set Db = CurrentDb()
Set Qdf = Db.QueryDefs("qry_Delete_OrdersNormal_by_Year")
Set Parm = Qdf.Parameters![Year?]
Parm = Me.tbxYear
Qdf.Execute

Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
Qdf.Parameters(0).Value = Me.tbxYear
'Set Parm = Qdf.Parameters![Year?]
'Parm = Me.tbxYear
Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

1) Setting the value of a Query parameter doesn't work
the way I remember. What's wrong with the syntax in the
first attempt to set the Parm variable to the query parm...

Set Parm = Qdf.Parameters![Year?]

The query has a parm [Year?] set up for one column in the
base table. I get a "type mismatch" error on this line if
I use this syntax.

2) OK, no big deal, I can change to more awkward syntax
if I have to in setting the parm. In the second instance
of setting the Parm...

Qdf.Parameters(0) = Me.tbxYear ' (a form text box)

This works. But now on the immediately following line

Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

I get a "type mismatch" error on this line. Why!!!?

Thanks for your help.

Steve

PS. the column that the [Year?] query parm is in is an
integer value (but this never seemed to matter before and
I get the last crash even if I try to convert the value of
tbxYear to an Int before I set the parm).

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


"Steve W." <(E-Mail Removed)> wrote in message
news:1a8ff01c387b4$7c1af3b0$(E-Mail Removed)...
> It's been several months since I programmed in VBA (and
> then in Access 2000), but it seems to me that this used to
> work. Has DAO changed somehow with Access 2002?
>
> Private Sub btnConvert_Click()
>
> Dim Rst As Recordset
> Dim Qdf As QueryDef
> Dim Parm As Parameter
> Dim ORst As Recordset
> Dim Db As Database
>
> Set Db = CurrentDb()
> Set Qdf = Db.QueryDefs("qry_Delete_OrdersNormal_by_Year")
> Set Parm = Qdf.Parameters![Year?]
> Parm = Me.tbxYear
> Qdf.Execute
>
> Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
> Qdf.Parameters(0).Value = Me.tbxYear
> 'Set Parm = Qdf.Parameters![Year?]
> 'Parm = Me.tbxYear
> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>
> 1) Setting the value of a Query parameter doesn't work
> the way I remember. What's wrong with the syntax in the
> first attempt to set the Parm variable to the query parm...
>
> Set Parm = Qdf.Parameters![Year?]
>
> The query has a parm [Year?] set up for one column in the
> base table. I get a "type mismatch" error on this line if
> I use this syntax.
>
> 2) OK, no big deal, I can change to more awkward syntax
> if I have to in setting the parm. In the second instance
> of setting the Parm...
>
> Qdf.Parameters(0) = Me.tbxYear ' (a form text box)
>
> This works. But now on the immediately following line
>
> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>
> I get a "type mismatch" error on this line. Why!!!?
>
> Thanks for your help.
>
> Steve
>
> PS. the column that the [Year?] query parm is in is an
> integer value (but this never seemed to matter before and
> I get the last crash even if I try to convert the value of
> tbxYear to an Int before I set the parm).

You probably have not set a reference to DAO. See Tools|References while in
VBA Editor. Also, if you are not using ADO, you should probably remove that
reference. Otherwise, you need to preface your variables with DAO, i.e.,
DAO.Recordset.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


"Steve W." <(E-Mail Removed)> wrote in message
news:1a8ff01c387b4$7c1af3b0$(E-Mail Removed)...
> It's been several months since I programmed in VBA (and
> then in Access 2000), but it seems to me that this used to
> work. Has DAO changed somehow with Access 2002?
>
> Private Sub btnConvert_Click()
>
> Dim Rst As Recordset
> Dim Qdf As QueryDef
> Dim Parm As Parameter
> Dim ORst As Recordset
> Dim Db As Database
>
> Set Db = CurrentDb()
> Set Qdf = Db.QueryDefs("qry_Delete_OrdersNormal_by_Year")
> Set Parm = Qdf.Parameters![Year?]
> Parm = Me.tbxYear
> Qdf.Execute
>
> Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
> Qdf.Parameters(0).Value = Me.tbxYear
> 'Set Parm = Qdf.Parameters![Year?]
> 'Parm = Me.tbxYear
> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>
> 1) Setting the value of a Query parameter doesn't work
> the way I remember. What's wrong with the syntax in the
> first attempt to set the Parm variable to the query parm...
>
> Set Parm = Qdf.Parameters![Year?]
>
> The query has a parm [Year?] set up for one column in the
> base table. I get a "type mismatch" error on this line if
> I use this syntax.
>
> 2) OK, no big deal, I can change to more awkward syntax
> if I have to in setting the parm. In the second instance
> of setting the Parm...
>
> Qdf.Parameters(0) = Me.tbxYear ' (a form text box)
>
> This works. But now on the immediately following line
>
> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>
> I get a "type mismatch" error on this line. Why!!!?
>
> Thanks for your help.
>
> Steve
>
> PS. the column that the [Year?] query parm is in is an
> integer value (but this never seemed to matter before and
> I get the last crash even if I try to convert the value of
> tbxYear to an Int before I set the parm).

The reference to Microsoft DAO 3.6 Library is not set by default (as it was
also not in Access 2000), so you have to open any module, on the menu, Tools
| References, find that line, and check it. You may want to move it above
ADO in the reference list just in case you miss qualifying an object.

Because ADO and DAO share object names, you'll need to qualify each DAO
object with, no surprise, DAO... as in

Dim rs as DAO.RecordSet.

VBA will use the definition from the library highest in the reference list
if you forget to qualify one of the objects you have Dim'd.

Both these "issues" were the same in the Access 2000 environment, so I guess
they must have slipped from your memory while you were away from Access.

Larry Linson
Microsoft Access MVP


"Steve W." <(E-Mail Removed)> wrote in message
news:1a8ff01c387b4$7c1af3b0$(E-Mail Removed)...
> It's been several months since I programmed in VBA (and
> then in Access 2000), but it seems to me that this used to
> work. Has DAO changed somehow with Access 2002?
>
> Private Sub btnConvert_Click()
>
> Dim Rst As Recordset
> Dim Qdf As QueryDef
> Dim Parm As Parameter
> Dim ORst As Recordset
> Dim Db As Database
>
> Set Db = CurrentDb()
> Set Qdf = Db.QueryDefs("qry_Delete_OrdersNormal_by_Year")
> Set Parm = Qdf.Parameters![Year?]
> Parm = Me.tbxYear
> Qdf.Execute
>
> Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
> Qdf.Parameters(0).Value = Me.tbxYear
> 'Set Parm = Qdf.Parameters![Year?]
> 'Parm = Me.tbxYear
> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>
> 1) Setting the value of a Query parameter doesn't work
> the way I remember. What's wrong with the syntax in the
> first attempt to set the Parm variable to the query parm...
>
> Set Parm = Qdf.Parameters![Year?]
>
> The query has a parm [Year?] set up for one column in the
> base table. I get a "type mismatch" error on this line if
> I use this syntax.
>
> 2) OK, no big deal, I can change to more awkward syntax
> if I have to in setting the parm. In the second instance
> of setting the Parm...
>
> Qdf.Parameters(0) = Me.tbxYear ' (a form text box)
>
> This works. But now on the immediately following line
>
> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>
> I get a "type mismatch" error on this line. Why!!!?
>
> Thanks for your help.
>
> Steve
>
> PS. the column that the [Year?] query parm is in is an
> integer value (but this never seemed to matter before and
> I get the last crash even if I try to convert the value of
> tbxYear to an Int before I set the parm).

I did have a reference set to DAO. But... taking the
reference to ActiveX Data Objects 2.1 from the reference
list worked. So I guess the syntax for the two data
access methods conflicts.

I ported my "old" database that I was working on before
from Access 97, so maybe that's why I hadn't encountered
this before.

Thanks for your help.
>-----Original Message-----
>You probably have not set a reference to DAO. See
Tools|References while in
>VBA Editor. Also, if you are not using ADO, you should
probably remove that
>reference. Otherwise, you need to preface your variables
with DAO, i.e.,
>DAO.Recordset.
>
>--
>Paul Overway
>Logico Solutions, LLC
>www.logico-solutions.com
>
>
>"Steve W." <(E-Mail Removed)> wrote in message
>news:1a8ff01c387b4$7c1af3b0$(E-Mail Removed)...
>> It's been several months since I programmed in VBA (and
>> then in Access 2000), but it seems to me that this used
to
>> work. Has DAO changed somehow with Access 2002?
>>
>> Private Sub btnConvert_Click()
>>
>> Dim Rst As Recordset
>> Dim Qdf As QueryDef
>> Dim Parm As Parameter
>> Dim ORst As Recordset
>> Dim Db As Database
>>
>> Set Db = CurrentDb()
>> Set Qdf = Db.QueryDefs
("qry_Delete_OrdersNormal_by_Year")
>> Set Parm = Qdf.Parameters![Year?]
>> Parm = Me.tbxYear
>> Qdf.Execute
>>
>> Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
>> Qdf.Parameters(0).Value = Me.tbxYear
>> 'Set Parm = Qdf.Parameters![Year?]
>> 'Parm = Me.tbxYear
>> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>>
>> 1) Setting the value of a Query parameter doesn't work
>> the way I remember. What's wrong with the syntax in the
>> first attempt to set the Parm variable to the query
parm...
>>
>> Set Parm = Qdf.Parameters![Year?]
>>
>> The query has a parm [Year?] set up for one column in
the
>> base table. I get a "type mismatch" error on this line
if
>> I use this syntax.
>>
>> 2) OK, no big deal, I can change to more awkward syntax
>> if I have to in setting the parm. In the second
instance
>> of setting the Parm...
>>
>> Qdf.Parameters(0) = Me.tbxYear ' (a form text box)
>>
>> This works. But now on the immediately following line
>>
>> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>>
>> I get a "type mismatch" error on this line. Why!!!?
>>
>> Thanks for your help.
>>
>> Steve
>>
>> PS. the column that the [Year?] query parm is in is an
>> integer value (but this never seemed to matter before
and
>> I get the last crash even if I try to convert the value
of
>> tbxYear to an Int before I set the parm).
>
>
>.
>

I did have DAO in my reference list, but I didn't qualify
the calls. Instead of qualifying the calls I just removed
the defaulted ADO reference and everything started working.

Come to think of it, I did all my work in Access 2000 on
databases that I started in Access 97. Maybe that's why I
didn't have to contend with this before?

Whatever... It's working now.

Thanks for your help.

Steve
>-----Original Message-----
>The reference to Microsoft DAO 3.6 Library is not set by
default (as it was
>also not in Access 2000), so you have to open any module,
on the menu, Tools
>| References, find that line, and check it. You may want
to move it above
>ADO in the reference list just in case you miss
qualifying an object.
>
>Because ADO and DAO share object names, you'll need to
qualify each DAO
>object with, no surprise, DAO... as in
>
> Dim rs as DAO.RecordSet.
>
>VBA will use the definition from the library highest in
the reference list
>if you forget to qualify one of the objects you have
Dim'd.
>
>Both these "issues" were the same in the Access 2000
environment, so I guess
>they must have slipped from your memory while you were
away from Access.
>
> Larry Linson
> Microsoft Access MVP
>
>
>"Steve W." <(E-Mail Removed)> wrote in message
>news:1a8ff01c387b4$7c1af3b0$(E-Mail Removed)...
>> It's been several months since I programmed in VBA (and
>> then in Access 2000), but it seems to me that this used
to
>> work. Has DAO changed somehow with Access 2002?
>>
>> Private Sub btnConvert_Click()
>>
>> Dim Rst As Recordset
>> Dim Qdf As QueryDef
>> Dim Parm As Parameter
>> Dim ORst As Recordset
>> Dim Db As Database
>>
>> Set Db = CurrentDb()
>> Set Qdf = Db.QueryDefs
("qry_Delete_OrdersNormal_by_Year")
>> Set Parm = Qdf.Parameters![Year?]
>> Parm = Me.tbxYear
>> Qdf.Execute
>>
>> Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
>> Qdf.Parameters(0).Value = Me.tbxYear
>> 'Set Parm = Qdf.Parameters![Year?]
>> 'Parm = Me.tbxYear
>> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>>
>> 1) Setting the value of a Query parameter doesn't work
>> the way I remember. What's wrong with the syntax in the
>> first attempt to set the Parm variable to the query
parm...
>>
>> Set Parm = Qdf.Parameters![Year?]
>>
>> The query has a parm [Year?] set up for one column in
the
>> base table. I get a "type mismatch" error on this line
if
>> I use this syntax.
>>
>> 2) OK, no big deal, I can change to more awkward syntax
>> if I have to in setting the parm. In the second
instance
>> of setting the Parm...
>>
>> Qdf.Parameters(0) = Me.tbxYear ' (a form text box)
>>
>> This works. But now on the immediately following line
>>
>> Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)
>>
>> I get a "type mismatch" error on this line. Why!!!?
>>
>> Thanks for your help.
>>
>> Steve
>>
>> PS. the column that the [Year?] query parm is in is an
>> integer value (but this never seemed to matter before
and
>> I get the last crash even if I try to convert the value
of
>> tbxYear to an Int before I set the parm).
>
>
>.
>

"Steve W." <(E-Mail Removed)> wrote in
news:045e01c389dc$7e1dd2c0$(E-Mail Removed):

> I did have a reference set to DAO. But... taking the
> reference to ActiveX Data Objects 2.1 from the reference
> list worked. So I guess the syntax for the two data
> access methods conflicts.

For future reference, if you ever need to have references to both, VBA will
stop looking after the first valid reference in the order in the References
dialog. If you move DAO above ADO, then Dim As Recordset will get you a DAO
one, and you'd have to use an explicit Dim As ADODB.Recordset to get the
other one.

Or vice versa.

Hope that makes sense


Tim F