Connie-
What is the text of the message with the 3134 error?
Try this:
Dim strSQL As String
strSQL = "INSERT INTO ListingsMiscCat " & _
"(ListID, MiscCatID, MiscCatDate, Note) " & _
"VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & _
"#, ""ListID = " & Me.ListID & """)"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
When that fails, go to the Immediate Window. Copy the SQL that was dumped out
to the clipboard, then start a new query, switch to SQL view, paste the SQL,
then switch to Design view. If that produces no error, try to run the query and
see if you get a more explicit error.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Kirkland, WA)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Tuesday, February 28, 2012 12:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Insert Into statement getting syntax error
John,
Thanks for the example of the Immediate window with an INSERT statement. I used
your solution and still am getting a 3134 syntax error which debugs to the
insert statement. I checked and the Note field is a text field limited to 100
characters. When I delete the Note portion from the Insert Into statement
everything works. Crazy!
The Immediate window for the INSERT statement looks good:
INSERT INTO ListingsMiscCat (ListID, MiscCatID, MiscCatDate, Note) VALUES (1840,
5, #12/9/2010#, "ListID = 132")
Here's all of the code for this procedure:
Private Sub cmdSELECT_Click()
On Error GoTo Proc_Err
'Enter older listing date as the one to use for DOM calculations
'Create a new record in ListingsMiscCat table
CurrentDb.Execute "INSERT INTO ListingsMiscCat " & _
"(ListID, MiscCatID, MiscCatDate, Note) " & _
"VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & _
"#, ""ListID = " & Me.ListID & """)", dbFailOnError
'Close Form
DoCmd.Close acForm, "RelistedProperty", acSaveNo
Proc_Exit:
Exit Sub ' or Exit Function
Proc_Err:
Msgbox Err.Description, , _
"ERROR " & Err.Number _
& " cmdSELECT_Click"
Resume Proc_Exit
Resume
End Sub
Thanks for all your help!
Connie
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Connie-
>
> The Immediate Window seems to like it as is. I tried this:
>
> ?"INSERT INTO ListingsMiscCat (Note) " & _
> "VALUES (""ListID = " & 999 & """)"
> INSERT INTO ListingsMiscCat (Note) VALUES ("ListID = 999")
>
> I suspect that won't run by itself because the new row is potentially missing
> required values for ListID, MiscCatID, or MiscCatDate.
>
> Here's my solution again with better line breaks:
>
> CurrentDb.Execute "INSERT INTO ListingsMiscCat " & _
> "(ListID, MiscCatID, MiscCatDate, Note) " & _
> "VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & _
> "#, ""ListID = " & Me.ListID & """)", dbFailOnError
>
> Testing in the Immediate Window, I get:
>
> ?"INSERT INTO ListingsMiscCat " & _
> "(ListID, MiscCatID, MiscCatDate, Note) " & _
> "VALUES (" & 1 & ", 5, #" & Date() & _
> "#, ""ListID = " & 999 & """)"
> INSERT INTO ListingsMiscCat (ListID, MiscCatID, MiscCatDate, Note) VALUES (1,
5,
> #2/28/2012#, "ListID = 999")
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Kirkland, WA)
>
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Tuesday, February 28, 2012 8:46 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Insert Into statement getting syntax error
>
> John,
>
> I'm still having trouble. Have separated out the Note portion on its
> own--that's where the syntax error occurs. The Note field is a memo field.
> Does that have any relevance?
>
> Here's what I'm using:
> CurrentDb.Execute "INSERT INTO ListingsMiscCat (Note) " & _
> "VALUES (""ListID = " & Me.ListID & """)", dbFailOnError
>
> Really appreciate the input on using the # signs.
>
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Connie-
> >
> > Like this:
> >
> > CurrentDb.Execute "INSERT INTO ListingsMiscCat (ListID, MiscCatID,
> MiscCatDate,
> > Note) " & _
> > "VALUES (" & Me.ReListID & ", 5, #" & Me.ListDate & "#, ""ListID = " &
> > Me.ListID & """)", _
> > dbFailOnError
> >
> > Note that I also surrounded the date literal with #.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Kirkland, WA)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > Sent: Monday, February 27, 2012 2:13 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Insert Into statement getting syntax error
> >
> > Hi all! Hope your day is going well. I have checked out the following
> > statement and it's the last value that is giving me the problem. It is to
be
> > put into the Note and would look like: ListID = 832
> >
> > I've tried several different approaches and can't get it to work. My guess
is
> > that I have more to learn about using quotations.
> >
> > CurrentDb.Execute "INSERT INTO ListingsMiscCat (ListID, MiscCatID,
> MiscCatDate,
> > Note) VALUES (" & Me.ReListID & ", 5, " & Me.ListDate & ", ""ListID = """ &
> > Me.ListID & ")", dbFailOnError
> >
> > Thanks!
> > Connie
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar