Sql code in access form in adp

[Guest]

Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form to a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

 

[Sylvain Lafontaine]

Probably missing blank spaces.

 

[Sylvain Lafontaine]

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
SQL-Server. You must convert the value before calling DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

 

[Guest]

--
Learning is Never ending. So is certification.

Probably missing blank spaces.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form to a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

Thanks for that

Heres one a little more challengingI assume that the from line is wrong as
well. I need to tansfer all data from this form to a table - about 7
controls. What do i need to change for this to work.
I only put 2 here for brevity.

Much appreciated

 

[Sylvain Lafontaine]

Forget about my last answer, the missing blank spaces and name of the
control after the name of the form got me distracted. Here is your correct
solution for two fields:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

Notice that the values of adm_name and Problem have been delimited by single
quote ' because they are probably string characters from their name. If
they are integer values, then drop these single quotes. Another solution
would be to use the keyword Values instead of a Select statement:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

The Select statement can be used to insert multiple records in a single
statement, however you cannot use this feature here because it requires that
the source is a table; not a form or a list of values.

Finally, make sure that the SQL string is correct by displaying it in a
message box.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

--
Learning is Never ending. So is certification.

Probably missing blank spaces.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form to
a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

Thanks for that

Heres one a little more challengingI assume that the from line is wrong as
well. I need to tansfer all data from this form to a table - about 7
controls. What do i need to change for this to work.
I only put 2 here for brevity.

Much appreciated

 

[Guest]

Is this in conjunction with the last posting which includes the sql coding.

If so then I cannot do what i want because I am referring to several text
boxes on one form and not just one. OR can i like it together and transfer
end box indiviually.

Would this work
Insert Into log
set admin = adm_name, issue=problem
from form!frm_tick_entry

Or is it the same problem.

Thankyou for your assistance - thiis is driving me mad.

I had tried the sql from the other posts - nothing happens at all - doesnt
even throw up an error.

--
Learning is Never ending. So is certification.

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
SQL-Server. You must convert the value before calling DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form to a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

 

[Sylvain Lafontaine]

Hi,

I'm not sure to which "last posting" you are making a reference. Is
this the one with the following piece of code:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

or:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

These two exemples have two fields, so there wouldn't be any real difference
if you want to have more.

And now about your last example:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

This one cannot work because you cannot make a Select statement from a Form
because SQL-Server doesn't know anything about the forms on your local
machine. (Don't forget that all SQL code are running "remotely" on the
server, even when the server is on the same local machine.)

My two exemple above build the whole sql string using all required values
from the form. When this string has been built, it can be sent to the
server to be run there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Is this in conjunction with the last posting which includes the sql
coding.

If so then I cannot do what i want because I am referring to several text
boxes on one form and not just one. OR can i like it together and transfer
end box indiviually.

Would this work
Insert Into log
set admin = adm_name, issue=problem
from form!frm_tick_entry

Or is it the same problem.

Thankyou for your assistance - thiis is driving me mad.

I had tried the sql from the other posts - nothing happens at all - doesnt
even throw up an error.

--
Learning is Never ending. So is certification.

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
SQL-Server. You must convert the value before calling DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form to
a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

 

[Guest]

Thanks for that - you have giivejn me an essential piece of information
about where the query acvtually runs.

BUT IT STILL DOESNT RUN - not your fault.

Where should this click reside, currently on the on click function in a
command button (as cose)!

Would an "autonumber field in the table be interfering.

Is there ANYWHERE else should i be looking. No errors are thrown up or
anything indicating the problem.

I am the virge of giving up on this.

Thanks for all the help you have given me.
--
Learning is Never ending. So is certification.

Hi,

I'm not sure to which "last posting" you are making a reference. Is
this the one with the following piece of code:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

or:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

These two exemples have two fields, so there wouldn't be any real difference
if you want to have more.

And now about your last example:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

This one cannot work because you cannot make a Select statement from a Form
because SQL-Server doesn't know anything about the forms on your local
machine. (Don't forget that all SQL code are running "remotely" on the
server, even when the server is on the same local machine.)

