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)

Code:
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