B
Bruce
I need to avoid duplicate entries in combined text
fields. DocNum and Revision in combination must be
unique. I can have Doc 123 Rev. A, and Doc 123 Rev. B,
but not two instances of either combination. [DocNum] and
[Revision] are the table fields (in tblMain), and
txtDocNum and txtRevision are the corresponding text boxes
on the form.
An additional consideration is that the user must be
advised of a duplicate entry as soon as the revision
letter is entered. Somebody provided code for the After
Update event of txtRevision, but it doesn't work. I
thought the problem might have been that the code was not
for text fields, so I tried to modify it by placement of
single and double quotes appropriate for text fields, but
maybe I don't understand how that works (in fact, I am
sure of it). I followed up with another question, but the
thread died. Here is the code in its current state:
If DCount("*", "tblMain", "txtDocNum = ' " & Me.DocNum _
& " ' AND txtRevision = ' " & Me.Revision _
& " ' ") > 0 Then
MsgBox "Document number is already in use"
Cancel = True
End If
Again, both fields are text fields. I have received
suggestions to use a combined field primary key and to use
indexing, but I do not care for those options. I prefer
that the primary key not be based on data entry, but
rather be an automatic and unseen part of the record.
Indexing generates an enigmatic error message only after
trying to exit the record (although I suppose I could add
code to save the record to the Exit event for txtRevision,
and maybe generate the enigmatic error message a bit
sooner). My preference is for a clear and direct error
message as soon as the error is made.
One further thing: I would like to have the data entry for
txtDocNum be such that entering "0404" shows "Form 04-
04". So far I have done this by using the following
format for both txtDocNum and the table field
[DocNum]: "Form "@@-@@. I would like to know if there is
a better way to do that, and if the format will interfere
with identifying duplicates.
fields. DocNum and Revision in combination must be
unique. I can have Doc 123 Rev. A, and Doc 123 Rev. B,
but not two instances of either combination. [DocNum] and
[Revision] are the table fields (in tblMain), and
txtDocNum and txtRevision are the corresponding text boxes
on the form.
An additional consideration is that the user must be
advised of a duplicate entry as soon as the revision
letter is entered. Somebody provided code for the After
Update event of txtRevision, but it doesn't work. I
thought the problem might have been that the code was not
for text fields, so I tried to modify it by placement of
single and double quotes appropriate for text fields, but
maybe I don't understand how that works (in fact, I am
sure of it). I followed up with another question, but the
thread died. Here is the code in its current state:
If DCount("*", "tblMain", "txtDocNum = ' " & Me.DocNum _
& " ' AND txtRevision = ' " & Me.Revision _
& " ' ") > 0 Then
MsgBox "Document number is already in use"
Cancel = True
End If
Again, both fields are text fields. I have received
suggestions to use a combined field primary key and to use
indexing, but I do not care for those options. I prefer
that the primary key not be based on data entry, but
rather be an automatic and unseen part of the record.
Indexing generates an enigmatic error message only after
trying to exit the record (although I suppose I could add
code to save the record to the Exit event for txtRevision,
and maybe generate the enigmatic error message a bit
sooner). My preference is for a clear and direct error
message as soon as the error is made.
One further thing: I would like to have the data entry for
txtDocNum be such that entering "0404" shows "Form 04-
04". So far I have done this by using the following
format for both txtDocNum and the table field
[DocNum]: "Form "@@-@@. I would like to know if there is
a better way to do that, and if the format will interfere
with identifying duplicates.