Dmax

[cemjs via AccessMonster.com]

Currently have two tables, first table named (tblCase) with a field
(AgencyCaseNumber)
Second table named (tblLead) with a field called (LeadNumber)

Main form contains the field AgencyCasenumber
Subform contains the field LeadNumber
On a control button to add a new lead I want the lead number to auto-
increment based on the case number.

I have been playing with the following code, but can not get it to work
properly.

Me!LeadNumber = IIf(Nz(DLookup("LeadNumber", "tblLead", "AgencyCaseNumber =
""" & _
Me.Parent!AgencyCaseNumber & """"), 0) = 0, 1, DMax("LeadNumber", "tblLead",
_
"AgencyCaseNumber & = """ & Me.Parent!AgencyCaseNumber & """") + 1)