I'm building a database with 2 main tables; one which holds personal details about users of company computer system and the other which holds details of how users are set-up (i.e profiled on those computer systems).

The personal details table holds details such as employee number, name, job title, department, etc.

The profiling table holds details of all of the different sytems that each employee has access to.

As employee's move between departments their system profile may change, e.g. while working in accounts they may be set up with full update access to financial systems but if they move to personnel they may need to have their access changed to read-only.

So an employee can have many 'profiles' down the years.

So in database terms the relationship between my personal details table and my 'profiles' table would be one to many.

The actual physical process of having your profile changed is filling in a form which is passed to a profiling team, who then enter the details onto a database and change the profiling.

Because of the auditable nature of IT security, I need to be able to go back and get an accurate picture of a persons circumstances (e.g. which dept they were in, what their job title was) when they had a particular profile. So to do this I need to hold a certain amount of personal details on the profiling table.

To make the database user friendly, I set up my database with the two tables, and included on the profile table personal information such as name, job title, department, etc. To make the whole system user-proof I endeavoured to create a form for the profile table which when I input the user employee number it pulled the rest of the personal details information in from the personal details table (using the relationship between employee number on both tables). That way database users could be sure that they were dealing with the correct user and would prompt certain routine questions (e.g. why do you need that profile if you're working in Accounts?).

However, in practice I was unable to do this. I did try to do this by using DLookUp statements withing the control source of the form fields that I was trying to pull information into. However, I get the feeling that I am doing something fundamentally wrong because when I open the form for input all of the DLookUp fields have the message "error" in them.

In theory the information that I am trying to pull in may not be up to date because the employee may have changed departments. Therefore, I needed to pull in the information held on the personal details table but if necessary have the facility to change those details on the profile form. Therefore, I was unable to use a form/subform relationship as if I changed the personal details on a form/subform I would lose historical details (e.g. like they were profiled for update access on financial systems because at the time they worked in accounts - if the personal details now state that they work in personnel this would have no relevance for the historical profile).

So in short, having failed using DLookUp on forms I am at a loss as to how I can automatically default-populate fields on a form using record data from a table with a relationship to the table on which the form is based.

I have previously asked questions about DLookUp on this forum but I don't think I correctly conveyed the reasons why I was working in the manner that I was and so my needs were lost on a lot of people who just thought that I was working incorrectly.

At present, I am beginning to lose heart that Access can actually provide a solution to my way of working and am thinking of moving to a more versatile (if more cumbersome) DB package.

However, one last plea to all of you knowledgeable types out there - HEEEEEEELLLLLLLLLLLPPP !

Lee

"Therefore, I was unable to use a form/subform relationship as if I changed the personal details on a form/subform I would lose historical details "

I might be wrong but prior to looking further at your Dlookup question, I have the impression that you have a database design problem. If your DB is not too big, would you mind to mail it to me?

You definitely need more than two tables to do what you want to do. There is a one-to-many relationship between the person and their job titles, and then a one-to-many relationship between their job and the systems to which they have access.

I would also have another table (or set of tables) that define the systems themselves.

And, all these tables need effectivity dates.

If you try to keep the original table setup, I think that you will have problems no matter what DB product you are using. More tables does not mean that the system will not be user friendly.

The problem here being that there are no hard and fast rules between jobs and IT Profiles/Systems and the vast number of permutations that there may be makes using further tables a non-starter.

Take it from me that DataEase and DBase are both versatile enough to do what I want to do but not as user friendly as access.

I would still be interested to hear from anyone who thinks that they can achieve my original objectives.