By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,232 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

HELP!! Strange new error - halting production!

P: n/a
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));

Dec 26 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <sa*******@yahoo.comwrote in message
news:39**********************************@i12g2000 prf.googlegroups.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));
Dec 27 '07 #2

P: n/a
On Dec 26, 8:47*pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <saraqp...@yahoo.comwrote in message

news:39**********************************@i12g2000 prf.googlegroups.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

Dec 27 '07 #3

P: n/a
On Dec 26, 8:47*pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <saraqp...@yahoo.comwrote in message

news:39**********************************@i12g2000 prf.googlegroups.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 -
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
Dec 27 '07 #4

P: n/a
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" <sa*******@yahoo.comwrote in message
news:22**********************************@q77g2000 hsh.googlegroups.com...
On Dec 26, 8:47 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <saraqp...@yahoo.comwrote in message

news:39**********************************@i12g2000 prf.googlegroups.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

Dec 27 '07 #5

P: n/a
On Dec 27, 9:02*am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <saraqp...@yahoo.comwrote in message

news:22**********************************@q77g2000 hsh.googlegroups.com...
On Dec 26, 8:47 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:


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" <saraqp...@yahoo.comwrote in message
news:39**********************************@i12g2000 prf.googlegroups.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- 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
Dec 27 '07 #6

P: n/a
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" <sa*******@yahoo.comwrote in message
news:52**********************************@d4g2000p rg.googlegroups.com...
On Dec 27, 9:02 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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
Dec 27 '07 #7

P: n/a
On Dec 27, 9:58*am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <saraqp...@yahoo.comwrote in message

news:52**********************************@d4g2000p rg.googlegroups.com...
On Dec 27, 9:02 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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- 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
Dec 27 '07 #8

P: n/a
sara <sa*******@yahoo.comwrote:
>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/
Dec 28 '07 #9

P: n/a
On Dec 27, 10:16*pm, "Tony Toews [MVP]" <tto...@telusplanet.net>
wrote:
sara <saraqp...@yahoo.comwrote:
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 athttp://www.granite.ab.ca/accsmstr.htm
* *Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Thanks for the info, Tony. I keep learning.....
Happy New Year,
Sara
Dec 28 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.