Re: Sql code in access form in adp

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


"Roger" <Roger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7AF95CF8-A26B-4681-A42A-3A2ADF30AC60@xxxxxxxxxxxxxxxx

--
Learning is Never ending. So is certification.


"Sylvain Lafontaine" wrote:

Probably missing blank spaces.

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


"Roger" <Roger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4C14FC0C-A863-4F2C-A275-8B1AB3A7FA4F@xxxxxxxxxxxxxxxx
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!!!!!!!
--
Learning is Never ending. So is certification.


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


.

Relevant Pages

  • Re: SQL to read an entry on a form
    ... dynamically building a sql string containing the value of a control, ... value must be put inside the sql string in the VBA code; ... Sylvain Lafontaine, ing. ... the three letters the user types into a text box on the form. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Need help with ADP code
    ... You should the sql string into a variable and display it in a message box to ... Sylvain Lafontaine, ing. ... Dim strSQL As String, strOrder As String, strWhere As String ... 'Constant Select statement for the RowSource ...
    (microsoft.public.access.formscoding)
  • Re: Simple Insert Into...
    ... You can store unbound control data in a variable, ... in the sql. ... Dim strSQL As String ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple Insert Into...
    ... The form is based on an SQL in the form's Record Source property, ... control on the form with the resulting value as a bound control. ... Sometimes the unbound controls do have a value (such as when they are set ... Dim strVar as String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Sql code in access form in adp
    ... BUT IT STILL DOESNT RUN - not your fault. ... My two exemple above build the whole sql string using all required values ... Sylvain Lafontaine, ing. ...
    (microsoft.public.access.adp.sqlserver)