One item - one user

[Kevin Field]

Hi guys,

I am currently looking to build a IT iventory for the company i work for. I have built a few other DB which are now working fine. However, i have come across an issue with this one which i need fixing.

I will have a main form which will list the User Details. Each of the users will have a monitor, base unit and telephone assigned to them.

However, in my previous attempt, on selection of the item i.e. monitor, it duplicates the record and assigns it to the person regardless if it is currently assigned elsewhere.

Basically, im looking for a way to only allow one item i.e. monitor, base unit or telephone to be assigned to one user at at time.

Any ideas how to enforce this?

Thanks!

 

[Trevor G]

You would need to look at the design of your tables and relationships.

The relationship in this case I believe should be One to One.

 

[The_Doc_Man]

If the Monitor, Base Unit, Phone Unit inventory items include serial numbers that must be kept distinct, I MIGHT (repeat MIGHT) do it this way:

Parent form: The user info, bound to the user record, one user at a time. Have three number slots for Monitor # (as a foreign key), base unit (FK), phone unit (FK).

On your assignment form, have a combo box to select a monitor, base unit, and phone unit. Each combo box can show the item serial number and description. You select one of each. Store the prime key of the Monitor, Base, and Phone in the user table. Since there is only one slot for each, that forces uniqueness.

In the table of times, you have a number that will the FK to the user table showing to whom the item is assigned.

If this is a combined table (all inventory items in same table, merely qualified by item type), write queries to select only unassigned items. Use a different query for each type of item to assign. Drive each of the combo boxes from the appropriate query.

The last step of this is a commit command button that runs a little bit of VBA that will store the user PK as an FK in each allocated inventory item. (You have have those number in the user record.)

Once the inventory items are marked with their owner and the owner record shows what items are allocated.

You might have to write some VBA behind one or more push-buttons to allow you to disassociate a user and one of these inventory items.

Now, the things you WON'T do. Don't set the combo-box to multi-select. Don't set the underlying database fields for the user's allocated items as multi-valued.

This is NOT the most elegant and rigorous solution, but it is simple conceptually and will avoid the multi-assign case. It is flat-out WRONG if you start assigning more than just a monitor, base unit, and phone. As long as all you ever assign are those three things, this will work.

The MOMENT that your inventory of assignable things gets greater variety, you will have to fully normalize this database and start working with normalized forms. THIS IS A STOP-GAP SOLUTION!

 

[Kevin Field]

Thank you for all of the above Doc. Youre a lifesaver.

 

[Kevin Field]

Doc,

I have followed exactly what you said and it all makes perfect sense.

I have users list being updated with the selected items id through combo boxes as you suggested. This works perfectly.

However, i am a little lost as to how to assign the UserID (primary key in the user table and will be a foreign key in the item table) when you select the item from the drop down lists.

Obviously this is important as otherwise, the users techinically has nothing assisgned.

Is this the VBA code you are talking about? to set the UserID in the items table?

If so, could you possibly give me a heads up on what this code might look like?

Thanks!