Afterupdate event code

[Tina]

I want to transfer a piece of equipment from one employee
to another. I have a table, tblInventory, which contains
data for each piece of equipment, such as EquipID
(autonumber field, primary key), equipment type, etc, as
well as the employeeID of the person assigned the equip.
EmployeeID is foreign key to another table, tblEmployee,
which contains data about each employee. EmployeeID is an
autonumber field and primary key for tblEmployee.

I want a form to transfer equipment from one employee to
another. I also want to keep a record of who has owned
the piece of equipment prior to the current assignment.

I think what I should do is create, at least, 3 fields on
the transfer form. The first field is a text box which
shows the EquipID field.

The second field is a text box which shows who currently
has the equipment. Not sure if this field has to be
EmployeeID (autonumber field) or if can have the
concatenated field "FullName".

The third field is a cbo where I select the new
EmployeeID, which is based on a Table/Query which shows
the FullName of the employee, and the bound field is
EmployeeID.

I think I need to have afterupdate event procedure on the
cbo that creates a new record which copies data from the
transfer form, along with new EmployeeID, but not sure how
to do this.

I also think I need code behind the form which appends the
old record to a history table.

Any idea how I can do this?

Thanks in advance,
Tina

 

[Randy Wayne]

First, it is usually not a good idea to use the
AfterUpdate event to create a record in a table. Suppose
you accidently click the EmpID above the one you wanted --
it would create a record in your table before you could
correct it. I suggest a cmdButton for the update.

There are lots of ways to do what you want. One way is to
do what you suggested. Have a field in your inventory
table for "current owner. Your form can change the record
in your inventory table. To create a log of the tools
history you would add an INSERT statement in the button
code BEFORE you execute the update code.


Sample Insert Statement


Dim strTool as String
Dim strEmp as String

strTool = Me.theNameOfTheTextBoxThatHasTheToolID
strEmp = Me.theNameOfTheTextBoxThatHasTheEmpID

"INSERT INTO [HistoryTable] (ToolID, EmpID) " _
& "values (" & strTool & ", " & strEmp & ");"

Finally, you probably would want a Timestamp field in your
History Table to track when each employee had the tool.

Let me know if this helps.