help with inserting into two tables

[soutie]

Hi all,

I'm using vb6 frontend and access 2007 for db. I have two tables, 'main' and 'prices'. On my form i have multiple textboxes and some of these need to go into the main table and the others into the prices table.

I had initially used one table with all the fields but decided it wiser to have two tables. so my current code is as follows: (please note i have reduced the number of fields to make it easier to read below)

Private Sub cmdSave_Click()
On Error GoTo ErrHand
If txtOrdernumber.Text = "" Then
    MsgBox "Enter Order Number First!", vbCritical, "Save"
ElseIf txtDeliverydate.Value = "" Then
    MsgBox "Select a Date!", vbCritical, "Save"
Else
    Set rCheck = c.Execute("Select * from main, where orderno='" & txtOrdernumber.Text & "';")
    If rCheck.EOF Then
        c.Execute "Insert into main(orderno, unitprice, unitprice2, subtotal, subtotal2, Username) values" & _
        "('" & txtOrdernumber.Text & "','" & Val(txtUnitprice.Text) & "','" & Val(txtUnitprice2.Text) & "','" & Val(txtSubtotal.Text) & "','" & Val(txtSubtotal2.Text) & "','" & frmLogon.nUSER & " ');"
        MsgBox "Order Saved Successfully!", vbInformation, "Save"
        r.Requery
     
    Else
        MsgBox "Record already exists! If you want to edit, go to the View and Edit Page"
    End If
End If
Exit Sub

ErrHand:
    MsgBox Err.Description, vbCritical, "Error"
End Sub

so now if i want orderno and username to go into the main table and the various prices to go into the prices table what syntax should i use?

Any help would be greatly appreciated!
Cheers,
Brian

[GaryMazzone]

You write 2 seperate Insert statements, you will also need the field that links the two tables together. How is that generated? If it is an Autonumber then you need to retrieve that after the first insert and use the result in the second insert

[soutie]

the primary key is the orderno field which is keyed in once and then on it is loaded into a combobox and everytime they update the records they select it from the combobox. (orderno is in the main table and prices table)

So is this what i should be doing?

Set rCheck = c.Execute("Select * from main, where orderno='" & txtOrdernumber.Text & "';")
    If rCheck.EOF Then
        c.Execute "Insert into main(orderno, Username) values" & _
        "('" & txtOrdernumber.Text & "','" & frmLogon.nUSER & " ');"
        MsgBox "Order Saved Successfully!", vbInformation, "Save"
        r.Requery

c.Execute "Insert into prices(unitprice, unitprice2, subtotal, subtotal2) values" & _
"('" & Val(txtUnitprice.Text) & "','" & Val(txtUnitprice2.Text) & "','" & Val(txtSubtotal.Text) & "','" & Val(txtSubtotal2.Text) & "');"
     
    Else
        MsgBox "Record already exists! If you want to edit, go to the View and Edit Page"
    End If