Insert statement with where clause

Question

Possible Duplicate:
Query Syntax error

I want insert value using the below command

DoCmd.RunSQL = "insert into tblContract(
 Empid,
 Start1,
 Finish1,
 Store1,
 Start2,
 Finish2,
 Store2)
values ('" & Me.txtEmpNo.Value & "','" 
  & Me.txtContSunStart1.Value & "', '"
  & Me.txtContSunFinish1.Value & "','" 
  & Me.txtContSunStore1.Value & "','" 
  & Me.txtContSunStart2.Value & "', '" 
  & Me.txtContSunFinish2.Value & "','" 
  & Me.txtContSunStore2.Value & "')"

But while inserting it should insert in the row where value of a column(testid) in the same table is max(testid) of another table.

This query not working :

SQL = " INSERT INTO tblContract (Empid,testid, Start1, Finish1, Store1, Start2, Finish2, Store2) SELECT " & Me.txtEmpNo.Value & "', MAX(testid), '" & Me.txtContSunStart1.Value & "', '" & Me.txtContSunFinish1.Value & "','" & Me.txtContSunStore1.Value & "','" & Me.txtContSunStart2.Value & "', '" & Me.txtContSunFinish2.Value & "','" & Me.txtContSunStore2.Value & "' " & "FROM tbltesting'"

I am finding an alternative for this . In ms access it gives compile error

Answer 1

You can use an INSERT SELECT instead of an INSERT VALUES. such as.

INSERT INTO MyTable (ColumnA, ColumnB, ColumnC) SELECT 'A', 'B', MAX(ColumnC) FROM MyOtherTable

On another note though, you should NOT build up your SQL as you are. This is prone to SQL Injection. Someone could quite easily enter "; DROP TABLE tblContract" into one of your text boxes!

Answer 2

First you will need to grab the value from the other table and then used that to insert into this table, you can also use a trigger to do that. Be aware that you can have the same value twice if 2 users execute the same query at the same time...you will need lock the other table in that case...max() is problematic

why not use identity and then the scope_identity() function to grab after you insert into the other table?

you also understand that what you doing is a sql injection attack waiting to happen

Answer 3

If I read your question correctly you want

INSERT INTO tblContract(col1..., testId) values
    ('value1',..., (SELECT MAX(testID) FROM tblOther));

Be warned this may lead to concurrency issues