Results 1 to 6 of 6
  1. #1
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74

    Type mismatch but ONLY when run via VBA?

    Hi all, I have a query. It works fine. But when I call it from the VBA code, I get a type mismatch error. I've been pausing the code, manually running the query, then continuing the code from the next line, so I know the point in time when the docmd.openquery was called, every variable possible is identical when I open it manually. Has anyone ever encountered this?



    I don't know if this helps at all without knowing data types and background info, but here's the query SQL:

    Code:
    INSERT INTO LineItemComponentsT ( ComponentID, ID, ComponentDescription, SourceComponent )
    SELECT LineItemsQ.ID, InsertAutoTotalComponentsA.ID, "AutoTotal" AS Source, InsertAutoTotalComponentsA.id AS src
    FROM LineItemsQ, InsertAutoTotalComponentsA
    WHERE (((LineItemsQ.Heading)=[forms]![LineItemSummaryCF]![DisplayHeading]) AND ((LineItemsQ.Report)=[forms]![LineItemSummaryCF]![Report]));

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What does "calling the query from vba code" mean? As in DoCmd.OpenQuery? Or executing the sql inside of code? There is a big difference.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    As mentioned in the OP, I am using docmd.openquery. However I tried DB.execute and docmd.runsql and the same thing happens either way

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Might help to see your actual code. Did you put the query name in quotes, as required?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, missed that. After an intense look at the sql, I'm afraid I can't see anything that should cause a query call in vba to fail - especially if you say the variables are all there. It may be in the details you haven't shown. Unless looking at the rest of the related code would reveal something, I'd try debug or msgbox statements to reveal the data type being passed to the query from the vba side. Can't recall how to get the data type of a control at the moment. Or you could try forcing it to the correct type to see if it works and if so, which one was the problem.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    FWIW, a method is posted here https://bytes.com/topic/access/answe...tatype-control
    Since the error is 'data type mismatch' I would concentrate on figuring out which of the few query fields aren't getting the correct type passed to it.
    Or you could show the rest of the relevant code as mentioned.

    I actually had a similar method in a test database and forgot about it.
    EDIT
    Forgot to mention that I used Left function on the controlsource. If it starts with = likely you don't want to pass it to the function as it would be a calculated field.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Type Mismatch
    By Alex Motilal in forum Programming
    Replies: 5
    Last Post: 02-04-2017, 05:57 PM
  2. Type 13 (Type Mismatch) error
    By sdel_nevo in forum Programming
    Replies: 5
    Last Post: 01-22-2016, 10:01 AM
  3. type mismatch
    By seeker63 in forum Programming
    Replies: 2
    Last Post: 12-05-2013, 02:54 PM
  4. Type Mismatch....WHY?!!?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 03-07-2011, 09:18 AM
  5. Type Mismatch
    By Alex Motilal in forum Programming
    Replies: 10
    Last Post: 02-13-2011, 05:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums