Avoid duplicate combinations

[Bruce]

I have a table (tblMain) with text fields for
[DocumentNumber] and [Revision]. On the form (frmMain)
these fields are the Record Source for text boxes
txtDocNum and txtRev. I would like to enter "0404" in
txtDocNum and have it appear as "Form 04-04". Is this
accomplished by formatting the table field, the text box,
both, or neither?
Next question: It is OK to have the same Document Number
twice, but not with the same revision. There can be only
one record for Form 04-04 Rev. A, one for 04-04 Rev. B,
etc. How can I trap a duplicate as soon as the Revision
is entered into txtRevision (or when leaving the control?
Again, these are both text fields in the underlying table.
I have posted this question before, but although I
received suggestions they did not work. Follow-up
questions remained unanswered, so I am trying again.

 

[Rick B]

Bruce: I just answered your second question this morning in a post someone
else had placed. Read the following and modify to meet your needs...

--------------------------------------------------------------------------
You can set a key field in the table of USERID&DATE and not allow
duplicates.

To do so, go into design view for the table. Click the IserID and the Date
fields (you can hold CTRL while clicking). Then click your "key" icon at
the top of the screen.

If you point your cursor to the table's title bar and right click, you will
get a menu that includes an "indexes" icon. Clicking that will allow you to
modify some of the key properties.

rick B




I have a table (tblMain) with text fields for
[DocumentNumber] and [Revision]. On the form (frmMain)
these fields are the Record Source for text boxes
txtDocNum and txtRev. I would like to enter "0404" in
txtDocNum and have it appear as "Form 04-04". Is this
accomplished by formatting the table field, the text box,
both, or neither?
Next question: It is OK to have the same Document Number
twice, but not with the same revision. There can be only
one record for Form 04-04 Rev. A, one for 04-04 Rev. B,
etc. How can I trap a duplicate as soon as the Revision
is entered into txtRevision (or when leaving the control?
Again, these are both text fields in the underlying table.
I have posted this question before, but although I
received suggestions they did not work. Follow-up
questions remained unanswered, so I am trying again.

 

[Bruce]

Thanks for the reply. If I understand correctly the
process you outlined, I will receive a messge when I
attempt to move to a new record. The problem is that the
entire form has been completed before there is an
indication that something is wrong. Further, the message
advising of the error provides no details, as I recall.
I would rather avoid if at all possible the use of a
combined field primary key. I am worried about what would
happen to relationships if there is a data entry mistake
that needs to be corrected later. Thanks again for taking
a look at the question.