On Thu, 14 May 2009 19:01:48 -0500, Russ wrote:

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

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

John W. Vinson [MVP]