MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Insert Into -- expected end of statement error

  • Follow


I need to add a record into a table.   I'm getting "expected end of statement"

table name:  tbl_details
field:   ID  (key, number, long)

syntax I'm trying to use in the module:

   INSERT INTO tbl_details ([ID]) VALUES (999)

Your help would be VERY appreciated!
0
Reply Utf 6/7/2010 4:02:42 PM 

You should probably post the VBA code you're using. It's hard to tell what's
going on without the actual code. That statement in and of itself looks OK,
but there's obviously something going on with your VBA code.

laavista wrote:
>I need to add a record into a table.   I'm getting "expected end of statement"
>
>table name:  tbl_details
>field:   ID  (key, number, long)
>
>syntax I'm trying to use in the module:
>
>   INSERT INTO tbl_details ([ID]) VALUES (999)
>
>Your help would be VERY appreciated!

-- 
Jim Burke

Message posted via http://www.accessmonster.com
0
Reply JimBurke 6/7/2010 4:28:42 PM 


Thanks, Jim for responding.  My code:

scenario:
   user is on form f_MainInfo
   they insert a new record for tbl_MainInfo
   they click on a command button 'cmdDetails' which will:
        1)  add a new record in the tbl_details table with a matching ID
        2)  take them to a the form f_details

private sub cmdDetails_click()

if me.newrecord = -1 then
   ' insert into tbl_details ([ID]) values (me.id)
   insert into tbl_details ([ID]) values (999)  'value of 999 to test
end if

exit sub 

Thanks for looking at this.

"JimBurke via AccessMonster.com" wrote:

> You should probably post the VBA code you're using. It's hard to tell what's
> going on without the actual code. That statement in and of itself looks OK,
> but there's obviously something going on with your VBA code.
> 
> laavista wrote:
> >I need to add a record into a table.   I'm getting "expected end of statement"
> >
> >table name:  tbl_details
> >field:   ID  (key, number, long)
> >
> >syntax I'm trying to use in the module:
> >
> >   INSERT INTO tbl_details ([ID]) VALUES (999)
> >
> >Your help would be VERY appreciated!
> 
> -- 
> Jim Burke
> 
> Message posted via http://www.accessmonster.com
> 
> .
>
0
Reply Utf 6/7/2010 6:06:13 PM 

private sub cmdDetails_click()

if me.newrecord = -1 then
   ' insert into tbl_details ([ID]) values (me.id)
   CurrentDb.Execute "insert into tbl_details ([ID]) values (999)", 
dbFailOnError
end if

exit sub


-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"laavista" <laavista@discussions.microsoft.com> wrote in message 
news:C6628380-0805-4B95-813A-4E5E3A28E91D@microsoft.com...
> Thanks, Jim for responding.  My code:
>
> scenario:
>   user is on form f_MainInfo
>   they insert a new record for tbl_MainInfo
>   they click on a command button 'cmdDetails' which will:
>        1)  add a new record in the tbl_details table with a matching ID
>        2)  take them to a the form f_details
>
> private sub cmdDetails_click()
>
> if me.newrecord = -1 then
>   ' insert into tbl_details ([ID]) values (me.id)
>   insert into tbl_details ([ID]) values (999)  'value of 999 to test
> end if
>
> exit sub
>
> Thanks for looking at this.
>
> "JimBurke via AccessMonster.com" wrote:
>
>> You should probably post the VBA code you're using. It's hard to tell 
>> what's
>> going on without the actual code. That statement in and of itself looks 
>> OK,
>> but there's obviously something going on with your VBA code.
>>
>> laavista wrote:
>> >I need to add a record into a table.   I'm getting "expected end of 
>> >statement"
>> >
>> >table name:  tbl_details
>> >field:   ID  (key, number, long)
>> >
>> >syntax I'm trying to use in the module:
>> >
>> >   INSERT INTO tbl_details ([ID]) VALUES (999)
>> >
>> >Your help would be VERY appreciated!
>>
>> -- 
>> Jim Burke
>>
>> Message posted via http://www.accessmonster.com
>>
>> .
>>
0
Reply Douglas 6/7/2010 6:21:05 PM 

Thank you so much.  This worked!
I really appreciate you taking the time to respond to my question.

"Douglas J. Steele" wrote:

