Insert Into -- expected end of statement error

  • Thread starter Thread starter laavista
  • Start date Start date
L

laavista

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!
 
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 said:
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.
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!
Click to expand...

--
Jim Burke



.
Click to expand...
 
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 said:
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 said:
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.
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!
Click to expand...

--
Jim Burke



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

Douglas J. Steele said:
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 said:
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 said:
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



.
Click to expand...
Click to expand...


.
Click to expand...
 
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
Click to expand...

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.
 
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 said:
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 said:
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 said:
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



.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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
Click to expand...

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.
Click to expand...
 
CurrentDb.Execute "insert into tbl_details (ID) values (" & NumID & ")",
dbFailOnError

If it were text, you'd need to use

CurrentDb.Execute "insert into tbl_details (ID) values (""" & TextID &
""")", dbFailOnError

or

CurrentDb.Execute "insert into tbl_details (ID) values ('" & TextID & "')",
dbFailOnError


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

laavista said:
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 said:
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 said:
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.

:

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



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top