My two exemple above build the whole sql string using all required values
from the form. When this string has been built, it can be sent to the
server to be run there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Is this in conjunction with the last posting which includes the sql
coding.

If so then I cannot do what i want because I am referring to several text
boxes on one form and not just one. OR can i like it together and transfer
end box indiviually.

Would this work
Insert Into log
set admin = adm_name, issue=problem
from form!frm_tick_entry

Or is it the same problem.

Thankyou for your assistance - thiis is driving me mad.

I had tried the sql from the other posts - nothing happens at all - doesnt
even throw up an error.

--
Learning is Never ending. So is certification.

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
SQL-Server. You must convert the value before calling DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form to
a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

 

[Sylvain Lafontaine]

This is a big difference between ADP and MDB. With MDB, excerpt for the
special case of sql pass-through, everything run locally inside jet while
with ADP (or SQL Pass-through), everything run remotely on the SQL-Server.

If it doesn't work, then you should show us the final result for the sql
string, just before you try to execute it on the server; to make sure that
there is no syntax error. (Personally, I use Query Analyser but I don't
know if you have it.)

And Yes, an autonumber can be interfering: you should not try to include a
value for the autonumber when making the insertion. (There is a special
command for doing this but this is clearly not your case.)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks for that - you have giivejn me an essential piece of information
about where the query acvtually runs.

BUT IT STILL DOESNT RUN - not your fault.

Where should this click reside, currently on the on click function in a
command button (as cose)!

Would an "autonumber field in the table be interfering.

Is there ANYWHERE else should i be looking. No errors are thrown up or
anything indicating the problem.

I am the virge of giving up on this.

Thanks for all the help you have given me.
--
Learning is Never ending. So is certification.

Hi,

I'm not sure to which "last posting" you are making a reference. Is
this the one with the following piece of code:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

or:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

These two exemples have two fields, so there wouldn't be any real
difference
if you want to have more.

And now about your last example:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

This one cannot work because you cannot make a Select statement from a
Form
because SQL-Server doesn't know anything about the forms on your local
machine. (Don't forget that all SQL code are running "remotely" on the
server, even when the server is on the same local machine.)

My two exemple above build the whole sql string using all required values
from the form. When this string has been built, it can be sent to the
server to be run there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Is this in conjunction with the last posting which includes the sql
coding.

If so then I cannot do what i want because I am referring to several
text
boxes on one form and not just one. OR can i like it together and
transfer
end box indiviually.

Would this work
Insert Into log
set admin = adm_name, issue=problem
from form!frm_tick_entry

Or is it the same problem.

Thankyou for your assistance - thiis is driving me mad.

I had tried the sql from the other posts - nothing happens at all -
doesnt
even throw up an error.

--
Learning is Never ending. So is certification.


:

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
SQL-Server. You must convert the value before calling DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it
between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form
to
a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

 

[Guest]

tHANKS FOR YOUR HELP

Ran this in query analyser
INSERT INTO log ([Admin],[Issue])
select '" & Forms!frm2![adm_name] & "', '" & Forms!frm2![Problem] & "'

Get this answer

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'log'.

This is run directly on the sql server

The table name is log though, the sql server shows dbo.log but when changed
the error is still the same.
--
Learning is Never ending. So is certification.

This is a big difference between ADP and MDB. With MDB, excerpt for the
special case of sql pass-through, everything run locally inside jet while
with ADP (or SQL Pass-through), everything run remotely on the SQL-Server.

If it doesn't work, then you should show us the final result for the sql
string, just before you try to execute it on the server; to make sure that
there is no syntax error. (Personally, I use Query Analyser but I don't
know if you have it.)

And Yes, an autonumber can be interfering: you should not try to include a
value for the autonumber when making the insertion. (There is a special
command for doing this but this is clearly not your case.)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks for that - you have giivejn me an essential piece of information
about where the query acvtually runs.

BUT IT STILL DOESNT RUN - not your fault.

Where should this click reside, currently on the on click function in a
command button (as cose)!

Would an "autonumber field in the table be interfering.

Is there ANYWHERE else should i be looking. No errors are thrown up or
anything indicating the problem.