> private sub cmdDetails_click()
> 
> if me.newrecord = -1 then
>    ' insert into tbl_details ([ID]) values (me.id)
>    CurrentDb.Execute "insert into tbl_details ([ID]) values (999)", 
> dbFailOnError
> end if
> 
> exit sub
> 
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> Co-author: Access 2010 Solutions, published by Wiley
> (no e-mails, please!)
> 
> "laavista" <laavista@discussions.microsoft.com> wrote in message 
> news:C6628380-0805-4B95-813A-4E5E3A28E91D@microsoft.com...
> > Thanks, Jim for responding.  My code:
> >
> > scenario:
> >   user is on form f_MainInfo
> >   they insert a new record for tbl_MainInfo
> >   they click on a command button 'cmdDetails' which will:
> >        1)  add a new record in the tbl_details table with a matching ID
> >        2)  take them to a the form f_details
> >
> > private sub cmdDetails_click()
> >
> > if me.newrecord = -1 then
> >   ' insert into tbl_details ([ID]) values (me.id)
> >   insert into tbl_details ([ID]) values (999)  'value of 999 to test
> > end if
> >
> > exit sub
> >
> > Thanks for looking at this.
> >
> > "JimBurke via AccessMonster.com" wrote:
> >
> >> You should probably post the VBA code you're using. It's hard to tell 
> >> what's
> >> going on without the actual code. That statement in and of itself looks 
> >> OK,
> >> but there's obviously something going on with your VBA code.
> >>
> >> laavista wrote:
> >> >I need to add a record into a table.   I'm getting "expected end of 
> >> >statement"
> >> >
> >> >table name:  tbl_details
> >> >field:   ID  (key, number, long)
> >> >
> >> >syntax I'm trying to use in the module:
> >> >
> >> >   INSERT INTO tbl_details ([ID]) VALUES (999)
> >> >
> >> >Your help would be VERY appreciated!
> >>
> >> -- 
> >> Jim Burke
> >>
> >> Message posted via http://www.accessmonster.com
> >>
> >> .
> >> 
> 
> 
> .
>
0
Reply Utf 6/7/2010 7:37:10 PM 

On Mon, 7 Jun 2010 11:06:13 -0700, laavista
<laavista@discussions.microsoft.com> wrote:

>private sub cmdDetails_click()
>
>if me.newrecord = -1 then
>   ' insert into tbl_details ([ID]) values (me.id)
>   insert into tbl_details ([ID]) values (999)  'value of 999 to test
>end if
>
>exit sub 

VBA is one language; SQL is a different language. You can't just switch into
SQL in the middle of a VBA procedure - the VBA compiler has no knowlege of it.
Es geht nicht, als sollte ich mittens ins Antwort auf Deutsch verandert.

Douglas' Execute method is the way to get around the two-languages problem.
-- 

             John W. Vinson [MVP]
0
Reply John 6/7/2010 7:45:00 PM 

I have one more question.  I need to use a variable in the query for the 
field name (e.g., ID).    In the module, I set NumID=to the value in the 
MainInfo form (the variable does contain the ID).  I tried just using it in 
the statement below and it didn't work.   I found 'help' on using a variable 
in a query that says you use a function.  So, I tried calling a function 
using:

NumID = forms!f_MainInfo!(ID}  
NumID = GetID()

function GetID()
   GetID = NumID
end function

in the statement you provided which worked on hard coded data, I tried:
 CurrentDb.Execute "insert into tbl_details (ID]) values (NumID)", 
dbFailOnError

Results:
NumID does = the ID on the form
on the "CurrentDb..." statement I get the run-time rror '3061'.  Too few 
parameters.  Expected 1.

THANKS AGAIN for your help

"Douglas J. Steele" wrote:

> private sub cmdDetails_click()
> 
> if me.newrecord = -1 then
>    ' insert into tbl_details ([ID]) values (me.id)
>    CurrentDb.Execute "insert into tbl_details ([ID]) values (999)", 
> dbFailOnError
> end if
> 
> exit sub
> 
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> Co-author: Access 2010 Solutions, published by Wiley
> (no e-mails, please!)
> 
> "laavista" <laavista@discussions.microsoft.com> wrote in message 
> news:C6628380-0805-4B95-813A-4E5E3A28E91D@microsoft.com...
> > Thanks, Jim for responding.  My code:
> >
> > scenario:
> >   user is on form f_MainInfo
> >   they insert a new record for tbl_MainInfo
> >   they click on a command button 'cmdDetails' which will:
> >        1)  add a new record in the tbl_details table with a matching ID
> >        2)  take them to a the form f_details
> >
> > private sub cmdDetails_click()
> >
> > if me.newrecord = -1 then
> >   ' insert into tbl_details ([ID]) values (me.id)
> >   insert into tbl_details ([ID]) values (999)  'value of 999 to test
> > end if
> >
> > exit sub
> >
> > Thanks for looking at this.
> >
> > "JimBurke via AccessMonster.com" wrote:
> >
> >> You should probably post the VBA code you're using. It's hard to tell 
> >> what's
> >> going on without the actual code. That statement in and of itself looks 
> >> OK,
> >> but there's obviously something going on with your VBA code.
> >>
> >> laavista wrote:
> >> >I need to add a record into a table.   I'm getting "expected end of 
> >> >statement"
> >> >
> >> >table name:  tbl_details
> >> >field:   ID  (key, number, long)
> >> >
> >> >syntax I'm trying to use in the module:
> >> >
> >> >   INSERT INTO tbl_details ([ID]) VALUES (999)
> >> >
> >> >Your help would be VERY appreciated!
> >>
> >> -- 
> >> Jim Burke
> >>
> >> Message posted via http://www.accessmonster.com
> >>
> >> .
> >> 
> 
> 
> .
>
0
Reply Utf 6/7/2010 9:24:54 PM 

