No duplicate records problem

[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.

 

[Fred]

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.

 

[Fred]

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.

 

[Russ]

How do you do that? I've only seen indexes on a single field with my
2k Access.

You could also set it to ignore nulls.

Same question.

 

[Russ]

How do you do that? I've only seen indexes on a single field with my
2k Access.

You could also set it to ignore nulls.

Same question.

 

[Fred]

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.

 

[Fred]

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.

 

[John W. Vinson]

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.

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

 

[John W. Vinson]

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.

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

 

[Russ]

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.

 

[Russ]

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.

 

[Russ]

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.

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

 

[Russ]

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.

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

 

[Russ]

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

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

 

[Russ]

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

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 W. Vinson]

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.

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 W. Vinson]

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.

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 W. Vinson]

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

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.

 

[John W. Vinson]

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

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.

 

[Russ]

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.

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