1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Error 2342 on Docmd.Runsql

Discussion in 'Business Applications' started by Charmian, Feb 7, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    My vb is giving me an error saying that it expects an sql statement. When in debug mode the statement looks like this:
    Select Jobcardno, JobcardID from Jobcard WHERE Jobcardno = "E900006" ;
    what I am trying to do is get the JobcardID of the record that I have just written in a previous docmd.runsql statement. Is there a n easier way to get that info or do I have a 'not finished writing yet' problem? The Jobcardid is a autofield and It is used as part of a key in a composite table. This system was originally in 2002 Access and I'm taking it to 2007 and adding some features, but have to keep the integrity of the existing tables and their relationships.
    Here is all the code:
    sql4 = "INSERT INTO JOBCARD (jobcardno,status,requestdate,requireddate,datereceived,requestedby,jobrequest,allocatedto,CCNO,workdone) " & _
    "VALUES (""" & Newjobcardno & """, """ & Status & """, " & today & "," & today & ", " & today & ", """ & GMemp & _
    """, """ & emergency & """, """ & Combo53.Column(0) & """, " & ccno & ", """ & Text74 & """)"
    MsgBox "sql4:" & sql4
    DoCmd.RunSQL sql4 This part runs and creates the record - I want to get hold of autonumber key
    sql5 = "Select Jobcardno, JobcardID " & _
    "from Jobcard " & _
    "WHERE Jobcardno = """ & Newjobcardno & """ ;"
    MsgBox "sql5:" & sql5
    DoCmd.RunSQL sql5 This is where is hightlights with the 2342 error
    Set pdb = CurrentDb.OpenRecordset(sql5) 'GET THE jobid for the one we just wrote
    jobidno = pdb!JobCardID
    pdb.Close

    Do I need to "Commit" the insert statement?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    First of all what is the Error Description for 2342?
    Second the where line of the problem SQL may not require extra "" as that converts a number to a string text.
    So if your table Jobcardno is a number you will get an "Type Mismatch" error.
     
  3. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    Error 2342: A RUNSQL action requires an argument consisting of an SQL statement.

    The SQL needs the extra " as jobcardno is not a number... in debug.print mode the sql5 statement looks like so:
    Select Jobcardno, JobcardID from Jobcard WHERE Jobcardno = "E900006" ;
    which actually does run when run as a query.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you remove the Where part of the SQL in sql5, does it then run?
     
  5. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    NO. took the where out and it still gives that error. - Changed it to a order by jobcardid desc.

    Aah no, just figured it out - you can only use RUNSQL for insert, delete and update statements for select you have to use

    Set pdb = CurrentDb.OpenRecordset(sql5)

    Sorry and thanks
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088533

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice