New record with previous data

[CJ]

Hi Groupies:

I am tracking inventory and need to keep a record of where a piece of
equipment has been and where it is now.
I have a query, qryInventoryTracking, that allows me to select a piece of
equipment and assign it to a Customer. If equipment is assigned to a
Customer it is not available for other records. However, when the equipment
is returned to us, I need to keep a history of where it has been but make it
available back in the inventory

So, I need this to happen:

Date Equip Location

June 6, 2008 Item 1 Customer A
July 12, 2008 Item 1 Inventory
August 3, 2008 Item 1 Customer W
March 9, 2009 Item 1 Inventory

I was thinking of putting in a ysnInventoryReturn check box on my form. When
it is selected, the current record becomes locked and a new record for this
item is created with the location set as inventory.

Can somebody please help me with the "a new record for this item is created
with the location set as inventory" part? I know it would be the after
update event but how do you copy the correct record and set it's location?

 

[Graham Mandeno]

Hi CJ

Have you considered having two date fields - DateOut and DateReturned? Your
data would then look like this:

DateOut DateReturned Equip Customer

June 6, 2008 July 12, 2008 Item 1 Customer A
August 3, 2008 March 9, 2009 Item 1 Customer W
October 1, 2008 Item 2 Customer A

Items that are currently unavailable would be those with a record in the
Assignments table where DateReturned is null.

 

[CJ]

I prefer your method Graham, it offers much more flexibility.
Sometimes another set of eyes is all that is required.

Thanks a bunch!

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

Hi CJ

Have you considered having two date fields - DateOut and DateReturned?
Your data would then look like this:

DateOut DateReturned Equip Customer

June 6, 2008 July 12, 2008 Item 1 Customer A
August 3, 2008 March 9, 2009 Item 1 Customer W
October 1, 2008 Item 2 Customer A

Items that are currently unavailable would be those with a record in the
Assignments table where DateReturned is null.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[AdminAmber]

I had the exact same question today and thought about it forever with almost
the same conclusion as CJ. (check box)

The date field was so easy and obvious once you said it.

Thanks, you're awesome!

 

[Graham Mandeno]

Hi AdminAmber

Thanks for the feedback. You might like to take a look at another thread
started by CJ about 28 hours ago where I show how to use a filter to select
either those items which are currently "out" or those which are "available".

 

[CJ]

Hi

I ran into another problem after using the date field so also check out the
thread: Update sub before record loads on 6/18/2008, it might be useful for
you.

CJ