Run cmd action on Form Submit

[glenmac]

I have a form with cmd button on it that inserts value in a table. Is there a way to have the value in the same record the form creates when submitted. As it stands now 2 records are created one from the form and one from the cmd button. I need to input the values in the same record.
here's the code from the cmd button.

Private Sub Command36_Click()

On Error GoTo Err_Command36_Click

For Each num In List37.ItemsSelected
var = var & List37.ItemData(num) & "_"
Next
If Len(var) > 0 Then
DoCmd.RunSQL "insert into Orders (Custom_Ingedients) values ('" & Left(var, Len(var) - 1) & "')"
'MsgBox var
End If



Exit_Command36_Click:
    Exit Sub

Err_Command36_Click:
    If (Err = ERR_OBJNOTEXIST) Or (Err = ERR_OBJNOTSET) Then
      Resume Next
    End If
    MsgBox Err.Description
   Resume Exit_Command36_Click
    
End Sub

All help will be greatly appreciated !!

 

[psemianonymous]

RunSQL can't run SELECT queries, SELECT statments don't do anything. Use currentdb.openrecordset() or currentdb.execute() instead.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[glenmac]

Thanks

 

[glenmac]

Can you help me assign a variable to this statement? Then I want to use that variable in the update statement.
CurrentDb.Execute "SELECT Max([Order_ID]) FROM Orders;"