I am the virge of giving up on this.

Thanks for all the help you have given me.
--
Learning is Never ending. So is certification.

Hi,

I'm not sure to which "last posting" you are making a reference. Is
this the one with the following piece of code:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

or:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

These two exemples have two fields, so there wouldn't be any real
difference
if you want to have more.

And now about your last example:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

This one cannot work because you cannot make a Select statement from a
Form
because SQL-Server doesn't know anything about the forms on your local
machine. (Don't forget that all SQL code are running "remotely" on the
server, even when the server is on the same local machine.)

My two exemple above build the whole sql string using all required values
from the form. When this string has been built, it can be sent to the
server to be run there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Is this in conjunction with the last posting which includes the sql
coding.

If so then I cannot do what i want because I am referring to several
text
boxes on one form and not just one. OR can i like it together and
transfer
end box indiviually.

Would this work
Insert Into log
set admin = adm_name, issue=problem
from form!frm_tick_entry

Or is it the same problem.

Thankyou for your assistance - thiis is driving me mad.

I had tried the sql from the other posts - nothing happens at all -
doesnt
even throw up an error.

--
Learning is Never ending. So is certification.


:

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
SQL-Server. You must convert the value before calling DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it
between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form
to
a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

 

[Sylvain Lafontaine]

Store the sql string into a string variable and show it's value in a message
to make sure that everything is OK.

Also, log is the name of mathematical function, so you will have to enclose
with square brackets: try with [dbo].[log] instead of just log.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

tHANKS FOR YOUR HELP

Ran this in query analyser
INSERT INTO log ([Admin],[Issue])
select '" & Forms!frm2![adm_name] & "', '" & Forms!frm2![Problem] & "'

Get this answer

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'log'.

This is run directly on the sql server

The table name is log though, the sql server shows dbo.log but when
changed
the error is still the same.
--
Learning is Never ending. So is certification.

This is a big difference between ADP and MDB. With MDB, excerpt for the
special case of sql pass-through, everything run locally inside jet while
with ADP (or SQL Pass-through), everything run remotely on the
SQL-Server.

If it doesn't work, then you should show us the final result for the sql
string, just before you try to execute it on the server; to make sure
that
there is no syntax error. (Personally, I use Query Analyser but I don't
know if you have it.)

And Yes, an autonumber can be interfering: you should not try to include
a
value for the autonumber when making the insertion. (There is a special
command for doing this but this is clearly not your case.)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks for that - you have giivejn me an essential piece of
information
about where the query acvtually runs.

BUT IT STILL DOESNT RUN - not your fault.

Where should this click reside, currently on the on click function in
a
command button (as cose)!

Would an "autonumber field in the table be interfering.

Is there ANYWHERE else should i be looking. No errors are thrown up or
anything indicating the problem.

I am the virge of giving up on this.

Thanks for all the help you have given me.
--
Learning is Never ending. So is certification.


:

Hi,

I'm not sure to which "last posting" you are making a reference.
Is
this the one with the following piece of code:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

or:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

These two exemples have two fields, so there wouldn't be any real
difference
if you want to have more.

And now about your last example:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

This one cannot work because you cannot make a Select statement from a
Form
because SQL-Server doesn't know anything about the forms on your local
machine. (Don't forget that all SQL code are running "remotely" on
the
server, even when the server is on the same local machine.)

My two exemple above build the whole sql string using all required
values
from the form. When this string has been built, it can be sent to the
server to be run there.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Is this in conjunction with the last posting which includes the sql
coding.

If so then I cannot do what i want because I am referring to several
text
boxes on one form and not just one. OR can i like it together and
transfer
end box indiviually.

Would this work
Insert Into log
set admin = adm_name, issue=problem
from form!frm_tick_entry

Or is it the same problem.

Thankyou for your assistance - thiis is driving me mad.

I had tried the sql from the other posts - nothing happens at all -
doesnt
even throw up an error.

--
Learning is Never ending. So is certification.


:

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on
the
SQL-Server. You must convert the value before calling
DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it
between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a
form
to
a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!