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
>
Tidak ada komentar:
Posting Komentar