VBA Module - Type Mismatch Error Message

Posted on 2009-02-19
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
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    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

    <Dim Rst As Recordset>

    Change that line to:

    Dim Rst As DAO.Recordset
    LVL 61

    Expert Comment

    Too slow...

    But all that is necessary as far as I know is the DAO.recordset
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    And to think I was checking for P at the same time. Talk about multi-tasking, lol ...

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    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
    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

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "But not a Database or QueryDef."

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

    LVL 44

    Expert Comment

    by:Leigh Purvis
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now