No duplicate records problem

  • Thread starter Thread starter Russ
  • Start date Start date
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.
 
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.
Click to expand...


Although I did initailly change the field names to E58No and ItemNo, I
neglected to change the control names in the form. I did change them
after your advice, but I'm still getting the above problem.
 
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.
Click to expand...


Although I did initailly change the field names to E58No and ItemNo, I
neglected to change the control names in the form. I did change them
after your advice, but I'm still getting the above problem.
 
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.
Click to expand...

I changed the field types to Number Long integer. In the ItemNo field
of the form, I've got <4 in the validation rule property. When I
enter 4 in that field I get the required validation text I wanted, but
after deleting the digit or trying to delete the entire record I
cannot get out of the field or close the form without the same message
popping up.
 
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.
Click to expand...

I changed the field types to Number Long integer. In the ItemNo field
of the form, I've got <4 in the validation rule property. When I
enter 4 in that field I get the required validation text I wanted, but
after deleting the digit or trying to delete the entire record I
cannot get out of the field or close the form without the same message
popping up.
 
My two fields in question are both text fields. I can probably change
them to Number if you think it will work better.
Click to expand...

Text should be ok, but since you're doing arithmatic on it, Number... Long
Integer would be better for ItemNo. If E58No is a Text field you just need
some syntactially required quotemarks, either ' or " - the singlequote is
easier. Try changing the datatype of ItemNo and use

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
 
My two fields in question are both text fields. I can probably change
them to Number if you think it will work better.
Click to expand...

Text should be ok, but since you're doing arithmatic on it, Number... Long
Integer would be better for ItemNo. If E58No is a Text field you just need
some syntactially required quotemarks, either ' or " - the singlequote is
easier. Try changing the datatype of ItemNo and use

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.
Click to expand...

I changed the field types to Number Long integer. In the ItemNo field
of the form, I've got <4 in the validation rule property. When I
enter 4 in that field I get the required validation text I wanted, but
after deleting the digit or trying to delete the entire record I
cannot get out of the field or close the form without the same message
popping up.
Click to expand...

oops... we're crossing messages in the two different subthreads here! I just
posted how to do it with text fields. You're better off with numbers, just
leave out the ' marks in my other answer.

The BeforeInsert code should make it *unnecessary* to manually enter an ItemNo
value, since it will calculate one for you automatically. Can you just hit
<Esc><Esc> to cancel editing the record? Why the validation rule anyway - you
want to limit it to three items? Note that a validation rule of < 4 permits
-12320185123 as a valid itemno... <g>
 
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.
Click to expand...

I changed the field types to Number Long integer. In the ItemNo field
of the form, I've got <4 in the validation rule property. When I
enter 4 in that field I get the required validation text I wanted, but
after deleting the digit or trying to delete the entire record I
cannot get out of the field or close the form without the same message
popping up.
Click to expand...

oops... we're crossing messages in the two different subthreads here! I just
posted how to do it with text fields. You're better off with numbers, just
leave out the ' marks in my other answer.

The BeforeInsert code should make it *unnecessary* to manually enter an ItemNo
value, since it will calculate one for you automatically. Can you just hit
<Esc><Esc> to cancel editing the record? Why the validation rule anyway - you
want to limit it to three items? Note that a validation rule of < 4 permits
-12320185123 as a valid itemno... <g>
 
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.
Click to expand...

I changed the field types to Number Long integer. In the ItemNo field
of the form, I've got <4 in the validation rule property. When I
enter 4 in that field I get the required validation text I wanted, but
after deleting the digit or trying to delete the entire record I
cannot get out of the field or close the form without the same message
popping up.
Click to expand...

oops... we're crossing messages in the two different subthreads here! I just
posted how to do it with text fields. You're better off with numbers, just
leave out the ' marks in my other answer.

The BeforeInsert code should make it *unnecessary* to manually enter an ItemNo
value, since it will calculate one for you automatically. Can you just hit
<Esc><Esc> to cancel editing the record? Why the validation rule anyway - you
want to limit it to three items? Note that a validation rule of < 4 permits
-12320185123 as a valid itemno... <g>
Click to expand...


You are right about not having to enter a number in the ItemNo field.
I had been manually entering it myself, but when I started writing the
description of the ItemNo one was entered for me. Very good.

I've put the validation rule in there to liimit the entry to no more
than 3 items per E58No. However, it does not appear to be doing it's
job since the code was able to enter a 4th ItemNo. I will change it
to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that
will solve the *4th ItemNo* problem.
 
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.
Click to expand...

I changed the field types to Number Long integer. In the ItemNo field
of the form, I've got <4 in the validation rule property. When I
enter 4 in that field I get the required validation text I wanted, but
after deleting the digit or trying to delete the entire record I
cannot get out of the field or close the form without the same message
popping up.
Click to expand...

