Make lookup exclusive - allow only one reference to the lookup

[rscott7706]

I am sure this has been asked and answered, but I must not be using the right search terms as I cannot come up with a post that addresses my need.



I have a users table and a computers table. I want the computers table to do a lookup to the users table. But, once a lookup has been established, I don't want Access to allow another. A pop up note stating the record has already been used (or something like that) would be icing on the cake.

So, let's say computer ID #1 is established as a lookup to user #4 (for instance), Access would not allow computer ID #1 to be used as a lookup to any other user.

Possible? (Thanks in advance)....

Ron

[June7]

And why?

If you need to associate Computer1 with User4, then need a record in a table that defines that association.

A presume a computer can be assigned to only one user at a time. Have a field in computers table for UserID.

[rscott7706]

I think I have already set up most of what you are saying, but still a little lost.

In my computers table is a field named "User_ID" - the table also has a field "model/brand".

I have made "User_ID" a lookup to my employees table field "ID". So in my computers table (for instance) I select the model/brand "dell XXX" (for instance) then select the lookup ("User_ID") to employees record "ID" number 4.

I want that relationship to be final. I don't want model/brand "dell XXX" to be hooked to any other user.

I hope I explained this better.

Ron

[June7]

Advise not to build lookups in table http://access.mvps.org/access/lookupfields.htm

Use combobox on form.

I don't understand the issue. Are you saying a computer will NEVER be reassigned to another employee?

Could have code that locks the combobox against editing if the UserID field has a value.

[rscott7706]

No, I don't want to lock the record, it could be assigned to someone else in the future. I just don't want the ability to assign the computer to more than one user at a time.

Let me look at your resource - it seems that lookup is not the way to go.

Thanks for your help!!

Ron

[June7]

I said lock the combobox, not the record, but you want to allow editing the user so that is not solution.

Can't assign a computer to more than one user at a time. The assigned user is indicated by the value in UserID field of Computers table.