Users and Asset Data linked on Form

[Dave]

Hello everyone,
I have two tables, one a Staff list and the other an Asset list
extracted from seperate databases, the information in the tables has
absolutely no common data, I have a form with the user name and other
bits and pieces on from the staff list and also a drop down list of
all asset numbers which I can then select the appropiate asset number
from then, that asset number and other details I would like to be
associated to that user.

I have tried, I have done this in the past about 4 years ago but I
cannot remember any of it!

Any help would most appreciated

Cheers
David

 

[Steve Schapel]

David,

The first thing to clarify is whether there will only ever be one Asset
associated with any given Staff, or whether a Staff can have more than
one Asset. Or, less likely I suppose, whether an Asset can be
associated with more than one Staff.

 

[Dave]

Thanks Steve,
A user certainly could have several assets for instance a PC, printer,
scanner, etc. It is also possible for a PC to be shared. But for the
moment just to show how you would do it and hopefully I will be able
to adapt it from there lets assume one asset is allocated to one
person, how would that be achieved/

Cheers
David

 

[Steve Schapel]

David,

Well, that's the point, you see. How it would be achieved in one case
is quite different from how it would be achieved in another, so staring
with one and then adapting to the other doesn't really come into it. If
we assume one asset is allocated to one person, you would need to put an
AssetNumber field in the Staff table, where each Staff has their
associated asset entered, which would be done via a combobox on the
Staff form. Whereas if we are talking one-to-many relationships, you
would need a separate table for the asset allocation data, which
includes both the StaffID and AssetNumber fields, so the data can be
related to both the Staff and Asset tables. And the data would be
entered via a subform, which is based on the Allocation table, and
placed onto the Staff form.

So, tryu it like this...

1. Make an Allocation table, with these fields:
AssetNumber (same data type as corresponding field in Assets table)
StaffID (same data type as id field in Staff table)

2. If you want to keep historical tracking of asset allocation, this
would also be the place to add other relevant fields e.g. AllocationDate.

3. Make a Continuous View from based on this Allocations table.

4. Make the control for StaffID hidden (set Visible property to No)

5. Make the control for AssetNumber as a combobox, whose Row Source is
the Assets table. Adjust the properties of this combobox so you can see
the columns you want to see in the drop-down list.

6. Place this form as a subform on the Staff form. Set the Link Child
Fields and Link Master Fields properties as StaffID.

7. Open the Staff form at the record for one of the Staff members, and
try entering in the subform the Asset(s) associated with that person

 

[Dave]

Thanks Steve,
That's extremely helpful

Cheers
David