HELP!! Strange new error - halting production!

Hi -

I have code (below) that ran as recently as Monday.
We decided to change the location for the output reports, and now the
code errors (13 Type Mismatch) on the query.

The query runs fine on its own.
The code still runs fine from the .mde.
If I undo the change (single line) I STILL get the error.
The .mdb compiles fine.
Query code at the end of this note.

Old Code:

Set db = CurrentDb

' This query holds the merchant keys that have RPT = Yes
Set rs = db.OpenRecordset("qrySelMerchForRpt")
'** This is where I get the error

' Set the path to put the snapped report for each merchant, so it
can be renamed and moved
strPath = "\\server-03\Building19\Sales Audit\HoldMerchRpt\"

New Code:
Set db = CurrentDb

' This query holds the merchant keys that have RPT = Yes

DoCmd.OpenQuery "qrySelMerchForRpt" ' *** I added this to see
if the query would open
'
And it does

Set rs = db.OpenRecordset("qrySelMerchForRpt") ' Type 13
mismatch

' Set the path to put the snapped report for each merchant, so it
can be renamed and moved
strPath = "\\server-03\Building19\Sales Reports\Merchants
\HoldMerchRpt\"

Many thanks! I don't even know where to begin to look on this one.
Will post/provide any info requested.

Sara

