CODEWITHSUNDEEP
ms-accessvba
user1015214
user1015214

Reputation: 3081

issue editing Access database table with OpenRecordSet

I am very new to access. I am trying to update a table using OpenRecordSet and am having issues. Here is my code:

Set rst2 = CurrentDb.OpenRecordset("tbl_Inventory", dbOpenTable)
With rst2
.Edit
If !InventoryID = Me.InventoryID Then

!Stock = Me.Quantity
.Update
.Close
End If
End With
Set rst2 = Nothing

This is called when an update button is clicked on an Inventory Form page. There is an update button and an input box, "Quantity" for every row on the form. Here is my issue:

1) When I click the update button, ALL of the Quantity input boxes (for each item) is changed to that new number. How do I specify just that line?

2) When I click update on the FIRST row, ID=1, and look back at the inventory form, I see that the changes have been made to the Stock column. But when I click the other rows, there is no change. I have a feeling that these stem from one underlining issue.

Upvotes: 0

Views: 490

Answers (2)

Johnny Bones
Johnny Bones

Reputation: 8402

You have no rs.MoveNext line in your code. Try this:

Set rst2 = CurrentDb.OpenRecordset("tbl_Inventory", dbOpenTable)

Do While rst2.eof = false

With rst2
If !InventoryID = Me.InventoryID Then
   .Edit
   !Stock = Me.Quantity
   .Update
End If

End With

rst2.MoveNext

Loop
rst2.close
Set rst2 = Nothing

Upvotes: 1

Grant
Grant

Reputation: 903

How are you selecting a single record to change? Have you tried using the .FindFirst?

So after With put:

.FindFirst ("[InventoryID] = '" & Me.InventoryID & "'")

That will make sure you are only using that one record.

Upvotes: 0

Related Questions