No duplicate records problem

  • Thread starter Thread starter Russ
  • Start date Start date
R

Russ

I've got a fairly simple db with only 4 tables. Only two are linked
by a field called E58# with a one to many relationship. In the
"many" table I want to allow a field's (Item#) value for each record
to contain a number 1, 2, or 3 and not duplicate the number under the
same E58# when entering data with a form.

However, in table design when I give it an index of 'Yes, no
duplicates' I cannot enter another Item# 1 or 2 or 3 in a different
record with a different E58#. I only want to restrict duplicity when
the E58# is the same. How can I do this?

I've tried making multiple keyed fields using both fields but if in
error a person enters the duplicate field value it is quite difficult
to extract yourself from the error. Even deleting the record in error
retrurns a message about no null values in key fields.
 
BTW, I personally wouldn't use a # sign in a field name, but if it works.......

Try: Set it up as a multi-field (on E58# and Item# fields) index, not a
multi-field key. Set unique to Y. You could also set it to ignore nulls.
 
BTW, I personally wouldn't use a # sign in a field name, but if it works.......

Try: Set it up as a multi-field (on E58# and Item# fields) index, not a
multi-field key. Set unique to Y. You could also set it to ignore nulls.
 
In 2003 it's obscure but there.....hopefully the same in 2K. ?

On the index grid, on the next line down from the first field index, enter a
new line, leave the index spot blank and enter the 2nd field name.
 
In 2003 it's obscure but there.....hopefully the same in 2K. ?

On the index grid, on the next line down from the first field index, enter a
new line, leave the index spot blank and enter the 2nd field name.
 
I've got a fairly simple db with only 4 tables. Only two are linked
by a field called E58# with a one to many relationship. In the
"many" table I want to allow a field's (Item#) value for each record
to contain a number 1, 2, or 3 and not duplicate the number under the
same E58# when entering data with a form.

However, in table design when I give it an index of 'Yes, no
duplicates' I cannot enter another Item# 1 or 2 or 3 in a different
record with a different E58#. I only want to restrict duplicity when
the E58# is the same. How can I do this?

I've tried making multiple keyed fields using both fields but if in
error a person enters the duplicate field value it is quite difficult
to extract yourself from the error. Even deleting the record in error
retrurns a message about no null values in key fields.
Click to expand...

The unique index won't work for the exact reason you describe: that field
ISN'T unique.

What you can do is create a two field unique index on the E58# field and the
Item# field (do note that # is a date delimiter and may best be avoided in
fieldnames). In addition you can have Access automatically assign the Item#
and have your form check for duplicates, avoiding the table error. In the
form's BeforeInsert event you can put code

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item#] = NZ(DMax("[Item#]", "
", "[E58#] = " & Me![E58#])) + 1
End Sub

and then check for duplicates before saving, just in case the user types a
value into the field:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Item#]", "table", _
"[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
 
I've got a fairly simple db with only 4 tables. Only two are linked
by a field called E58# with a one to many relationship. In the
"many" table I want to allow a field's (Item#) value for each record
to contain a number 1, 2, or 3 and not duplicate the number under the
same E58# when entering data with a form.

However, in table design when I give it an index of 'Yes, no
duplicates' I cannot enter another Item# 1 or 2 or 3 in a different
record with a different E58#. I only want to restrict duplicity when
the E58# is the same. How can I do this?

I've tried making multiple keyed fields using both fields but if in
error a person enters the duplicate field value it is quite difficult
to extract yourself from the error. Even deleting the record in error
retrurns a message about no null values in key fields.
Click to expand...

The unique index won't work for the exact reason you describe: that field
ISN'T unique.

What you can do is create a two field unique index on the E58# field and the
Item# field (do note that # is a date delimiter and may best be avoided in
fieldnames). In addition you can have Access automatically assign the Item#
and have your form check for duplicates, avoiding the table error. In the
form's BeforeInsert event you can put code

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item#] = NZ(DMax("[Item#]", "
", "[E58#] = " & Me![E58#])) + 1
End Sub

and then check for duplicates before saving, just in case the user types a
value into the field:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Item#]", "table", _
"[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
 
Well, I was able to create the multi field index, but it would only
warn me of the duplicate values after I tried to move off that record.
Then, I could not delete that record with a delete record command but
had to close the form itself. I've got the index set to ignore nulls
as well.
 
Well, I was able to create the multi field index, but it would only
warn me of the duplicate values after I tried to move off that record.
Then, I could not delete that record with a delete record command but
had to close the form itself. I've got the index set to ignore nulls
as well.
 
John,

I was able to delete the record after entering your code, but the form
will not insert a digit into the ItemNo field (I've renamed and
removed the # symbol and replaced them with "No") when I insert a new
record.

I've got a fairly simple db with only 4 tables. Only two are linked
by a field called E58# with a one to many relationship. In the
"many" table I want to allow a field's (Item#) value for each record
to contain a number 1, 2, or 3 and not duplicate the number under the
same E58# when entering data with a form.

However, in table design when I give it an index of 'Yes, no
duplicates' I cannot enter another Item# 1 or 2 or 3 in a different
record with a different E58#. I only want to restrict duplicity when
the E58# is the same. How can I do this?

I've tried making multiple keyed fields using both fields but if in
error a person enters the duplicate field value it is quite difficult
to extract yourself from the error. Even deleting the record in error
retrurns a message about no null values in key fields.
Click to expand...

The unique index won't work for the exact reason you describe: that field
ISN'T unique.

What you can do is create a two field unique index on the E58# field and the
Item# field (do note that # is a date delimiter and may best be avoided in
fieldnames). In addition you can have Access automatically assign the Item#
and have your form check for duplicates, avoiding the table error. In the
form's BeforeInsert event you can put code

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item#] = NZ(DMax("[Item#]", "
", "[E58#] = " & Me![E58#])) + 1
End Sub

and then check for duplicates before saving, just in case the user types a
value into the field:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Item#]", "table", _
"[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
Click to expand...
 
John,

I was able to delete the record after entering your code, but the form
will not insert a digit into the ItemNo field (I've renamed and
removed the # symbol and replaced them with "No") when I insert a new
record.

I've got a fairly simple db with only 4 tables. Only two are linked
by a field called E58# with a one to many relationship. In the
"many" table I want to allow a field's (Item#) value for each record
to contain a number 1, 2, or 3 and not duplicate the number under the
same E58# when entering data with a form.

However, in table design when I give it an index of 'Yes, no
duplicates' I cannot enter another Item# 1 or 2 or 3 in a different
record with a different E58#. I only want to restrict duplicity when
the E58# is the same. How can I do this?

I've tried making multiple keyed fields using both fields but if in
error a person enters the duplicate field value it is quite difficult
to extract yourself from the error. Even deleting the record in error
retrurns a message about no null values in key fields.
Click to expand...

The unique index won't work for the exact reason you describe: that field
ISN'T unique.

What you can do is create a two field unique index on the E58# field and the
Item# field (do note that # is a date delimiter and may best be avoided in
fieldnames). In addition you can have Access automatically assign the Item#
and have your form check for duplicates, avoiding the table error. In the
form's BeforeInsert event you can put code

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item#] = NZ(DMax("[Item#]", "
", "[E58#] = " & Me![E58#])) + 1
End Sub

and then check for duplicates before saving, just in case the user types a
value into the field:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Item#]", "table", _
"[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
Click to expand...
 
What you can do is create a two field unique index on the E58# field and the
Item# field (do note that # is a date delimiter and may best be avoided in
fieldnames). In addition you can have Access automatically assign the Item#
and have your form check for duplicates, avoiding the table error. In the
form's BeforeInsert event you can put code

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item#] = NZ(DMax("[Item#]", "
", "[E58#] = " & Me![E58#])) + 1
End Sub

and then check for duplicates before saving, just in case the user types a
value into the field:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Item#]", "table", _
"[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
Click to expand...



John,

After screwing up and putting your code in the wrong form, I finally
got it located in the subform where it belongs. However it will not
insert a new Item number in the ItemNo field when I add a record. I
also get an error message when I try to exit the form which says:
Run time error 2465.
Microsoft Access can't fiind the field 'I' referred to inyour
expression. (the 'I' looks like a pipe symbol that I couldn't find on
my keyboard)

Here's what I entered:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![ItemNo] = Nz(DMax("[ItemNo]", "[Items]", "[E58No] = " &
Me![E58No])) + 1
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[ItemNo]", "Items", _
"[E58No] = " & Me.[E58No] & " AND [ItemNo] = " & Me![ItemNo])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
 
What you can do is create a two field unique index on the E58# field and the
Item# field (do note that # is a date delimiter and may best be avoided in
fieldnames). In addition you can have Access automatically assign the Item#
and have your form check for duplicates, avoiding the table error. In the
form's BeforeInsert event you can put code

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Item#] = NZ(DMax("[Item#]", "
", "[E58#] = " & Me![E58#])) + 1
End Sub

and then check for duplicates before saving, just in case the user types a
value into the field:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Item#]", "table", _
"[E58#] = " & Me.[E58#] & " AND [Item#] = " & Me![Item#])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
Click to expand...



John,

After screwing up and putting your code in the wrong form, I finally
got it located in the subform where it belongs. However it will not
insert a new Item number in the ItemNo field when I add a record. I
also get an error message when I try to exit the form which says:
Run time error 2465.
Microsoft Access can't fiind the field 'I' referred to inyour
expression. (the 'I' looks like a pipe symbol that I couldn't find on
my keyboard)

Here's what I entered:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![ItemNo] = Nz(DMax("[ItemNo]", "[Items]", "[E58No] = " &
Me![E58No])) + 1
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[ItemNo]", "Items", _
"[E58No] = " & Me.[E58No] & " AND [ItemNo] = " & Me![ItemNo])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
 
