Adding a Previous Owner Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form to track equipment. On the form users must have a
asset # and must assign the equipment to a person.

I would now like to add a way that when you change the user (chosen from a
drop down list) field it stores the previous users name in a field in my
table.

How do I go about doing this or is would you suggest another way?
 
It's not entirely clear how your database is set up, but I think I see what's
generally going on. My suggestions are based on the assumption that an
employee will sign out a piece of equipment, then return it, then another
persons will sign it out, etc. One thing that isn't clear is "On the form
users must have a asset #". Does each employee have their own asset #, or
does the equipment have an asset #, or what exactly?
Leaving that aside, I think you need an employees table (tblEmployees), an
equipment table (tblEquip) and a sign out table (tblSignOut). Something like
this:

tblEmployees
EmployeeID (primary key, or PK)
Employee name
Etc.

tblEquip
EquipID (PK)
Description
Serial Number
Etc.

tblSignOut
SignOutID (PK)
EmployeeID (foreign key, or FK)
EquipID (FK)
Date Out
Date In
Etc.

You need to identify the PKs in table design view. You establish the FKs in
the relationships window by adding all three tables, then dragging EmployeeID
from tblEmployees to tblSignOut. Click Enforce Referential Integrity.
Repeat for EquipID. If you base a form (frmEquip) on tblEquip and another
(frmSignOut) on tblSignOut, then drag frmSignOut onto frmEquip in form design
view, you will establish a form/subform relationship to correspond to the
table relationship you have already set up. You can use the combo box wizard
to create a combo box for frmEquip to select a piece of equipment, and
another on frmSignOut to select an employee from a list based on
tblEmployees. This is a general outline, so post back if you need details.
It sounds like what you are trying to do is to store the name of a previous
user in one field and the name of the current user in another. That really
is spreadsheet thinking, and does not lend itself well to a relational
database. In a relational database such as I have outlined you will be
adding records, not modifying an existing record over and over. It will
enable you not only to see the previous user, but the entire history of that
piece of equipment. And it's probably easier to set up and less apt to cause
problems than a system for modifying an existing record.
 
Thanks for your reply.

You are correct in how I have set up my database and what I want to
accomplish.
Each employee have their own emp #, and the equipment has an asset #.

I have all the forms already created I just want to add this feature so that
I can track the history of that piece of equipment.

I am going to try adding the tblSignOut as I already have the others.
I don't necissarily need the previous user to be displayed on the form, if
its easier I could have this info stored only in the table for additional
information purposes..
 
I have created a form to track equipment. On the form users must have a
asset # and must assign the equipment to a person.

I would now like to add a way that when you change the user (chosen from a
drop down list) field it stores the previous users name in a field in my
table.

How do I go about doing this or is would you suggest another way?
Click to expand...

Have *two* tables in a one to many relationship. The second table
would be an EquipmentOwner table with fields for the asset number, the
unique id of the person, and an effective date; you could use a
Subform on your form, sorted in descending chronological order, to
show the current owner first.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top