Add a record to a table using VBA

[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.

 

[vanderghast]

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

 

[GLT]

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]

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

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];"

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

 

[GLT]

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

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

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];"

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.

 

[John W. Vinson]

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.

 

[GLT]

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 Spencer]

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.

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.

 

[GLT]

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

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.

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.

.