I did not realize that about SQL and VBA.  That helped, thanks.

Using Douglas' method, do you know how to use a numeric (dim as long) 
variable?

Thanks for your help.

"John W. Vinson" wrote:

> On Mon, 7 Jun 2010 11:06:13 -0700, laavista
> <laavista@discussions.microsoft.com> wrote:
> 
> >private sub cmdDetails_click()
> >
> >if me.newrecord = -1 then
> >   ' insert into tbl_details ([ID]) values (me.id)
> >   insert into tbl_details ([ID]) values (999)  'value of 999 to test
> >end if
> >
> >exit sub 
> 
> VBA is one language; SQL is a different language. You can't just switch into
> SQL in the middle of a VBA procedure - the VBA compiler has no knowlege of it.
> Es geht nicht, als sollte ich mittens ins Antwort auf Deutsch verandert.
> 
> Douglas' Execute method is the way to get around the two-languages problem.
> -- 
> 
>              John W. Vinson [MVP]
> .
>
0
Reply Utf 6/7/2010 9:50:58 PM
microsoft.public.access.modulesdaovba 1671 articles. 5 followers.

7 Replies
760 Views

(page loaded in 0.144 seconds)

Similiar Articles:

Insert Into -- expected end of statement error - microsoft.public ...
I need to add a record into a table. I'm getting "expected end of statement" table name: tbl_details field: ID (key, number, long) syntax...

INSERT INTO Query Converted to VBA - microsoft.public.access ...
Insert Into -- expected end of statement error - microsoft.public ... Insert Into -- expected end of statement error - microsoft.public ... live mail how do I insert time ...

Duplicating reocords from main form and subform receiving Runtime ...
Insert Into -- expected end of statement error - microsoft.public ... Insert Into -- expected end of statement error Follow ... My code: > > scenario: > user is on form f ...

Execute a variable as an insertion into a VBA code line ...
Insert Into -- expected end of statement error - microsoft.public ... You should probably post the VBA code you're using. ... details ([ID]) values (me.id) CurrentDb ...

microsoft.public.access.modulesdaovba
Insert Into -- expected end of statement error Utf 7 488 I need to add a record into a table. I'm getting "expected end of statement" table name: tbl_details field ...

Getting new record's identity (ID) - microsoft.public.access ...
Insert Into -- expected end of statement error - microsoft.public ..... user is on form f_MainInfo they insert a new record for tbl ... button 'cmdDetails' which will ...

set identity_insert not works for insert statement on SQL2008 ...
... skip all columns name and try a statement "insert into ... typing all columns name when insert from > another table ? The error ... id = @@IDENTITY RETURN @tbox_id END ...

programatically insert record - microsoft.public.access ...
I'm sure the sql statement should be something like: "INSERT INTO ... _____ On Error GoTo Err_cmdAdd_Click If ... include the semicolon at the end of my sql statement?

How do I get the current User's path to Desktop - microsoft ...
Insert Into -- expected end of statement error - microsoft.public ... I need to add a record into a table. I'm getting "expected ... How do I get the current User's path ...

is there a way to get number of "objects" in a Access db ...
Insert Into -- expected end of statement error - microsoft.public ..... table name: tbl_details field: ID (key, number ... That statement in and of itself looks OK, but ...

Answer : Insert Into -- expected end of statement error
I need to add a record into a table. I'm getting "expected end of statement" table name: tbl_details field: ID (key, number, long) syntax I'm trying to use in the ...

Insert Into -- expected end of statement error DataBase
I need to add a record into a table. I am getting expected end of statement table name: tbl_details field: ID (key, number, long) syntax I am tryi

INSERT INTO gets compile error: Expected end of statement. DataBase
I'm just trying to add a new record in a table consisting of two fields System_ID and SerialNumber. My SQL statement is as follows: DoCmd.RunSQL INSERT

Insert Into -- expected end of statement error - microsoft.public ...
I need to add a record into a table. I'm getting "expected end of statement" table name: tbl_details field: ID (key, number, long) syntax...

Code 800A0401- Expected end of statement | VBScript Error
Free Real-time Bandwidth Monitor. This tools polls your interface every half second. It also reports real-time bandwidth usage on a graph.

7/28/2012 8:14:20 AM


Reply:



Privacy Policy | All Times Are GMT(UTC) | powered by