Preventing Duplicate Records

[Thorson]

I know how to prevent duplicate records for a specified field in a table, but
is there a way to prevent records for a combination of date and ID? So a
record with the same ID and different date can be entered but not two records
with the same Date and ID?

Thanks,

 

[fredg]

I know how to prevent duplicate records for a specified field in a table, but
is there a way to prevent records for a combination of date and ID? So a
record with the same ID and different date can be entered but not two records
with the same Date and ID?

Thanks,

Code the Form's BeforeUpdate event:

If DCount("*", "TableName", "[ID] = " & Me.[ID] & " and [DateField] =
#" & Me.[DateField] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If

Use your actual table and field names.
The above assumes [ID] is a Number datatype and [DateField] is a Date
datatype.

Look up
Where Clause
and
Restrict data to a subset of records
in VBA help files.

 

[Douglas J. Steele]

Fred's shown you how to do it in code. Be aware that a table's PrimaryKey is
simply a special index, and indexes can contain up to ten separate fields.
That means that you can define the table's PrimaryKey to be the combination
date and ID.

 

[Thorson]

I entered in the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = " &
Me.[ProtocolNumber] & " and [EarTag] = #" & Me.[EarTag] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If
End Sub

However when I tested the form the following error came up: "Compile Error:
End If without Block If"

--
Thorson

I know how to prevent duplicate records for a specified field in a table, but
is there a way to prevent records for a combination of date and ID? So a
record with the same ID and different date can be entered but not two records
with the same Date and ID?

Thanks,

Code the Form's BeforeUpdate event:

If DCount("*", "TableName", "[ID] = " & Me.[ID] & " and [DateField] =
#" & Me.[DateField] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If

Use your actual table and field names.
The above assumes [ID] is a Number datatype and [DateField] is a Date
datatype.

Look up
Where Clause
and
Restrict data to a subset of records
in VBA help files.

 

[Douglas J. Steele]

If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = " &
Me.[ProtocolNumber] & " and [EarTag] = #" & Me.[EarTag] & "#") > 0 Then

should be a single line of text. Is it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I entered in the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = " &
Me.[ProtocolNumber] & " and [EarTag] = #" & Me.[EarTag] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If
End Sub

However when I tested the form the following error came up: "Compile
Error:
End If without Block If"

--
Thorson

I know how to prevent duplicate records for a specified field in a
table, but
is there a way to prevent records for a combination of date and ID? So
a
record with the same ID and different date can be entered but not two
records
with the same Date and ID?

Thanks,

Code the Form's BeforeUpdate event:

If DCount("*", "TableName", "[ID] = " & Me.[ID] & " and [DateField] =
#" & Me.[DateField] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If

Use your actual table and field names.
The above assumes [ID] is a Number datatype and [DateField] is a Date
datatype.

Look up
Where Clause
and
Restrict data to a subset of records
in VBA help files.

 

[Thorson]

Yes it is a single line, I tried it again this morning and it gave a Run-Time
error '3075'

Neither of the fields I am comparing is actually a date, does that matter?
I think in my original question I asked for one field to be a date, that is
correct for a different form, but I also want to prevent duplicate records in
this form between the protocol number (which has letters and numbers) and the
EarTag (which also has letters and numbers)

--
Thorson

If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = " &
Me.[ProtocolNumber] & " and [EarTag] = #" & Me.[EarTag] & "#") > 0 Then

should be a single line of text. Is it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I entered in the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = " &
Me.[ProtocolNumber] & " and [EarTag] = #" & Me.[EarTag] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If
End Sub

However when I tested the form the following error came up: "Compile
Error:
End If without Block If"

--
Thorson

On Fri, 5 Jun 2009 09:40:01 -0700, Thorson wrote:

I know how to prevent duplicate records for a specified field in a
table, but
is there a way to prevent records for a combination of date and ID? So
a
record with the same ID and different date can be entered but not two
records
with the same Date and ID?

Thanks,

Code the Form's BeforeUpdate event:

If DCount("*", "TableName", "[ID] = " & Me.[ID] & " and [DateField] =
#" & Me.[DateField] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If

Use your actual table and field names.
The above assumes [ID] is a Number datatype and [DateField] is a Date
datatype.

Look up
Where Clause
and
Restrict data to a subset of records
in VBA help files.

 

[Douglas J. Steele]

Definitely it will matter.

If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = '" &
Me.[ProtocolNumber] & "' and [EarTag] = '" & Me.[EarTag] & "'") > 0 Then

Exagerated for clarity, that's

If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = ' " &
Me.[ProtocolNumber] & " ' and [EarTag] = ' " & Me.[EarTag] & " ' ") > 0 Then



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes it is a single line, I tried it again this morning and it gave a
Run-Time
error '3075'

Neither of the fields I am comparing is actually a date, does that matter?
I think in my original question I asked for one field to be a date, that
is
correct for a different form, but I also want to prevent duplicate records
in
this form between the protocol number (which has letters and numbers) and
the
EarTag (which also has letters and numbers)

--
Thorson

If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = " &
Me.[ProtocolNumber] & " and [EarTag] = #" & Me.[EarTag] & "#") > 0 Then

should be a single line of text. Is it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I entered in the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblProtocolIndID", "[ProtocolNumber] = " &
Me.[ProtocolNumber] & " and [EarTag] = #" & Me.[EarTag] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If
End Sub

However when I tested the form the following error came up: "Compile
Error:
End If without Block If"

--
Thorson


:

On Fri, 5 Jun 2009 09:40:01 -0700, Thorson wrote:

I know how to prevent duplicate records for a specified field in a
table, but
is there a way to prevent records for a combination of date and ID?
So
a
record with the same ID and different date can be entered but not
two
records
with the same Date and ID?

Thanks,

Code the Form's BeforeUpdate event:

If DCount("*", "TableName", "[ID] = " & Me.[ID] & " and [DateField] =
#" & Me.[DateField] & "#") > 0 Then
MsgBox "This is a duplicate record."
Cancel = True
End If

Use your actual table and field names.
The above assumes [ID] is a Number datatype and [DateField] is a Date
datatype.

Look up
Where Clause
and
Restrict data to a subset of records
in VBA help files.

 

[Thorson]

Works Perfectly! Thanks!