Returning Loaned Videos (1 Viewer)

I'm creating a form for returning videos to a video rental store by including the ID of the video being returned, the ID of the member returning it and the dates of rental and return. I have done it using two separate queries but I was searching for a way of doing it so that the computer will automatically amend the returned video from the table of videos on loan to the table of returned videos. Is this possible?

I believe you may need to take a look at your table structure. You have a table for loaned videos, and a table for returned videos. You should really only have one table called tblLoans.

THis table should include what you've said, VideoID, CustomerID, DateLoaned, DateReturned, and any of fields you like.

For all videos currently on loan you would run a query on this table but show only the record where DateReturned is Null. This also keeps your video history more intact, if you ever need to search the history on a video it's all in one location.

Hope this helps,