Re: Missing semicolon error???

You were all right on the points you gave me. As the saying goes "It takes a
village..." Thank you all very much for your input; I couldn't have done it
without your help!
Al

"bcap" wrote:

This (misleading) error message occurs because a WHERE clause is meaningless
and invalid in an INSERT statement.

Mind you, having removed the WHERE clause you will next hit the problem
highlighted by Dale Fye, which is that you are trying to open a recordset
using an action query. Dale's solution is almost there, you should do this:

objDB.execute strSQL1, dbFailOnError


"Al" <Al@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BC7706AB-6729-4FF4-93F2-B3AA5E614045@xxxxxxxxxxxxxxxx
I am trying to run an Insert query and keep getting a "Rub-time error
'3137':
Missing semicolon (;) at end of SQL stement. I haven not seen this error
before; however, I have a form event that triggers the following code to
run
(I populate the variables and have other code too, but this is where the
problem lies):

Dim strSQL1 As String
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset

strSQL1 = "INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],[Code],[Rank],[Scolor]) Values ( "
&
PID & ",'" + CStr(Prty) + "','" + CStr(PrAct) + "','" + CStr(cAct) + "','"
+
CStr(PlAct) + "','" + CStr(Phas) + "'," + Pcent + ",'" +
CStr(FridayOfWeek) +
"','" + CStr(Cd) + "'," & Rnk & "," & sCol & ") Where [ID] = " & PID & ""

Set objDB = CurrentDb
Set objRS = objDB.OpenRecordset(strSQL1) 'IT ERRORS HERE

Call objRS.Close
Set objRS = Nothing
Set objDB = Nothing

This is what I see in debug (everything is getting populated fine):
"INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],[Code],[Rank],[Scolor]) Values (
1,'High','','Test Curr','Test Plan','Complete',1,'Friday, September 12,
2008','C',1,1) Where [ID] = 1"

Thanks in advance,
Al



.

Relevant Pages

  • Missing semicolon error???
    ... (I populate the variables and have other code too, ... Dim objDB As DAO.Database ... Set objDB = CurrentDb ... Set objRS = objDB.OpenRecordset'IT ERRORS HERE ...
    (microsoft.public.access.queries)
  • Re: Missing semicolon error???
    ... This error message occurs because a WHERE clause is meaningless ... Dim objDB As DAO.Database ... Set objDB = CurrentDb ... Set objRS = objDB.OpenRecordset'IT ERRORS HERE ...
    (microsoft.public.access.queries)
  • Re: Missing semicolon error???
    ... Agreed, but if you are going to add the dbFailOnError option, youalso need ... Dim objDB As DAO.Database ... Set objDB = CurrentDb ... Set objRS = objDB.OpenRecordset'IT ERRORS HERE ...
    (microsoft.public.access.queries)
  • Re: How to speed up creation of docs without displaying them.
    ... I think there might be a big speed gain if you can avoid opening and closing the documents. ... Dim objConn As Object ... Set objConn = CreateObject ... Set objRS = objConn.Execute ...
    (microsoft.public.word.vba.general)
  • Re: How to speed up creation of docs without displaying them.
    ... Dim objConn As Object ... Dim strSelectList, strSQL, intCol ... Set objConn = CreateObject ... Set objRS = objConn.Execute ...
    (microsoft.public.word.vba.general)
Loading