VBA Module - Type Mismatch Error Message

Posted on 2009-02-19
Medium Priority
Last Modified: 2013-11-25
Your help, please in determining the cause of the error message on running the attached code.  As background, my user will have opened a client's record, then decided to send (open for entry of a message) an email.  I store this client's email address in a SINGLE record table called "StorageTable" (for use later).  The querydef shown in the code works.  I tested it.  I am receiving a Run-time error 13 - Type Mismatch at the "Set Rst = DB.OpenRecordset(...) statement.
Thanks for your help.
Public Function SendClientEmail()
' Open an Email For the Client Selected By User
Dim DB As Database, Q As QueryDef, qSQL_Strng As String
Dim Rst As Recordset
Dim appOutlook As New Outlook.Application
Dim otlItem As Outlook.MailItem
Dim strMailRecipient As String
'Get the email address stored for selected customer
Set DB = CurrentDb()
Set Q = DB.QueryDefs("StoredEmail")
qSQL_Strng = "Select StorageTable.Cust_Email From StorageTable Where ID =1"
Q.SQL = qSQL_Strng
' Open a recordset of one record
Set Rst = DB.OpenRecordset("StoredEmail")
strMailRecipient = Rst(0)
'Open Outlook
Set otlItem = appOutlook.CreateItem(olMailItem)
With otlItem
    .To = strMailRecipient
    .Subject = "Message From Zack"
End With
End Function

Open in new window

Question by:mcn
  • 4
  • 2
  • 2
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 23688190
Try making these changes:

Dim DB As dao.Database, Q As dao.QueryDef, qSQL_Strng As String

Dim Rst As dao.Recordset
LVL 61

Accepted Solution

mbizup earned 300 total points
ID: 23688194
<Dim Rst As Recordset>

Change that line to:

Dim Rst As DAO.Recordset
LVL 61

Expert Comment

ID: 23688212
Too slow...

But all that is necessary as far as I know is the DAO.recordset
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 75
ID: 23688220
And to think I was checking m.com for P at the same time. Talk about multi-tasking, lol ...

LVL 75
ID: 23688511
Actually, DAO should be put in front of all Access object variables referring to an Access database, recordsets, querydefs, etc.

LVL 44

Expert Comment

by:Leigh Purvis
ID: 23691304
The reason for the reference library prefix is to disambiguate the object.
Yes, it can be considered good practice to do it across all objects as the habit it forms stands us in good stead  but thats personal preference, not a general dictate.

However its only required when there is a named object available from more than one library (such as DAO and ADO recordsets).
With Database and QueryDef being inherent DAO objects (and not appearing in ADO or any other library that I can think of) they are free to appear without issue.
In lazy moments Ive left those objects without a library prefix (and old code is probably littered with it, they were simpler times! ;-)
IMO you'd be crazy not to disambiguate, say, a Connection or Document object...
But not a Database or QueryDef.
LVL 75
ID: 23694861
"But not a Database or QueryDef."

Of course ...IF ... MS changed things in the future .... ?!?!?

LVL 44

Expert Comment

by:Leigh Purvis
ID: 23705340
Hi again.

That's a pretty big "If" to use an absolute statement like "DAO should be put in front of all ..." ;-)
As opposed to having said "It would be good practice..." or "I always..." and then to expand upon why.
It's a pesonal developer preference of an implemented practice.

For example MS have "changed things" with Office 2007.  DAO has been replaced with <takes a deep breath>...
"Microsoft Office 12.0 Access database engine Object library"
(I mean honestly... could it be less concise?)

However, though I can hypocritically call MS inconcise, they had the foresight to name this library "DAO".
Obviously it's naming was obvious to us because it was based almost entirely on DAO's OM (and existing code base).
Vitally its presence precluses DAO being selected as a referenced library in the same project.
There was no lack of thought in the process.  It allows fully referenced and object only declarations to continue on as before.
What if they'd not named it DAO?
All old code which declared the referenced the library as well as object then wouldn't convert seemlessly.
We'd have all been up in arms naturally.  Much as we would be with a new library which duplicated much of DAO's OM and its object's names.
Consider the Recordset2 object.  The same but different.  Named to be complimentary (if not desperately imaginative lol).

That is just the flip side of the possibilities that exist if MS make changes in the future.
It would get caught in testing and trials.  (They have some great  testers).
Depsite what many think, MS are very aware of their userbase.  They don't want to go breaking functionality in countless exiting applications out there - even though they'd naturally love us all to jump on board to using new versions exclusively.
You might recall the scare mongering that broke last year that VBA was going to be removed from Office 14 (just because some columnist asserted that it would be - since it was removed from the Mac 2008 version :-s)
Crazy naturally.  Literally millions of applications the world over would have been decimated.

Anyway, I've already gone on record in this thread that I believe disambiguating objects is a good practice even when not required.
But I stand by the assertion that someone couldn't be considered crazy for, based on an informed decision - i.e. knowing there are no conflicting objects, not disambiguating the Objects they know are unique in their scope.
Good chat though.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question