On Thu, 14 May 2009 14:59:16 -0600, John W. Vinson
wrote:

On Thu, 14 May 2009 13:15:52 -0500, Russ wrote:



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#]", "[table]", "[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