Prevent duplications in two tables

[TimTDP]

I need to have the field "UserName" in two tables.
I cannot have the same UserName in both tables.
I have indexed the field to "No Duplicates" in both tables to prevent duplicates at that level, but how can I check to see if there is a duplicate in the second table?

Many thanks

 

[plog]

When are you checking? Prior to insertion of a new record or just once right now with data in both tables?

To see if duplicates exist right now, you would create a query, link each table by UserName fields and any value that gets returned is in both tables.

To see if a duplicate exists prior to insertion from a form is going to require a Dlookup (http://www.techonthenet.com/access/functions/domain/dlookup.php). You could either make the form unbound and then have the user click a button to add the record at which time you would validate the UserName, or you could do it after they enter the UserName data and yell at them if it is a dupe.

 

[Galaxiom]

I strongly suspect the two tables should be one. Why are they separated?

 

[TimTDP]

I strongly suspect the two tables should be one. Why are they separated?

One table is called tblReps and the other tblCustomerContacts
I don't see how I can create a single table

 

[Galaxiom]

Add a ContactType field to the table.

Use one value to represent Reps and another to represent Customers. The field could be Boolean (eg True for Rep, False for Customer). Or you could use a Text or Number.

Instead of the tables as your forms and report RecordSources you would use queries with Where conditions:

With Boolean:
"WHERE ContactType = True" would return Reps if Reps was represented by true for example. Similarly with the numbers alternative.

Now all your contacts are in the same table and the issue with the validation of unique values becomes trivial.

BTW If you used 1 for Rep and 2 for Customer you could have 3 for both.

If you really want to get advanced, ContactTypes could be represented in a binary structure with types 1, 2, 4, 8, 16 etc. Unique values for any combination are produced by adding the types. The individual values can be returned using BitWise expressions.

 

[plog]

If you really want to get advanced, ContactTypes could be represented in a binary structure with types 1, 2, 4, 8, 16 etc.

Actually, at that point its time to set up a new table.