Tracking changes to data on a form

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

I've got an inventory database and would like to track
movement between locations.

How am I able to track this information in a way that I
can produce a report that shows me what the location was
before the change was made and the new location?

Thanks,

AJ
 
In order to do that, you would need a table to store the stock movements.
- MoveID primary key
- FromLocationID foreign key to tblLocation.LocationID
- ToLocationID foreign key to tblLocation.LocationID
- MoveDate Date/Time when moved.
And a related table to store the items moved:
- MoveDetailID primary key
- MoveID foreign key to the above table
- Quantity amount moved
- ProductID foreign key to tblProduct.ProductID
 
You first have to decide how many changes you want to track. If an item is moved 4 times,
do you want all 4 or just the last move?

If all you want is the last move, then create another field next to the one for the
current location and call is something like PreviousLocation. Place a textbox on a form
bound to this field. On the form where you make the change, in the BeforeUpdate event of
the form set the value of this new textbox to the OldValue of the location control.

Example:
If Me.txtLocation.Value <> Me.txtLocation.OldValue Then
Me.txtPreviousLocation = Me.txtLocation.OldValue
End If

Doing this in the Form's BeforeUpdate event will do this just before the record is saved.
If you do it when you change the location control, then there could be a problem if you
change you mind about making the entry.

If you want to keep track of all of the moves, you will need to set up a table with fields
for a unique ID for each item, the location before the move, and the date/time of the
move. In the BeforeUpdate event of the form you would make the same check as above, and
then write the data to the movement table.

Example:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblMovements", dbOpenDynaset)
With rst
.AddNew
!Item = Me.txtItemID
!PreviousLocation = Me.txtLocation.OldValue
!WhenMoved = Now
.Update
.Close
End With
Set rst = Nothing
 
Back
Top