How do I update an inventory column in DataSheet Form??

[Guest]

Friends,
How can one update an Inventory Quan field on a DataSheet or Tabular Form?

Thank you,
Paul

 

[Arvin Meyer [MVP]]

A number of ways. The easiest is to use an Update query. If you just want
the records in a particular form, the best way is to use the update method
of a recordset and use the form's recordsetclone as the recordset. Here's
some code that I once used to update a date, that might help serve as a
guidline. Remeber to set a reference to DAO:

Dim rst as DAO.Recordset
Set rst = Me![MySubFormControl].Form.RecordsetClone
With rst
If .RecordCount = 0 Then
.MoveFirst
Do While Not .EOF
.Edit
![DueDate] = Date() + 15
.Update
.MoveNext
Loop
End If
End With
rst.Close
Set rst = Nothing

 

[Arvin Meyer [MVP]]

Yes, it's feasible. You can build a recordset and update each record or
records as the quantity is entered. It sounds like you are storing a
calculation which is unnecessary, and against normalization rules. If you
have the quantity purchased, and the quantity sold or shipped, you always
have the quantity on hand. Simply run a query which totals both purchased
and shipped, and then subtract 1 from the other.

When you store the calculated value, it is also easier to get everything out
of sync. Suppose the user forgets whether they updated or not?