Edit a previous record when checking in/out inventory

[adrianf44]

I have a database that tracks inventory by creating a new record (changing the status from Checked Out to Checked In and placing the current date) when a piece of inventory is checked out and then creates another record when the inventory is checked in. I would like it to add the checked out date to the original record, and only create a new record when the piece of inventory is checked out again.

Reason being is I would like to have a query is shows Checked In date Is Null so it only displays items currently Checked Out. Right now it will show ALL previous records of the item being Checked Out.

Any additional info please ask. Thanks!

[Micron]

When you check out you create a new record (append query) and write the date and/or time to the CheckedOut date field. When you check it in, you Update the record (Update query) and write the date/time to the CheckedIn field. Both operations are acted upon the item id.

[adrianf44]

Thanks for that help! I think i got it working, maybe not the best way but does what i need. I have the update query write the CheckInDate in a record where the SerialNumber = SerialNumber selected and CheckInDate is Null