Help Editing a Table

[BPhilb]

Hello..It's been a while since I've worked in Access and I'm just getting back into it. I'm having a problem getting information to update back into my table from a form. I can get the first record to work fine but subsequent records seem to get the data but will not update into the table. Any help anyone can provide me would be much appreciated.

Private Sub Command16_Click()
Dim db As Database
Set db = CurrentDb()
Dim rst As Recordset, rst1 As Recordset
Set rst = db.OpenRecordset("WM241BASD_IDCASE11")
Dim var As Variant
Dim var1 As Variant
var = Me.PalletID & " "

rst.MoveFirst
Do While Not rst.EOF
var1 = rst("IDCASN")
If var = var1 Then
Set rst1 = db.OpenRecordset("table1")
rst1.Edit
rst1("PalletID") = rst("IDCASN")
rst1("SKU") = rst("IDSTYL")
rst1("Location") = rst("IDZONE") & rst("IDAISL") & rst("IDBAY") & rst("IDLEVL") & rst("IDPOSN")
rst1("DateConsumed") = rst("IDDLM")
rst1("Qty") = rst("IDQTY")
rst1("Color") = rst("IDCOLR")
rst1("DateBuilt") = rst("IDDCR")
rst1.Update

Exit Do
Else
rst.MoveNext
End If
Loop
MsgBox "Update Complete"
Me.Refresh
End Sub

 

[Ascentient]

BPhilb,
How's it going?

Try dimming your recordsets in DAO or ADO
CODE
Dim rst As DAO.Recordset, rst1 As DAO.Recordset
Set rst1 = db.OpenRecordset("WM241BASD_IDCASE11", dbOpenDynaset)
With rst1
.Edit
!PalletID = rst("IDCASN")
!SKU = rst("IDSTYL")
!Location = rst("IDZONE") & rst("IDAISL") & rst("IDBAY") & rst("IDLEVL") & rst("IDPOSN")
!DateConsumed = rst("IDDLM")
!Qty = rst("IDQTY")
!Color = rst("IDCOLR")
!DateBuilt = rst("IDDCR")
.Update
End With

This is UNTESTED, but will probably solve your problem.

Ascentient

 

[JerryKlmns]

BPhilb

You are trying to find that value Me.PalletID & " " at the rst("IDCASN") field, looping through all the records(?!). If found, you edit the first record of the rst1! Is this what you need or you want to add the record to table1? If I m correct then you need

Private Sub Command16_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset, rst1 As DAO.Recordset
Dim var As String

var = Me.PalletID & "          "

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT [WM241BASD_IDCASE11].* FROM [WM241BASD_IDCASE11] WHERE IDCASN='" & var & "'", dbOpenForwardOnly, , dbReadOnly)
If Not (rst.BOF And rst.EOF) Then
  Set rst1 = db.OpenRecordset("Select table1.* From table1", dbOpenForwardOnly, dbAppendOnly, dbOptimistic)
     With rst1
        .AddNew
        .Fields("PalletID") = rst.Fields("IDCASN")
        .Fields("SKU") = rst.Fields("IDSTYL")
        .Fields("Location") = rst.Fields("IDZONE") & rst.Fields("IDAISL") & rst.Fields("IDBAY") & rst.Fields("IDLEVL") & rst.Fields("IDPOSN")
        .Fields("DateConsumed") = rst.Fields("IDDLM")
        .Fields("Qty") = rst.Fields("IDQTY")
        .Fields("Color") = rst.Fields("IDCOLR")
        .Fields("DateBuilt") = rst.Fields("IDDCR")
        .Update
        .Close
     End With
     Set rst1 = Nothing
     MsgBox "Update Complete"
     Me.Refresh
Else
     MsgBox "Not Found in WM241BASD_IDCASE11!"
End If
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub