[RESOLVED] Updating two tables at once.

[soutie]

Hi all,

I have two tables in my database. Each table has a form where i use the Insert into command to add data. i also have a "Master edit page" where values from both tables are on the form and can be edited. The problem im having is updating both tables with a single click(cmdSave).

Private Sub cmdSave_Click()

Set r = New ADODB.Recordset
With r
    .ActiveConnection = c
  
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Source = "SELECT* FROM Main WHERE orderno=" & "'" & cmbOrderno.Text & "'"
        .Open
        .Fields("quantity") = (Me.txtQuantity.Text)
        .Fields("quantity2") = (Me.txtQuantity2.Text)
        .Fields("unitprice") = Val(Me.txtUnitprice.Text)
        .Update
        .Close
End With
Set r = New ADODB.Recordset
With r
    .ActiveConnection = c
  
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Source = "SELECT* FROM orders WHERE orderno=" & "'" & cmbOrderno.Text & "'"
        .Open
        .Fields("ordershipshipdate") = (Me.txtOrdershipshipdate.Value)
        .Fields("ordershipshipdetail") = (Me.txtOrdershipshipdetail.Text)
        .Update
        .Close
MsgBox "Order Details Updated Successfully!", vbInformation
End With
End Sub

The second table does not get updated. Any help would be greatly appreciated. (vb6 with access 2007)
Brian

[nasir1]

Good idea, anybody will reply and increase my knowledge also. I also need this information?????

[soutie]

I figured it out. for anyone else who might be stuck with this, this is how i solved my problem.

Private Sub cmdSave_Click()

Set r = New ADODB.Recordset
Set r2 = New ADODB.Recordset

With r
    .ActiveConnection = c
  
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Source = "SELECT* FROM TABLE1 WHERE orderno=" & "'" & cmbOrderno.Text & "'"
        .Open
        .Fields("quantity") = (Me.txtQuantity.Text)
        .Fields("quantity2") = (Me.txtQuantity2.Text)
        .Fields("unitprice") = Val(Me.txtUnitprice.Text)
        .Update
        .Close
End With

With r2
    .ActiveConnection = c2
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Source = "SELECT* FROM orders WHERE orderno=" & "'" & cmbOrderno.Text & "'"
        .Open
        .Fields("ordershipshipdate") = (Me.txtOrdershipshipdate.Value)
        .Fields("ordershipshipdetail") = (Me.txtOrdershipshipdetail.Text)
        .Update
        .Close
End With
MsgBox "Order Details Updated Successfully!", vbInformation
End Sub

Note: I also created the above new connections and recordsets in my main module.

[baadil]

When you need to update multiple tables, it is best to put them in a stored procedure. Then you can send all of the required information to the stored procedure and SP will perform the inserts. SP can also put your inserts into a transaction as well use Try-Catch. This way not only you can insert them properly / in-order but also catch/report any exceptions easily.