[RESOLVED] Confusion over different tables and sql update, insert etc

[soutie]

Hi all,

I'm at my wits end and have nearly yanked all my hair out. Initially i was tasked to make a simple db app with around 30 fields so it had just one table, my boss now keeps adding more and more fields and i have 200 fields in a single table.

I have tried adding a new table: main, ordership. The fields for the main table are filled in another form. I have a form (FRMordership) which should then add the values into the ordership table. orderno is the primary key and relationship is based on this. The problem is i have a combobox on the FRMordership which should be loaded with values in the field (orderno) from the main table. So users can select the orderno from the combobox and when they click save it should add the values into the ordership table. My code so far :

Private Sub Form_Load()
Dim c As New ADODB.Connection
Dim r As New ADODB.Recordset
Dim rSearch As New ADODB.Recordset
Dim rCheck As New ADODB.Recordset
Dim s As String
Dim search As String


s = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database\data.mdb;Persist Security Info=False;Jet OLEDB:Database Password=iamthestig;"
c.Open s
r.Open "main", c, adOpenDynamic, adLockOptimistic
r.Requery


Do While Not r.EOF
Combo1.AddItem r!orderno
r.MoveNext
Loop
End Sub

Private Sub cmdSave_Click()

    c.Execute ("Update ordership set ordershipshipdate='" & txtOrdershipshipdate.Value & "',ordershipshipdetail='" & txtOrdershipshipdetail.Text & "',ordershipcontainerarrivaldate='" & txtOrdershipcontainerarrivaldate.Value & "',osratios='" & txtOSRatioS.Text & "',osratiom='" & txtOSRatioM.Text & "', osratiol='" & txtOSRatioL.Text & "', osratioxl ='" & txtOSRatioXL.Text & "', osratioxxl ='" & txtOSRatioXXL.Text & "'where orderno='" & Combo1.Text & "';")
    MsgBox "Order Shipping Details Updated Successfully!", vbInformation, "Save"

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

what am i doing wrong? when i open the database no values are there in the ordership table. any help/advice would be immensely appreciated
Brian