On Thu, 14 May 2009 17:48:10 -0600, John W. Vinson
wrote:

On Thu, 14 May 2009 17:57:31 -0500, Russ wrote:

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.


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.