Using One table to look up info to store in another.

[Joe_A]

Good Morning.
I am starting to play with access 2007 for my company. I could use some help with a few areas. (very upset they took out user perms) The perpose of the DB is to track assets and keep track of its history. IE ... on this date , this user, replaced a part on this asset.

I have four tables
USERS
ASSETS
PART LOOKUP
CONTACTS

I built a form to populate assets. On the form I have a drop down box for a part number from the Parts look up table. I want the form to look up additional info based on the part number the user selects and populate the form with additional information such as title of part and so on. Then when I hit the add record button on the form I would like those fiels that it pulled from the PARTS LOOKUP table to save in different fields on the assets table.

I hope this defines what I am looking for


Thank you

[rpeare]

Is your PartsLookup table (don't use spaces or special characters in your field names and try to use compound words or word portions to stay away from reserved words like NAME, TIME, DATE, etc.) the table that is tracking the user and the part number they used along with the date/time etc?

If so you only need to store the userID and the partID, you don't need to store any of the lookup information you can readily recover it for the purposes of reporting, queries, etc without storing it in two different locations.

If your form is a bound form any time you look up an existing record you can build it such that it will populate the user and part information without having to store it on your history table (again assuming that's your PartsLookup table) but using the autolookup design.

On your USERS and ASSETS table make sure the field you're using as your unique identifier is also listed as the primary key. If you then store that value in your PartsLookup table you can create a query linking FROM your PartsLookup to the primary key of your two support tables (arrow should point from partslookup to assets and partslookup to users. now as as simple test enter an existing partID in the corresponding field in your partslookup table and the item should populate the item fields for you.

Before you use this on a form, however, make sure you LOCK all the fields that are not directly related to partslookup because if you don't they can be modified by the user which you do not want.