Query code: (runs fine on its own; Type 13 mismatch on the Set rs =
db.Openrecordset

SELECT tblMerchant.MerchantKey, Left([MerchFirstName],4) &
Left([MerchLastName],4) AS Merch
FROM tblMerchant
WHERE (((tblMerchant.MerchReport)=-1));

Re: HELP!! Strange new error - halting production!

You don't show the declaration of rs, but presumably you have the line:
Dim rs As Recordset

The Type error indicates the object is the wrong type, so probably from the
wrong library. Try:
Dim rs As DAO.Recordset

More info:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" wrote in message
news:399faf5c-86ab-435f-85f8-e653cc066dae@i12g2000prf.google groups.com...
> Hi -
>
> I have code (below) that ran as recently as Monday.
> We decided to change the location for the output reports, and now the
> code errors (13 Type Mismatch) on the query.
>
> The query runs fine on its own.
> The code still runs fine from the .mde.
> If I undo the change (single line) I STILL get the error.
> The .mdb compiles fine.
> Query code at the end of this note.
>
> Old Code:
>
> Set db = CurrentDb
>
> ' This query holds the merchant keys that have RPT = Yes
> Set rs = db.OpenRecordset("qrySelMerchForRpt")
> '** This is where I get the error
>
> ' Set the path to put the snapped report for each merchant, so it
> can be renamed and moved
> strPath = "\\server-03\Building19\Sales Audit\HoldMerchRpt\"
>
> New Code:
> Set db = CurrentDb
>
> ' This query holds the merchant keys that have RPT = Yes
>
> DoCmd.OpenQuery "qrySelMerchForRpt" ' *** I added this to see
> if the query would open
> '
> And it does
>
> Set rs = db.OpenRecordset("qrySelMerchForRpt") ' Type 13
> mismatch
>
> ' Set the path to put the snapped report for each merchant, so it
> can be renamed and moved
> strPath = "\\server-03\Building19\Sales Reports\Merchants
> \HoldMerchRpt\"
>
> Many thanks! I don't even know where to begin to look on this one.
> Will post/provide any info requested.
>
> Sara
>
> Query code: (runs fine on its own; Type 13 mismatch on the Set rs =
> db.Openrecordset
>
> SELECT tblMerchant.MerchantKey, Left([MerchFirstName],4) &
> Left([MerchLastName],4) AS Merch
> FROM tblMerchant
> WHERE (((tblMerchant.MerchReport)=-1));
>

Re: HELP!! Strange new error - halting production!

On Dec 26, 8:47=A0pm, "Allen Browne" wrote:
> You don't show the declaration of rs, but presumably you have the line:
> =A0 =A0 Dim rs As Recordset
>
> The Type error indicates the object is the wrong type, so probably from th=
e
> wrong library. Try:
> =A0 =A0 Dim rs As DAO.Recordset
>
> More info:
> =A0 =A0http://allenbrowne.com/ser-38.html
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "sara" wrote in message
>
> news:399faf5c-86ab-435f-85f8-e653cc066dae@i12g2000prf.google groups.com...
>
>
>
> > Hi -
>
> > I have code (below) that ran as recently as Monday.
> > We decided to change the location for the output reports, and now the
> > code errors (13 Type Mismatch) on the query.
>
> > The query runs fine on its own.
> > The code still runs fine from the .mde.
> > If I undo the change (single line) =A0I STILL get the error.
> > The .mdb compiles fine.
> > Query code at the end of this note.
>
> > Old Code:
>
> > =A0Set db =3D CurrentDb
>
> > ' =A0This query holds the merchant keys that have RPT =3D Yes
> > =A0 =A0Set rs =3D db.OpenRecordset("qrySelMerchForRpt")
> > '** This is where I get the error
>
> > ' =A0Set the path to put the snapped report for each merchant, so it
> > can be renamed and moved
> > =A0 =A0strPath =3D "\\server-03\Building19\Sales Audit\HoldMerchRpt\"
>
> > New Code:
> > =A0 Set db =3D CurrentDb
>
> > ' =A0This query holds the merchant keys that have RPT =3D Yes
>
> > =A0 =A0DoCmd.OpenQuery "qrySelMerchForRpt" =A0 ' *** =A0I added this to =
see
> > if the query would open
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'
> > And it does
>
> > =A0 =A0Set rs =3D db.OpenRecordset("qrySelMerchForRpt") =A0 ' =A0Type 13=

> > mismatch
>
> > ' =A0Set the path to put the snapped report for each merchant, so it
> > can be renamed and moved
> > =A0 =A0strPath =3D "\\server-03\Building19\Sales Reports\Merchants
> > \HoldMerchRpt\"
>
> > Many thanks! =A0I don't even know where to begin to look on this one.
> > Will post/provide any info requested.
>
> > Sara
>
> > Query code: =A0(runs fine on its own; Type 13 mismatch on the Set rs =3D=

> > db.Openrecordset
>
> > SELECT tblMerchant.MerchantKey, Left([MerchFirstName],4) &
> > Left([MerchLastName],4) AS Merch
> > FROM tblMerchant
> > WHERE (((tblMerchant.MerchReport)=3D-1));- Hide quoted text -
>
> - Show quoted text

Allen -
Thank you! That worked, but let me tell you what happened and perhaps
you can explain a bit more to me.

First, I went into my test DB (a copy of production) which errored
yesterday and ran the code to get the error. No error! So, I went
into the production MDB and ran the code, and got the error. I
checked to make sure DAO was in the references and it was (3.6 - I'm
running A2K)

I put your Dim statement in and it ran fine.

But I still wondered why it ran in one and not the other. I checked
"references" to make sure I had the same version of DAO in both - and
found the DAO was near the bottom of my references in the production
MDB (that failed) and near the top in the test version (that worked).

I removed your Dim statement and moved DAO up in the list and it ran!
Why is that? I am set - production runs - but I would like to
understand what is happening so I can learn to not do it again, or fix
it if the error occurs in another db.

Thanks very much -
Sara

Re: HELP!! Strange new error - halting production!

On Dec 26, 8:47=A0pm, "Allen Browne" wrote:
> You don't show the declaration of rs, but presumably you have the line:
> =A0 =A0 Dim rs As Recordset
>
> The Type error indicates the object is the wrong type, so probably from th=
e
> wrong library. Try:
> =A0 =A0 Dim rs As DAO.Recordset
>
> More info:
> =A0 =A0http://allenbrowne.com/ser-38.html
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "sara" wrote in message
>
> news:399faf5c-86ab-435f-85f8-e653cc066dae@i12g2000prf.google groups.com...
>
>
>
> > Hi -
>
> > I have code (below) that ran as recently as Monday.
> > We decided to change the location for the output reports, and now the
> > code errors (13 Type Mismatch) on the query.
>
> > The query runs fine on its own.
> > The code still runs fine from the .mde.
> > If I undo the change (single line) =A0I STILL get the error.
> > The .mdb compiles fine.
> > Query code at the end of this note.
>
> > Old Code:
>
> > =A0Set db =3D CurrentDb
>
> > ' =A0This query holds the merchant keys that have RPT =3D Yes
> > =A0 =A0Set rs =3D db.OpenRecordset("qrySelMerchForRpt")
> > '** This is where I get the error
>
> > ' =A0Set the path to put the snapped report for each merchant, so it
> > can be renamed and moved
> > =A0 =A0strPath =3D "\\server-03\Building19\Sales Audit\HoldMerchRpt\"
>
> > New Code:
> > =A0 Set db =3D CurrentDb
>
> > ' =A0This query holds the merchant keys that have RPT =3D Yes
>
> > =A0 =A0DoCmd.OpenQuery "qrySelMerchForRpt" =A0 ' *** =A0I added this to =
see
> > if the query would open
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'
> > And it does
>
> > =A0 =A0Set rs =3D db.OpenRecordset("qrySelMerchForRpt") =A0 ' =A0Type 13=

> > mismatch
>
> > ' =A0Set the path to put the snapped report for each merchant, so it
> > can be renamed and moved
> > =A0 =A0strPath =3D "\\server-03\Building19\Sales Reports\Merchants
> > \HoldMerchRpt\"
>
> > Many thanks! =A0I don't even know where to begin to look on this one.
> > Will post/provide any info requested.
>
> > Sara
>
> > Query code: =A0(runs fine on its own; Type 13 mismatch on the Set rs =3D=

> > db.Openrecordset
>
> > SELECT tblMerchant.MerchantKey, Left([MerchFirstName],4) &
> > Left([MerchLastName],4) AS Merch
> > FROM tblMerchant
> > WHERE (((tblMerchant.MerchReport)=3D-1));- Hide quoted text -
>
> - Show quoted text -

And perhaps I should mention that there are 2 of us who work on this
DB. And sometimes we make an .MDE (or even edit code) from someone
else's machine. Do I have to make sure DAO is high in the list on all
machines? Especially those editing and creating MDEs?

Thanks

Re: HELP!! Strange new error - halting production!

Yes, your explanation makes sense.

When you move a reference up the list, you are changing its *priority*. So,
if 2 libraries have the same object (e.g. ADO and DAO both have a Recordset
Object), the statement:
Dim rs As Recordset
gives you the higher priority type. If the wrong library has priority, you
get the wrong type (and hence the type mismatch.)

However, if you clearly disambiguate, e.g.:
Dim rs As DAO.Recordset
you get the correct type, regardless of which has priority. Consequently I
strongly recommend that you disambiguate all declarations that *could* be
interpreted as the wrong type, even if you don't use both libraries.
Especially if you are working with a team, where someone else could add
another library later, this is the only way to ensure you code won't fail.

Field and Property (and their plurals) are examples of other objects that
need to be disambiguated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" wrote in message
news:22d62eee-52cc-46e2-b621-e6bfdb250b32@q77g2000hsh.google groups.com...
On Dec 26, 8:47 pm, "Allen Browne" wrote:
> You don't show the declaration of rs, but presumably you have the line:
> Dim rs As Recordset
>
> The Type error indicates the object is the wrong type, so probably from
> the
> wrong library. Try:
> Dim rs As DAO.Recordset
>
> More info:
> http://allenbrowne.com/ser-38.html
>
> "sara" wrote in message
>
> news:399faf5c-86ab-435f-85f8-e653cc066dae@i12g2000prf.google groups.com...
>
>
>
> > Hi -
>
> > I have code (below) that ran as recently as Monday.
> > We decided to change the location for the output reports, and now the
> > code errors (13 Type Mismatch) on the query.
>
> > The query runs fine on its own.
> > The code still runs fine from the .mde.
> > If I undo the change (single line) I STILL get the error.
> > The .mdb compiles fine.
> > Query code at the end of this note.
>
> > Old Code:
>
> > Set db = CurrentDb
>
> > ' This query holds the merchant keys that have RPT = Yes
> > Set rs = db.OpenRecordset("qrySelMerchForRpt")
> > '** This is where I get the error
>
> > ' Set the path to put the snapped report for each merchant, so it
> > can be renamed and moved
> > strPath = "\\server-03\Building19\Sales Audit\HoldMerchRpt\"
>
> > New Code:
> > Set db = CurrentDb
>
> > ' This query holds the merchant keys that have RPT = Yes
>
> > DoCmd.OpenQuery "qrySelMerchForRpt" ' *** I added this to see
> > if the query would open
> > '
> > And it does
>
> > Set rs = db.OpenRecordset("qrySelMerchForRpt") ' Type 13
> > mismatch
>
> > ' Set the path to put the snapped report for each merchant, so it
> > can be renamed and moved
> > strPath = "\\server-03\Building19\Sales Reports\Merchants
> > \HoldMerchRpt\"
>
> > Many thanks! I don't even know where to begin to look on this one.
> > Will post/provide any info requested.
>
> > Sara
>
> > Query code: (runs fine on its own; Type 13 mismatch on the Set rs =
> > db.Openrecordset
>
> > SELECT tblMerchant.MerchantKey, Left([MerchFirstName],4) &
> > Left([MerchLastName],4) AS Merch
> > FROM tblMerchant
> > WHERE (((tblMerchant.MerchReport)=-1));- Hide quoted text -
>
> - Show quoted text

Allen -
Thank you! That worked, but let me tell you what happened and perhaps
you can explain a bit more to me.

First, I went into my test DB (a copy of production) which errored
yesterday and ran the code to get the error. No error! So, I went
into the production MDB and ran the code, and got the error. I
checked to make sure DAO was in the references and it was (3.6 - I'm
running A2K)

I put your Dim statement in and it ran fine.

But I still wondered why it ran in one and not the other. I checked
"references" to make sure I had the same version of DAO in both - and
found the DAO was near the bottom of my references in the production
MDB (that failed) and near the top in the test version (that worked).

I removed your Dim statement and moved DAO up in the list and it ran!
Why is that? I am set - production runs - but I would like to
understand what is happening so I can learn to not do it again, or fix
it if the error occurs in another db.

Thanks very much -
Sara

Re: HELP!! Strange new error - halting production!

On Dec 27, 9:02=A0am, "Allen Browne" wrote:
> Yes, your explanation makes sense.
>
> When you move a reference up the list, you are changing its *priority*. So=
,
> if 2 libraries have the same object (e.g. ADO and DAO both have a Recordse=
t
> Object), the statement:
> =A0 =A0 Dim rs As Recordset
> gives you the higher priority type. If the wrong library has priority, you=

> get the wrong type (and hence the type mismatch.)
>
> However, if you clearly disambiguate, e.g.:
> =A0 =A0 Dim rs As DAO.Recordset
> you get the correct type, regardless of which has priority. Consequently I=

> strongly recommend that you disambiguate all declarations that *could* be
> interpreted as the wrong type, even if you don't use both libraries.
> Especially if you are working with a team, where someone else could add
> another library later, this is the only way to ensure you code won't fail.=

>
> Field and Property (and their plurals) are examples of other objects that
> need to be disambiguated.
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "sara" wrote in message
>
> news:22d62eee-52cc-46e2-b621-e6bfdb250b32@q77g2000hsh.google groups.com...
> On Dec 26, 8:47 pm, "Allen Browne" wrote:
>
>
>
>
>
> > You don't show the declaration of rs, but presumably you have the line:
> > Dim rs As Recordset
>
> > The Type error indicates the object is the wrong type, so probably from
> > the
> > wrong library. Try:
> > Dim rs As DAO.Recordset
>
> > More info:
> >http://allenbrowne.com/ser-38.html
>
> > "sara" wrote in message
>
> >news:399faf5c-86ab-435f-85f8-e653cc066dae@i12g2000prf.googl egroups.com...=

>
> > > Hi -
>
> > > I have code (below) that ran as recently as Monday.
> > > We decided to change the location for the output reports, and now the
> > > code errors (13 Type Mismatch) on the query.
>
> > > The query runs fine on its own.
> > > The code still runs fine from the .mde.
> > > If I undo the change (single line) I STILL get the error.
> > > The .mdb compiles fine.
> > > Query code at the end of this note.
>
> > > Old Code:
>
> > > Set db =3D CurrentDb
>
> > > ' This query holds the merchant keys that have RPT =3D Yes
> > > Set rs =3D db.OpenRecordset("qrySelMerchForRpt")
> > > '** This is where I get the error
>
> > > ' Set the path to put the snapped report for each merchant, so it
> > > can be renamed and moved
> > > strPath =3D "\\server-03\Building19\Sales Audit\HoldMerchRpt\"
>
> > > New Code:
> > > Set db =3D CurrentDb
>
> > > ' This query holds the merchant keys that have RPT =3D Yes
>
> > > DoCmd.OpenQuery "qrySelMerchForRpt" ' *** I added this to see
> > > if the query would open
> > > '
> > > And it does
>
> > > Set rs =3D db.OpenRecordset("qrySelMerchForRpt") ' Type 13
> > > mismatch
>
> > > ' Set the path to put the snapped report for each merchant, so it
> > > can be renamed and moved
> > > strPath =3D "\\server-03\Building19\Sales Reports\Merchants
> > > \HoldMerchRpt\"
>
> > > Many thanks! I don't even know where to begin to look on this one.
> > > Will post/provide any info requested.
>
> > > Sara
>
> > > Query code: (runs fine on its own; Type 13 mismatch on the Set rs =3D
> > > db.Openrecordset
>
> > > SELECT tblMerchant.MerchantKey, Left([MerchFirstName],4) &
> > > Left([MerchLastName],4) AS Merch
> > > FROM tblMerchant
> > > WHERE (((tblMerchant.MerchReport)=3D-1));- Hide quoted text -
>
> > - Show quoted text
>
> Allen -
> Thank you! =A0That worked, but let me tell you what happened and perhaps
> you can explain a bit more to me.
>
> First, I went into my test DB (a copy of production) which errored
> yesterday and ran the code to get the error. =A0No error! =A0So, I went
> into the production MDB and ran the code, and got the error. =A0I
> checked to make sure DAO was in the references and it was (3.6 - I'm
> running A2K)
>
> I put your Dim statement in and it ran fine.
>
> But I still wondered why it ran in one and not the other. =A0I checked
> "references" to make sure I had the same version of DAO in both - and
> found the DAO was near the bottom of my references in the production
> MDB (that failed) and near the top in the test version (that worked).
>
> I removed your Dim statement and moved DAO up in the list and it ran!
> Why is that? =A0I am set - production runs - but I would like to
> understand what is happening so I can learn to not do it again, or fix
> it if the error occurs in another db.
>
> Thanks very much -
> Sara- Hide quoted text -
>
> - Show quoted text -

Allen -
Wow! That is very clear and understandable...It's sort of like when a
book has sections and each section has a Chapter 2. You have to tell
the user *which* Chapter 2 to reference so they'll find what they're
looking for.

Am I also correct in saying that I will always want DAO (as opposed to
ADO) for the recordset?

I will make the change - now that I understand WHY, even tho it is
working.

Many many thanks - I'm learning!

Sara

Re: HELP!! Strange new error - halting production!

If you are working with JET tables (those in the Access database), yes: you
will almost always want DAO.

Here's my recommendation of what libraries you need for each version of
Access, and how to fix reference problems:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" wrote in message
news:52ec1e28-d94e-4564-82e2-c8f1d6c18552@d4g2000prg.googleg roups.com...
On Dec 27, 9:02 am, "Allen Browne" wrote:
> Wow! That is very clear and understandable...It's sort of like when a
> book has sections and each section has a Chapter 2. You have to
> tell the user *which* Chapter 2 to reference so they'll find what they're
> looking for.
>
> Am I also correct in saying that I will always want DAO (as opposed
> to ADO) for the recordset?
>
> I will make the change - now that I understand WHY, even tho it is
> working.
>
> Many many thanks - I'm learning!
>
> Sara

Re: HELP!! Strange new error - halting production!

On Dec 27, 9:58=A0am, "Allen Browne" wrote:
> If you are working with JET tables (those in the Access database), yes: yo=
u
> will almost always want DAO.
>
> Here's my recommendation of what libraries you need for each version of
> Access, and how to fix reference problems:
> =A0 =A0http://allenbrowne.com/ser-38.html
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "sara" wrote in message
>
> news:52ec1e28-d94e-4564-82e2-c8f1d6c18552@d4g2000prg.googleg roups.com...
> On Dec 27, 9:02 am, "Allen Browne" wrote:
>
>
>
> > Wow! =A0That is very clear and understandable...It's sort of like when a=

> > book has sections and each section has a Chapter 2. =A0You have to
> > tell the user *which* Chapter 2 to reference so they'll find what they'r=
e
> > looking for.
>
> > Am I also correct in saying that I will always want DAO (as opposed
> > to ADO) for the recordset?
>
> > I will make the change - now that I understand WHY, even tho it is
> > working.
>
> > Many many thanks - I'm learning!
>
> > Sara- Hide quoted text -
>
> - Show quoted text -

This is VERY informational and helpful. Thanks. I've bookmarked it
and read a little; will read more and learn as much as I can.

Again, many thanks.

Happy New Year!
Sara

Re: HELP!! Strange new error - halting production!

sara wrote:

>And perhaps I should mention that there are 2 of us who work on this
>DB. And sometimes we make an .MDE (or even edit code) from someone
>else's machine. Do I have to make sure DAO is high in the list on all
>machines? Especially those editing and creating MDEs?

No. The references are per MDB/MDE and not by machine.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Re: HELP!! Strange new error - halting production!

On Dec 27, 10:16=A0pm, "Tony Toews [MVP]"
wrote:
> sara wrote:
> >And perhaps I should mention that there are 2 of us who work on this
> >DB. =A0And sometimes we make an .MDE (or even edit code) from someone
> >else's machine. =A0Do I have to make sure DAO is high in the list on all
> >machines? =A0Especially those editing and creating MDEs?
>
> No. =A0 The references are per MDB/MDE and not by machine.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> =A0 =A0Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> =A0 =A0Microsoft Access Links, Hints, Tips & Accounting Systems athttp://w=
ww.granite.ab.ca/accsmstr.htm
> =A0 =A0Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Thanks for the info, Tony. I keep learning.....
Happy New Year,
Sara