John,

I was able to delete the record after entering your code, but the form
will not insert a digit into the ItemNo field (I've renamed and
removed the # symbol and replaced them with "No") when I insert a new
record.
Click to expand...

Please post your code, and indicate the datatypes of your fields. If either of
them are Text fields instead of Number you need to tweak the code a bit.
 
John,

I was able to delete the record after entering your code, but the form
will not insert a digit into the ItemNo field (I've renamed and
removed the # symbol and replaced them with "No") when I insert a new
record.
Click to expand...

Please post your code, and indicate the datatypes of your fields. If either of
them are Text fields instead of Number you need to tweak the code a bit.
 
After screwing up and putting your code in the wrong form, I finally
got it located in the subform where it belongs. However it will not
insert a new Item number in the ItemNo field when I add a record. I
also get an error message when I try to exit the form which says:
Run time error 2465.
Microsoft Access can't fiind the field 'I' referred to inyour
expression. (the 'I' looks like a pipe symbol that I couldn't find on
my keyboard)

Here's what I entered:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![ItemNo] = Nz(DMax("[ItemNo]", "[Items]", "[E58No] = " &
Me![E58No])) + 1
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[ItemNo]", "Items", _
"[E58No] = " & Me.[E58No] & " AND [ItemNo] = " & Me![ItemNo])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
Click to expand...

