Access Form question

[Guest]

I am creating a employee computer inventory database. The tables are
Employee, Hardware, Status.
Fields of tables are-
Employee: EmpID,LastName,FirstName etc.
Hardware:
HID,Make,Model,AsseTag,Category,Serial#,AcquiredDate,PurchaseOrder#,EmpID,StatusID.
Status: StatusID, Status

Hardware table is already populated with a series of items. A new piece of
hardware purchase goes to the hardware table.
I am using a form to enter hardware data.
When a new Employee comes, I enter the Employee data through a form.
Then I have to assign a piece of hardware to that employee, which will open
the Hardware form, and pick a hardware item for that employee.
The Employee form will have a hardware subform that will show the specific
hardware data assigned to that employee, but this subform is for viewing only.
The hardware Assigned with the specific Serial# will belong to only that
employee.
I am not sure how I can achieve that. I am using Access 2003, I am a novice
programmer. I greately appreciate any help.

 

[Jeff Boyce]

You are describing forms. Let's step back to the data for a moment...

It sounds like you are describing a one-to-many relationship. Is the
following statement true?:
One Employee can have Many pieces of Hardware assigned

If so, one way to handle this is by adding another table to hold the
AssignedHardware info. It could include:

EmployeeID
HardwareID
DateAssigned

Now you can use a main form/subform construction. Put the employee info in
the main form. Create a second form that holds records from the
AssignedHardware table, then "embed" it as a subform in the main form. When
you do this, Access will walk you through setting up the common fields
(i.e., EmployeeID).

This way, whenever you pull up an employee's record in the main form, all of
that employee's AssignedHardware will show in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanmks, but how do I embed?
--
thadi

You are describing forms. Let's step back to the data for a moment...

It sounds like you are describing a one-to-many relationship. Is the
following statement true?:
One Employee can have Many pieces of Hardware assigned

If so, one way to handle this is by adding another table to hold the
AssignedHardware info. It could include:

EmployeeID
HardwareID
DateAssigned

Now you can use a main form/subform construction. Put the employee info in
the main form. Create a second form that holds records from the
AssignedHardware table, then "embed" it as a subform in the main form. When
you do this, Access will walk you through setting up the common fields
(i.e., EmployeeID).

This way, whenever you pull up an employee's record in the main form, all of
that employee's AssignedHardware will show in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Check Access HELP for "subform".

Open the main form in design view.

The Toolbox has a button for subform ... drag it over onto the main form.

Access prompts you for which form to use and which fields to join on.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks. I worked it out fine.
--
thadi

Check Access HELP for "subform".

Open the main form in design view.

The Toolbox has a button for subform ... drag it over onto the main form.

Access prompts you for which form to use and which fields to join on.

Regards

Jeff Boyce
Microsoft Office/Access MVP