How can I best do this? - Access 2002

[Andrew Dorph]

I have a database with 3 tables - Equipment, History and Employee.

The relationships are:
One-to-Many From Equipment to History
Many-to-One From History to Employee

I want to set up a means by which a user can select an instance of
Equipment from a list.

When the instance is selected, I want to display (read only) the
equipment history showing when the equipment went to an employee and
when it left their possession (located in the History table) along
with the employee's name and room number (located in the Employee
table).

Thanks much!

- Andrew Dorph

[Salad]

Andrew Dorph wrote:

I have a database with 3 tables - Equipment, History and Employee.

The relationships are:
One-to-Many From Equipment to History
Many-to-One From History to Employee

I want to set up a means by which a user can select an instance of
Equipment from a list.

When the instance is selected, I want to display (read only) the
equipment history showing when the equipment went to an employee and
when it left their possession (located in the History table) along
with the employee's name and room number (located in the Employee
table).

Thanks much!

- Andrew Dorph

Create a form to detail the histor/employee records. Then in the (main)
form where you select the equipment, from the toolbox (with form open
from menu select View/Toolbox) drop a subform control into the form.
Then change the filter as the equipment changes.

[Don Leverton]

Hi Andrew,

Try designing a mainform based on the "Equipment" table, and two subforms,
based on the other two tables.

Assuming the following table and field names, and that relationships are
defined with referential integrity / cascading updates & deletes:
---------------

Equipment (table)
EquipID -- AutoNumber -- PK
---------------

History (table)
HistoryID -- AutoNumber -- PK
EquipID -- Long Integer -- FK
EmployeeID -- Long Integer -- FK

---------------
Employee (table)
EmployeeID -- AutoNumber -- PK

----------------
frmEquipment -- the name of the main form
sbfHistory -- the name of the History subform
sbfEmployee -- the name of the Employee subform

---------------

You'll ned an unbound textbox on the main form (frmEquipment) named say ...
"txtEmployeeID" which will have "=Me![sbfHistory].Form![EmployeeID] as it's
ConrolSource.

Use "EquipID" as both the LinkMaster/ LinkChild for "sbfHistory"
Use "txtEmployeeID" as the LinkMaster, and EmployeeID as the LinkChild for
"sbfEmployees"

Using this strategy, you should be able to:
1.) Select the Equipment record ... use the combo-box wizard and the "find a
record on my form" option for this.
2.) In sbfHistory, select the record that contains the time period.
3.) The related Employee information should be displayed in "sbfEmployee"

Another advantage with this is that the form can be used for viewing,
editing, and adding data.
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"Andrew Dorph" <an********@ao.uscourts.gov> wrote in message
news:dc**************************@posting.google.c om...

I have a database with 3 tables - Equipment, History and Employee.

The relationships are:
One-to-Many From Equipment to History
Many-to-One From History to Employee

I want to set up a means by which a user can select an instance of
Equipment from a list.

When the instance is selected, I want to display (read only) the
equipment history showing when the equipment went to an employee and
when it left their possession (located in the History table) along
with the employee's name and room number (located in the Employee
table).

Thanks much!

- Andrew Dorph