Prevent duplicates

[Bruce]

I received some code here to prevent duplicate entries in
two fields, but I think the first part of the code assumes
a different data type than I am using.
I have a table (tblDocument), and a form (frmDocument)
based on the table. tblDocument contains fields [DocNum]
and [Revision]. The corresponding text boxes on
frmDocument are txtDocNum and txtRevision. I need a
message box to pop up if both fields match another
record. If DocNum 123, Revision A exists, a new record
can contain DocNum123 only if Revision is something other
than A. Both [DocNum] and [Revision] are text fields.
The code I have (which is wrong, but I wonder if I am even
close) is (underscore means no line break):
If DCount("*", "tblDocument", "txtDocNum = " &_
Me.DocNum & " AND txtRevision = ' " & Me.Revision_
& " ' ") > 0 Then
MsgBox ...
and the rest of the code is OK. I am pretty sure I need
some single quotes in the first part, since it is text,
but I can't find the combination that works. The trouble
is that I can't tell to which part of the expression the
quotes belong. The other trouble is that in VBA field
names, etc. are sometimes in brackets, sometimes in
quotes, and probably some other options as well. If there
is a pattern, I have not yet discovered it.

 

[JohnFol]

Sounds like you are close. If both are text fields you need (spaced out for
legibility) you need to add in the single quotes around the Me.DocNum

If DCount("*", "tblDocument", "txtDocNum = ' " & Me.DocNum & " ' AND
txtRevision = ' " & Me.Revision_
& " ' ") > 0 Then . . .. . .



Field names can be done in a variety of ways. Some is personal preference
and some is how you use it. For example

MyData![FieldName]
MyData.Fields("FieldName")
MyData("FieldName")

 

[Bruce]

I put the code into the Before Update event of
txtRevision, but could not get it to generate an error
message. The next line of code (after "Then") is
MsgBox "Duplicate"
Cancel = True
End If

It occured to me that it could matter that txtDocNum has
an input mask, so that typing "0405" shows up as "Form 04-
05". I tried adding the same input mask to the table,
then I tried having it only in the table, but neither of
those options permitted me to do anything in the text
box. The input mask is a great help in avoiding mistakes
on repetitive data entry, but I will abandon it if it will
not work with the code. Avoiding duplicates is the most
important thing.

-----Original Message-----
Sounds like you are close. If both are text fields you need (spaced out for
legibility) you need to add in the single quotes around the Me.DocNum

If DCount("*", "tblDocument", "txtDocNum = ' " & Me.DocNum & " ' AND
txtRevision = ' " & Me.Revision_
& " ' ") > 0 Then . . .. . .



Field names can be done in a variety of ways. Some is personal preference
and some is how you use it. For example

MyData![FieldName]
MyData.Fields("FieldName")
MyData("FieldName")

I received some code here to prevent duplicate entries in
two fields, but I think the first part of the code assumes
a different data type than I am using.
I have a table (tblDocument), and a form (frmDocument)
based on the table. tblDocument contains fields [DocNum]
and [Revision]. The corresponding text boxes on
frmDocument are txtDocNum and txtRevision. I need a
message box to pop up if both fields match another
record. If DocNum 123, Revision A exists, a new record
can contain DocNum123 only if Revision is something other
than A. Both [DocNum] and [Revision] are text fields.
The code I have (which is wrong, but I wonder if I am even
close) is (underscore means no line break):
If DCount("*", "tblDocument", "txtDocNum = " &_
Me.DocNum & " AND txtRevision = ' " & Me.Revision_
& " ' ") > 0 Then
MsgBox ...
and the rest of the code is OK. I am pretty sure I need
some single quotes in the first part, since it is text,
but I can't find the combination that works. The trouble
is that I can't tell to which part of the expression the
quotes belong. The other trouble is that in VBA field
names, etc. are sometimes in brackets, sometimes in
quotes, and probably some other options as well. If there
is a pattern, I have not yet discovered it.

.