Add a record to a table using VBA

  • Thread starter Thread starter GLT
  • Start date Start date
G

GLT

Hi,

I have three feilds: SERVER, TYPE, ERROR

I would like to simply add a new record (three feilds mentioned above) to a
table using VBA (when a user clicks a button), however my form also contains
an option group, and if the first option button is selected then SERVER =
ALL, if the second option button is selected, then SERVER = servname.

Can anyone advise the SQL statement I need to use?

Any help is always greatly appreciated...

Cheers,
GLT.
 
DoCmd.RunSQL "INSERT INTO tableName(server, type, error) VALUES(
FORMS!formNameHere!server, FORMS!formNameHere!type,
FORMS!formNameHere!error)"

would do in a simpler case. In your case, the
FORMS!formNameHere!server
has to be replace with an iif statement (for which I have some problem to
follow your specs.)


Using DoCmd allows to use FORMS!formName!ControlName WITHOUT having to
use delimiter (for strings and dates), neither to check if that delimiter is
already present in the value. In other words, it is simpler.


Vanderghast, Access MVP
 
Hi Vandeghast,

Thanks for your reply - I have constructed the following SQL in the Q.Design
Grid and it worked OK, however when converted that into the DoCmd.RunSQL
statement, it errors at the "ALL" point, with a syntax error.

Can anyone advise why and what I need to do to fix it?

Cheers,
GLT.

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,"ALL",[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server, [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"
 
Indeed, the delimiter for the string is a double quote, so VBA assumes you
said:


DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,"

given that the string ends as soon as it sees a second ". You can try:

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,""ALL"",[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server, [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"



since 'doubling' the " indicates, in VBA, that you don't mean to stop the
string at that point.



DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,'ALL',[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server, [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"


would also do, since ' are an alternative delimiter for string.



Vanderghast, Access MVP

GLT said:
Hi Vandeghast,

Thanks for your reply - I have constructed the following SQL in the
Q.Design
Grid and it worked OK, however when converted that into the DoCmd.RunSQL
statement, it errors at the "ALL" point, with a syntax error.

Can anyone advise why and what I need to do to fix it?

Cheers,
GLT.

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,"ALL",[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server, [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"


vanderghast said:
DoCmd.RunSQL "INSERT INTO tableName(server, type, error) VALUES(
FORMS!formNameHere!server, FORMS!formNameHere!type,
FORMS!formNameHere!error)"

would do in a simpler case. In your case, the
FORMS!formNameHere!server
has to be replace with an iif statement (for which I have some problem to
follow your specs.)


Using DoCmd allows to use FORMS!formName!ControlName WITHOUT having to
use delimiter (for strings and dates), neither to check if that delimiter
is
already present in the value. In other words, it is simpler.


Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Hi Vandergahst,

Thanks it works perfectly (I used the second option with a mix of " and '.
One last thing, how do I chop the statement up using " & _ at the end of
each line, everytime I try it I get an error message...

Cheers,
GTL

vanderghast said:
Indeed, the delimiter for the string is a double quote, so VBA assumes you
said:


DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,"

given that the string ends as soon as it sees a second ". You can try:

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,""ALL"",[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server, [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"



since 'doubling' the " indicates, in VBA, that you don't mean to stop the
string at that point.



DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,'ALL',[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server, [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"


would also do, since ' are an alternative delimiter for string.



Vanderghast, Access MVP

GLT said:
Hi Vandeghast,

Thanks for your reply - I have constructed the following SQL in the
Q.Design
Grid and it worked OK, however when converted that into the DoCmd.RunSQL
statement, it errors at the "ALL" point, with a syntax error.

Can anyone advise why and what I need to do to fix it?

Cheers,
GLT.

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service
Name] )
SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,"ALL",[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server, [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"


vanderghast said:
DoCmd.RunSQL "INSERT INTO tableName(server, type, error) VALUES(
FORMS!formNameHere!server, FORMS!formNameHere!type,
FORMS!formNameHere!error)"

would do in a simpler case. In your case, the
FORMS!formNameHere!server
has to be replace with an iif statement (for which I have some problem to
follow your specs.)


Using DoCmd allows to use FORMS!formName!ControlName WITHOUT having to
use delimiter (for strings and dates), neither to check if that delimiter
is
already present in the value. In other words, it is simpler.


Vanderghast, Access MVP


Hi,

I have three feilds: SERVER, TYPE, ERROR

I would like to simply add a new record (three feilds mentioned above)
to
a
table using VBA (when a user clicks a button), however my form also
contains
an option group, and if the first option button is selected then SERVER
=
ALL, if the second option button is selected, then SERVER = servname.

Can anyone advise the SQL statement I need to use?

Any help is always greatly appreciated...

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
Thanks it works perfectly (I used the second option with a mix of " and '.
One last thing, how do I chop the statement up using " & _ at the end of
each line, everytime I try it I get an error message...
Click to expand...

You can't put a continuation line in the middle of a text literal, but it
should work fine if you end the text literal; follow it with a blank, an
underscore, and an immediate carriage return. If this isn't working please
post the actual code that's giving you an error.
 
Hi John,

Thanks for your reply, here is the code that works when I paste it as one
line, but flops when I try to split it over several lines:

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service Name]
) SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,'ALL',[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server,[FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"

Cheers,
GLT.
 
That would look like the following:
Dim strSQL as String

strSQL ="INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"

I must say that I am surprised that the above works for you.
strSQL ="INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" Values (""" & [FORMS]![frmAddPermNoMon]![fldSelShutType] & """" & _
", """ &
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,"ALL",[FORMS]![frmAddPermNoMon]![fldSelServer])
& """" & _
", """ & [FORMS]![frmAddPermNoMon]![fldSelSevice] & """)"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi John,

Thanks for your reply, here is the code that works when I paste it as one
line, but flops when I try to split it over several lines:

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service Name]
) SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,'ALL',[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server,[FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"

Cheers,
GLT.

John W. Vinson said:
You can't put a continuation line in the middle of a text literal, but it
should work fine if you end the text literal; follow it with a blank, an
underscore, and an immediate carriage return. If this isn't working please
post the actual code that's giving you an error.
Click to expand...
Click to expand...
 
Hi John,

Thanks for your reply, I used the fist example and it works pefrectly thx.

Im sticking with the first example - too many quotes is way to confusing :)

John Spencer said:
That would look like the following:
Dim strSQL as String

strSQL ="INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"

I must say that I am surprised that the above works for you.
strSQL ="INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" Values (""" & [FORMS]![frmAddPermNoMon]![fldSelShutType] & """" & _
", """ &
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,"ALL",[FORMS]![frmAddPermNoMon]![fldSelServer])
& """" & _
", """ & [FORMS]![frmAddPermNoMon]![fldSelSevice] & """)"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi John,

Thanks for your reply, here is the code that works when I paste it as one
line, but flops when I try to split it over several lines:

DoCmd.RunSQL "INSERT INTO tblPermSrvcsIgnore ( Type, Server, [Service Name]
) SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type,
IIf([FORMS]![frmAddPermNoMon]![Frame7]=2,'ALL',[FORMS]![frmAddPermNoMon]![fldSelServer])
AS Server,[FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"

Cheers,
GLT.

John W. Vinson said:
Thanks it works perfectly (I used the second option with a mix of " and '.
One last thing, how do I chop the statement up using " & _ at the end of
each line, everytime I try it I get an error message...
You can't put a continuation line in the middle of a text literal, but it
should work fine if you end the text literal; follow it with a blank, an
underscore, and an immediate carriage return. If this isn't working please
post the actual code that's giving you an error.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top