Copy at least 1 record or more from one table to another table within the same .mdb

[kezmij]

I am currently trying to write an "Inventory Tracking System." I have
a table called "Equipment" that contains information documenting pieces
of equipment ( i.e. Description, Model Number, Serial Number etc. ) and
included in this table are fields with the information of "Who" and
"When" the equipment is loaned out. What I need to do, is to duplicate
the entire record from the "Equipment" table into an "Archive" table
when the equipment is returned back after being "Loaned out" and now
available for redistribution.

Where I'm having trouble is designing code to "Loop" through the
"Equipment" table if a user "Loaned out" Multiple Pieces of Equipment,
similar to a user checking out one or many books from a library.
Can anybody help me with some sample code - ?

[ManningFan]

I think you're going about it wrong.

Why not have a counter that holds the number of pieces of said
equipment available, and when it's returned you add 1 to the counter
and when it's loaned you subtract 1 from the counter? Unless you need
a history of who borrowed something 4 months ago... If that's the
case, track it using a WhoBorrowed table, assign a BorrowID to the
transaction and store it there.

You need to have a better plan or you're going to end up with a
database held together by bandaids. Not everyone can structure a
database properly, you might want to seek advice on how to set it up
first.
[e-mail] wrote:

I am currently trying to write an "Inventory Tracking System." I have
a table called "Equipment" that contains information documenting pieces
of equipment ( i.e. Description, Model Number, Serial Number etc. ) and
included in this table are fields with the information of "Who" and
"When" the equipment is loaned out. What I need to do, is to duplicate
the entire record from the "Equipment" table into an "Archive" table
when the equipment is returned back after being "Loaned out" and now
available for redistribution.

Where I'm having trouble is designing code to "Loop" through the
"Equipment" table if a user "Loaned out" Multiple Pieces of Equipment,
similar to a user checking out one or many books from a library.
Can anybody help me with some sample code - ?

[pietlinden]

I built one of these a few million years ago. It's f-u-n fun. you
don't need to move the records at all. You need to put in a ReturnDate
for the loan. The structure is something like this:

Person(PersonID PK, FirstName, LastName...)
Loan(LoanPersonID, LoanItemID, IssueDate, ReturnDate)
Item(ItemID, Description,LossDate)

of course, if you're scheduling, you have to assume most of the
inventory will be returned. I did it by getting a count of matching
items from the items table and subtracting some number. Then if that's
positive, allow the loan/reservation. Albert Kallal has some
interesting notes on this. Check out his website. (check
www.mvps.org/access to find his website...)

There's no need to archive the data, really. You can if you want,
though. You might want to set a flag in the Items table for flagging
lost items.