The code assumes that your Subform has controls named [E85No] and [ItemNo]. If
it doesn't, add controls of those names bound to the corresponding fields, or
(perhaps better) textboxes named txtE85No and txtItemNo and replace the
Me![E85No] and Me![ItemNo] references with Me![txtE85No] and Me![txtItemno].
The textboxes can be invisible if you wish.
 
After screwing up and putting your code in the wrong form, I finally
got it located in the subform where it belongs. However it will not
insert a new Item number in the ItemNo field when I add a record. I
also get an error message when I try to exit the form which says:
Run time error 2465.
Microsoft Access can't fiind the field 'I' referred to inyour
expression. (the 'I' looks like a pipe symbol that I couldn't find on
my keyboard)

Here's what I entered:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![ItemNo] = Nz(DMax("[ItemNo]", "[Items]", "[E58No] = " &
Me![E58No])) + 1
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[ItemNo]", "Items", _
"[E58No] = " & Me.[E58No] & " AND [ItemNo] = " & Me![ItemNo])) Then
MsgBox "Duplicate item number!", vbOKOnly
Cancel = True
End If
End Sub
Click to expand...

The code assumes that your Subform has controls named [E85No] and [ItemNo]. If
it doesn't, add controls of those names bound to the corresponding fields, or
(perhaps better) textboxes named txtE85No and txtItemNo and replace the
Me![E85No] and Me![ItemNo] references with Me![txtE85No] and Me![txtItemno].
The textboxes can be invisible if you wish.
 
Please post your code, and indicate the datatypes of your fields. If either of
them are Text fields instead of Number you need to tweak the code a bit.
Click to expand...


My two fields in question are both text fields. I can probably change
them to Number if you think it will work better.
 
Back
Top