oops... we're crossing messages in the two different subthreads here! I just
posted how to do it with text fields. You're better off with numbers, just
leave out the ' marks in my other answer.

The BeforeInsert code should make it *unnecessary* to manually enter an ItemNo
value, since it will calculate one for you automatically. Can you just hit
<Esc><Esc> to cancel editing the record? Why the validation rule anyway - you
want to limit it to three items? Note that a validation rule of < 4 permits
-12320185123 as a valid itemno... <g>
Click to expand...


You are right about not having to enter a number in the ItemNo field.
I had been manually entering it myself, but when I started writing the
description of the ItemNo one was entered for me. Very good.

I've put the validation rule in there to liimit the entry to no more
than 3 items per E58No. However, it does not appear to be doing it's
job since the code was able to enter a 4th ItemNo. I will change it
to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that
will solve the *4th ItemNo* problem.
 
You are right about not having to enter a number in the ItemNo field.
I had been manually entering it myself, but when I started writing the
description of the ItemNo one was entered for me. Very good.

I've put the validation rule in there to liimit the entry to no more
than 3 items per E58No. However, it does not appear to be doing it's
job since the code was able to enter a 4th ItemNo. I will change it
to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that
will solve the *4th ItemNo* problem.
Click to expand...

Trap the fourth itemno in the BeforeInsert event instead:

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[Items]", " [E58No] = " & Me![E58No]) >= 3 Then
MsgBox "Only three items per E58 please", vbOKOnly
Cancel = True
Else
Me![ItemNo] = Nz(DMax("[ItemNo]","[Items]","[E58No] = " & Me![E58No])) + 1
End If
End Sub

The problem with the validation rule is that it fires too late - after you've
entered all the data for the record and then try to save it.
 
You are right about not having to enter a number in the ItemNo field.
I had been manually entering it myself, but when I started writing the
description of the ItemNo one was entered for me. Very good.

I've put the validation rule in there to liimit the entry to no more
than 3 items per E58No. However, it does not appear to be doing it's
job since the code was able to enter a 4th ItemNo. I will change it
to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that
will solve the *4th ItemNo* problem.
Click to expand...

Trap the fourth itemno in the BeforeInsert event instead:

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[Items]", " [E58No] = " & Me![E58No]) >= 3 Then
MsgBox "Only three items per E58 please", vbOKOnly
Cancel = True
Else
Me![ItemNo] = Nz(DMax("[ItemNo]","[Items]","[E58No] = " & Me![E58No])) + 1
End If
End Sub

The problem with the validation rule is that it fires too late - after you've
entered all the data for the record and then try to save it.
 
You are right about not having to enter a number in the ItemNo field.
I had been manually entering it myself, but when I started writing the
description of the ItemNo one was entered for me. Very good.

I've put the validation rule in there to liimit the entry to no more
than 3 items per E58No. However, it does not appear to be doing it's
job since the code was able to enter a 4th ItemNo. I will change it
to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that
will solve the *4th ItemNo* problem.
Click to expand...

Trap the fourth itemno in the BeforeInsert event instead:

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[Items]", " [E58No] = " & Me![E58No]) >= 3 Then
MsgBox "Only three items per E58 please", vbOKOnly
Cancel = True
Else
Me![ItemNo] = Nz(DMax("[ItemNo]","[Items]","[E58No] = " & Me![E58No])) + 1
End If
End Sub

The problem with the validation rule is that it fires too late - after you've
entered all the data for the record and then try to save it.
Click to expand...

Very nice John! Thank you very much for the help and the extra added
info.

Russ
 
You are right about not having to enter a number in the ItemNo field.
I had been manually entering it myself, but when I started writing the
description of the ItemNo one was entered for me. Very good.

I've put the validation rule in there to liimit the entry to no more
than 3 items per E58No. However, it does not appear to be doing it's
job since the code was able to enter a 4th ItemNo. I will change it
to 1 Or 2 Or 3 to eliiminate the minus numbers, but I don't think that
will solve the *4th ItemNo* problem.
Click to expand...

Trap the fourth itemno in the BeforeInsert event instead:

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[Items]", " [E58No] = " & Me![E58No]) >= 3 Then
MsgBox "Only three items per E58 please", vbOKOnly
Cancel = True
Else
Me![ItemNo] = Nz(DMax("[ItemNo]","[Items]","[E58No] = " & Me![E58No])) + 1
End If
End Sub

The problem with the validation rule is that it fires too late - after you've
entered all the data for the record and then try to save it.
Click to expand...

Very nice John! Thank you very much for the help and the extra added
info.

Russ
 
Back
Top