No duplicate fields allowed

[Bruce]

This should be simple, but a search of Help and of
newsgroups has shown me that it is not. I want to prevent
a duplicate entry in the DocumentNumber field (which is
not the PK). I know that I can set the table to Index (no
duplicates), but the error message shows up only when
moving from the record, and is not useful in any case, as
it does not tell the user just what needs to change. I
would like an error message to show up after leaving the
DocumentNumber field if the number entered there is
already being used. I have seen suggestions for the
Before Update event that involve If statements combined
with Dcount; Dlookup in various combinations; and other
code that runs on for fifteen lines or more, but so far
everything I have tried has produced an error message.
By the way, I do not regard compound field PK as one of
the options (it was among the suggestions).

 

[Rick Brandt]

This should be simple, but a search of Help and of
newsgroups has shown me that it is not. I want to prevent
a duplicate entry in the DocumentNumber field (which is
not the PK). I know that I can set the table to Index (no
duplicates), but the error message shows up only when
moving from the record, and is not useful in any case, as
it does not tell the user just what needs to change. I
would like an error message to show up after leaving the
DocumentNumber field if the number entered there is
already being used. I have seen suggestions for the
Before Update event that involve If statements combined
with Dcount; Dlookup in various combinations; and other
code that runs on for fifteen lines or more, but so far
everything I have tried has produced an error message.
By the way, I do not regard compound field PK as one of
the options (it was among the suggestions).

In BeforeUpdate event of the DocumentNumber control on the form...

If DCount("*", "YourTableName", "DocumentNumber = " & Me.DocumentNumber &
"") > 0 Then
MsgBox "Duplicate. Try Again"
Cancel = True
End If

 

[AlCamp]

Bruce,
I'd do a Refresh on the AfterUpdate event for DocumentNumber, to force
the "no dupes" rule to be tested/applied right away.
hth
Al Camp

 

[Bruce]

Interesting idea. I will file it away for future
reference. In this case, though, it leaves me with the
mysterious Microsoft error message, which will be of
little help to most users. Thanks for taking the time to
reply.

 

[Bruce]

Thanks for taking the time to answer, and for a response
that did the trick. How could I apply that same idea to
two fields in combination? For instance, suppose in the
above example I added a Revision field, so that Document
#123, Rev. C could not be repeated, but Rev. D would be
allowed if it did not already exist? The actual details
are somewhat different, but the example is close enough.

 

[Rick Brandt]

Thanks for taking the time to answer, and for a response
that did the trick. How could I apply that same idea to
two fields in combination? For instance, suppose in the
above example I added a Revision field, so that Document
#123, Rev. C could not be repeated, but Rev. D would be
allowed if it did not already exist? The actual details
are somewhat different, but the example is close enough.

Actual expression would all be on one line.

If DCount("*", "YourTableName",
"DocumentNumber = " & Me.DocumentNumber &
" AND Revision = ' " & Me.Revision & " ' ") > 0 Then...

Since Revision ois text it need single quotes around it. I surronded those
in tha bove with spaces to make them easier to see, but you would eliminate
those in actual expression. If it were written with literals instead of
form references it would look something like...

If DCount("*", "YourTableName",
"DocumentNumber = 123456 AND Revision = 'C'") > 0 Then...

 

[Bruce]

Thanks again. I still haven't sorted out all of those
single and double quotes, so I really appreciate the
syntax help.