Prevent Duplicates

[Bruce]

I asked this question earlier and received a suggestion.
I had some questions, but the thread died out. Here is
the situation again.
I have a table (tblDocument) that includes the fields
[DocumentNumber] and [Revision]. A form (frmDocument)
contains text boxes txtDocumentNumber and txtRevision.
The record sources for these controls are [DocumentNumber]
and [Revision]. Both fields are text fields. I would
like the format to be such that if I type "0422" it shows
up as "Form 04-22". More on that in a moment. It is OK
to have the DocumentNumber appear in more than one record,
provided the Revision is different each time. If the
revision is the same, I would like to see an error message
right away (when exiting txtRevision) instead of seeing an
enigmatic and useless (to the average user) error message
when trying to exit the record. Somebody suggested the
following as the After Update event for txtRevision
(txtDocumentNumber is filled in first) to generate an
error message is DocumentNumber and Revision together
match an existing record (underscores mean no line breaks):
If DCount("*", "tblDocument", "txtDocumentNumber = ' " &_
Me.DocumentNumber & " ' AND txtRevision = ' " &_
Me.Revision & " ' ") > 0 Then
MsgBox "Duplicate"
Cancel = True

However, it does not generate the error message under any
circumstances.
Back to the format. I can set the format in both
txtDocumentNumber in the form and [DocumentNumber] in the
table to "Form "@@-@@, which has the desired result of
having both the table and form show the number as
described above. I wondered if the formatting was
interfering with the ability of the code to find a
duplicate conversation, but I have tried this with the
formatting just in txtDocumentNumber on the form,
[DocumentNumber] just in the table, formatting in both,
and formatting in neither. In no case can I generate an
error message when I deliberately enter a duplicate
Document/Number and Revision combination. Again, both
fields are text. Any ideas?

 

[Lynn Trapp]

when trying to exit the record. Somebody suggested the

following as the After Update event for txtRevision
(txtDocumentNumber is filled in first) to generate an
error message is DocumentNumber and Revision together
match an existing record (underscores mean no line breaks):
If DCount("*", "tblDocument", "txtDocumentNumber = ' " &_
Me.DocumentNumber & " ' AND txtRevision = ' " &_
Me.Revision & " ' ") > 0 Then
MsgBox "Duplicate"
Cancel = True

Try putting your code in the BeforeUpdate event. The AfterUpdate event does
not have a Cancel paremeter.

 

[Bruce]

Thanks, but it makes no difference. I am free to enter
duplicate DocumentNumber/Revision combinations without
receiving any kind of error message. In the field
[DocumentNumber] I am using the format "Form "@@-@@ to
produce "Form 04-04" when "0404" is entered. Will this
cause any problems with finding duplicates? Again, both
fields are text, in case that matters with the code syntax.
I think I could probably do this with indexing, but I
would prefer a helpful error message. The ones that come
with the program are not.