"Can't add records" error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry if this ends up as a double post, but it seems the first attempt didn't
go through.

I have a database in which I must have 2 unique fields, Code and User.
If I have both fields in one table and set each one as a Primary Key, the
combination of the two fields will be unique, but not necessarily each
individual field. For example, I could have John123, Mary123, Mary456, which
are all unique combinations. However, I need the individual field John to be
unique, Mary to be unique, 123 to be unique, 456, etc.

The way I tried to get around this is to have two tables:

TblUsers TblCodes
UserID (Primary) Code (Primary)
Fname, Lname, etc. UserID

Then I joined the two tables into UserFormQuery in which I have all the
fields from the Users table plus the Code field from the Codes table, joined
with UserID. I then used this query as the data source for my UserForm.

When I try to add data to the Codes field, I get the message "Can't add
record; join key of table "Codes" not in recordset." I can add data to all
the other fields. But if I go back to a previous record, then forward to
the one I'm working on, I am able to add the data into the Code field. Any
idea what's wrong? It's probably staring me right in the face!
 
If you want to have CODE unique and USER unique in one table you will need
to create two indexes. For each index set the Primary to No and Unique to
Yes.
 
Thanks, Allan...that did it!

Allan Murphy said:
If you want to have CODE unique and USER unique in one table you will need
to create two indexes. For each index set the Primary to No and Unique to
Yes.
Click to expand...
 
Back
Top