Solved: Error 2342 on Docmd.Runsql

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 

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.
 

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.
 

OBP

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

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top