DMAX

[cemjs via AccessMonster.com]

I have two tables, In one table I have a CaseNumber Field (which is test). In
the second table I have a LeadNumber field (which is a number field).

On my Main Form I have the CaseNumber Field and in a subform I have the
LeadNumber Field. When adding a new record (new lead) I am trying to get the
lead number to auto update to next number using DMAX) , but must reset to 1
for each new case number. This is what I have been working with.

If Nz(Me.LeadNumber, 0) = 0 Then
Me.LeadNumber = Nz(DMax("leadnumber", "tbllead", "agencycasenumber="" & Forms!
[frmMain].agencycasenumber &"""), 0) + 1

Resets the leadnumber to 1 for each new case, but does not allow to add the
next number.

 

[BruceM]

Is agencycasenumber related to CaseNumber in the main table?

If so, I think you need to drop the If, as it will only be true once for
each set of related records. Try:
Me.LeadNumber.DefaultValue = Nz(DMax("leadnumber", "tbllead", _
"agencycasenumber = """ & Me.Parent.agencycasenumber & """"), 0) + 1

I used Me.Parent rather than the Forms! etc. format you used, on the
assumption that frmMain is the main form, and the code is in the subform.
Also, note that I added extra quote marks. You could also use single quotes
(expanded for clarity):
Me.LeadNumber = Nz(DMax("leadnumber", "tbllead", _
"agencycasenumber = ' " &
Me.Parent.agencycasenumber & " ' "), 0) + 1

Note also that I used DefaultValue, which applies only to a new record, so
you won't be resetting LeadNumber every time you visit the record.

You don't say where you placed this code. In a single-user environment you
could put the code in the form's Current event. In a multi-user environment
you could place it in the form's Before Update event, which would help guard
against two users working on records simultaneously and trying to grab the
same number. If there are to be many users you may want to add error
handling in case of a duplicate number.

 

[Beetle]

You might try;

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

I have assumed you are doing this in code (otherwise you'll have to remove
the Parent reference). Also, it should all be on one line and there could be
some syntax errors as I am just writing this in the ng editor, but hopefully
you get the idea.

 

[cemjs via AccessMonster.com]

It is a single user format, Tried placing your code on the Form's Current
event, It does reset the counter to 1 for each new case, but still doesn't
increment past that

Is agencycasenumber related to CaseNumber in the main table?

If so, I think you need to drop the If, as it will only be true once for
each set of related records. Try:
Me.LeadNumber.DefaultValue = Nz(DMax("leadnumber", "tbllead", _
"agencycasenumber = """ & Me.Parent.agencycasenumber & """"), 0) + 1

I used Me.Parent rather than the Forms! etc. format you used, on the
assumption that frmMain is the main form, and the code is in the subform.
Also, note that I added extra quote marks. You could also use single quotes
(expanded for clarity):
Me.LeadNumber = Nz(DMax("leadnumber", "tbllead", _
"agencycasenumber = ' " &
Me.Parent.agencycasenumber & " ' "), 0) + 1

Note also that I used DefaultValue, which applies only to a new record, so
you won't be resetting LeadNumber every time you visit the record.

You don't say where you placed this code. In a single-user environment you
could put the code in the form's Current event. In a multi-user environment
you could place it in the form's Before Update event, which would help guard
against two users working on records simultaneously and trying to grab the
same number. If there are to be many users you may want to add error
handling in case of a duplicate number.

I have two tables, In one table I have a CaseNumber Field (which is test).
In

[quoted text clipped - 15 lines]

the
next number.

 

[BruceM]

Just to be clear, you are running the code in the subform's Current event?
I was not as clear on that as I should have been.

It is a single user format, Tried placing your code on the Form's Current
event, It does reset the counter to 1 for each new case, but still doesn't
increment past that

Is agencycasenumber related to CaseNumber in the main table?

If so, I think you need to drop the If, as it will only be true once for
each set of related records. Try:
Me.LeadNumber.DefaultValue = Nz(DMax("leadnumber", "tbllead", _
"agencycasenumber = """ & Me.Parent.agencycasenumber & """"), 0) +
1

I used Me.Parent rather than the Forms! etc. format you used, on the
assumption that frmMain is the main form, and the code is in the subform.
Also, note that I added extra quote marks. You could also use single
quotes
(expanded for clarity):
Me.LeadNumber = Nz(DMax("leadnumber", "tbllead", _
"agencycasenumber = ' " &
Me.Parent.agencycasenumber & " ' "), 0) + 1

Note also that I used DefaultValue, which applies only to a new record, so
you won't be resetting LeadNumber every time you visit the record.

You don't say where you placed this code. In a single-user environment
you
could put the code in the form's Current event. In a multi-user
environment
you could place it in the form's Before Update event, which would help
guard
against two users working on records simultaneously and trying to grab the
same number. If there are to be many users you may want to add error
handling in case of a duplicate number.

I have two tables, In one table I have a CaseNumber Field (which is
test).
In

[quoted text clipped - 15 lines]

the
next number.