[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

[soutie]

ok i sort of figured it out but i still have a major problem. My records in the ordership table keep getting overwritten so if i run the program twice the new information i enter overwrites the first one.

Private Sub cmdSave_Click()

    c.Execute ("Update ordership set orderno='" & Combo1.Text & "';")
    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

[RhinoBull]

What do you epect? You are updating all records by executing the following statement:

c.Execute ("Update ordership set orderno='" & Combo1.Text & "';")

Instead you probably need to use "Insert Into..."
Your second Update statement updates specific record (for specific order number) - this isn't most likely necessary as Insert Into would take care of this anyway.

So, the bottom line is:
- when you need to create new record use "Insert Into..."
- when you need change data use "Update..."

[soutie]

thanks RhinoBull.