DMAX Function

[cemjs via AccessMonster.com]

Problem using the DMax function.
1st table named "tblCase" and a field in the table called "AgencyCaseNumber".

2nd table named "tblLead" and a field in that table called "LeadNumber".

On adding a new record (command button) I am trying to get the lead number to
auto - increase by one, but it needs to reset to 1 for each new case number.

I am using a main form called "frmMain" which contains my case number and
then a sub-form on that called "subfrmlead" which contains my lead number and
my command button.

Have been playing with the following code, but unable to get it to work.

DoCmd.GoToRecord , , acNewRec
AllowEdits = True

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

 

[Allen Browne]

It might work better to assign this number on the first keystroke when a new
record is being added:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "AgencyCaseNumber = """" & !AgencyCaseNumber & """"
Me!LeadNumber = Nz(DLookup("LeadNumber", "tblLead",
strWhere),0)+1
End If
End With
End Sub

Remove the extra quotes if AgencyCaseNumber is a Number field (not a Text
field)

 

[cemjs via AccessMonster.com]

Still having difficulty, it resets the leadnumber to 1 everytime.

It might work better to assign this number on the first keystroke when a new
record is being added:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "AgencyCaseNumber = """" & !AgencyCaseNumber & """"
Me!LeadNumber = Nz(DLookup("LeadNumber", "tblLead",
strWhere),0)+1
End If
End With
End Sub

Remove the extra quotes if AgencyCaseNumber is a Number field (not a Text
field)

Problem using the DMax function.
1st table named "tblCase" and a field in the table called

[quoted text clipped - 23 lines]

_
"AgencyCaseNumber & = """ & Me.Parent!AgencyCaseNumber & """") + 1)

 

[Allen Browne]

If you open your table in design view, what is the Data Type of LeadNumber?
(We assume it is Number, not Text.)

Does the AgencyCaseNumber on the main form actually have a value when this
happens?

For debugging purposes, try the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "AgencyCaseNumber = """" & !AgencyCaseNumber & """"
varResult = DLookup("LeadNumber", "tblLead",strWhere)

Debug.Print !AgencyCaseNumber
Debug.Print varResult
Stop

Me!LeadNumber = Nz(varResult, 0) + 1
End If
End With
End Sub

Start adding a new record below some others in the subform (so it should
pick a higher number.) It will stop and take you to the code window with the
Stop line highlighted. Press Ctrl+G to open the Immediate Window. You should
see 2 lines there, such as
xyz
2
where xyz represents the AgencyCaseNumber in the main form, and 2 represents
the highest line number for this case. You can test what's going on. Press
F5 to continue execution.

Once you have it sorted out, remove the 3 lines in the middle.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Still having difficulty, it resets the leadnumber to 1 everytime.

It might work better to assign this number on the first keystroke when a
new
record is being added:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "AgencyCaseNumber = """" & !AgencyCaseNumber & """"
Me!LeadNumber = Nz(DLookup("LeadNumber", "tblLead",
strWhere),0)+1
End If
End With
End Sub

Remove the extra quotes if AgencyCaseNumber is a Number field (not a Text
field)

Problem using the DMax function.
1st table named "tblCase" and a field in the table called

[quoted text clipped - 23 lines]

_
"AgencyCaseNumber & = """ & Me.Parent!AgencyCaseNumber & """") + 1)

 

[cemjs via AccessMonster.com]

This is what I get for the lines:
Test Case # 1
Null

If you open your table in design view, what is the Data Type of LeadNumber?
(We assume it is Number, not Text.)

Does the AgencyCaseNumber on the main form actually have a value when this
happens?

For debugging purposes, try the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "AgencyCaseNumber = """" & !AgencyCaseNumber & """"
varResult = DLookup("LeadNumber", "tblLead",strWhere)

Debug.Print !AgencyCaseNumber
Debug.Print varResult
Stop

Me!LeadNumber = Nz(varResult, 0) + 1
End If
End With
End Sub

Start adding a new record below some others in the subform (so it should
pick a higher number.) It will stop and take you to the code window with the
Stop line highlighted. Press Ctrl+G to open the Immediate Window. You should
see 2 lines there, such as
xyz
2
where xyz represents the AgencyCaseNumber in the main form, and 2 represents
the highest line number for this case. You can test what's going on. Press
F5 to continue execution.

Once you have it sorted out, remove the 3 lines in the middle.

Still having difficulty, it resets the leadnumber to 1 everytime.

[quoted text clipped - 24 lines]

 

[Allen Browne]

The fact that the 2nd expression returns Null means that there are no
matches in the table yet.

Try adding the line:
Debug.Print strWhere
below the other two.

This should return:
AgencyCaseNumber = "Test Case # 1"
with the quotes.

If you get that, try this expression in the Immediate Window:
? DLookup("LeadNumber", "tblLead", "AgencyCaseNumber = ""Test Case #
1""")

If that still gives you Null, the tblLead doesn't contain any LeadNumber for
Test Case #1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This is what I get for the lines:
Test Case # 1
Null

If you open your table in design view, what is the Data Type of
LeadNumber?
(We assume it is Number, not Text.)

Does the AgencyCaseNumber on the main form actually have a value when this
happens?

For debugging purposes, try the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "AgencyCaseNumber = """" & !AgencyCaseNumber & """"
varResult = DLookup("LeadNumber", "tblLead",strWhere)

Debug.Print !AgencyCaseNumber
Debug.Print varResult
Stop

Me!LeadNumber = Nz(varResult, 0) + 1
End If
End With
End Sub

Start adding a new record below some others in the subform (so it should
pick a higher number.) It will stop and take you to the code window with
the
Stop line highlighted. Press Ctrl+G to open the Immediate Window. You
should
see 2 lines there, such as
xyz
2
where xyz represents the AgencyCaseNumber in the main form, and 2
represents
the highest line number for this case. You can test what's going on. Press
F5 to continue execution.

Once you have it sorted out, remove the 3 lines in the middle.

Still having difficulty, it resets the leadnumber to 1 everytime.

[quoted text clipped - 24 lines]

_
"AgencyCaseNumber & = """ & Me.Parent!AgencyCaseNumber & """") + 1)

 

[cemjs via AccessMonster.com]

I will try your suggestions, but when looking at the tblLead, Test Case #1
has two leads I previously typed in with the number 1 and 2, so my code
should return lead number 3.

My parent form and subform are linked through the CaseID

The fact that the 2nd expression returns Null means that there are no
matches in the table yet.

Try adding the line:
Debug.Print strWhere
below the other two.

This should return:
AgencyCaseNumber = "Test Case # 1"
with the quotes.

If you get that, try this expression in the Immediate Window:
? DLookup("LeadNumber", "tblLead", "AgencyCaseNumber = ""Test Case #
1""")

If that still gives you Null, the tblLead doesn't contain any LeadNumber for
Test Case #1.

This is what I get for the lines:
Test Case # 1

[quoted text clipped - 49 lines]

 

[cemjs via AccessMonster.com]

when I enter your next debug statement, this is what it returns

Test Case # 1
Null
AgencyCaseNumber = "" & !AgencyCaseNumber & ""

The fact that the 2nd expression returns Null means that there are no
matches in the table yet.

Try adding the line:
Debug.Print strWhere
below the other two.

This should return:
AgencyCaseNumber = "Test Case # 1"
with the quotes.

If you get that, try this expression in the Immediate Window:
? DLookup("LeadNumber", "tblLead", "AgencyCaseNumber = ""Test Case #
1""")

If that still gives you Null, the tblLead doesn't contain any LeadNumber for
Test Case #1.

This is what I get for the lines:
Test Case # 1

[quoted text clipped - 49 lines]

 

[cemjs via AccessMonster.com]

If I add the "agencycasenumber" field to my subform the debug then returns
the following.

AgencyCaseNumber = "" & !AgencyCaseNumber & ""
Test Case # 1
Null
AgencyCaseNumber = "" & !AgencyCaseNumber & ""

when I enter your next debug statement, this is what it returns

Test Case # 1
Null
AgencyCaseNumber = "" & !AgencyCaseNumber & ""

The fact that the 2nd expression returns Null means that there are no
matches in the table yet.

[quoted text clipped - 19 lines]

 

[Allen Browne]

The Debug.Print should yield something like this:
AgencyCaseNumber = "Test Case 1"

Therefore you have the quote marks wrong on the line that creates the
strWhere string. You need:
- one opening quote;
- 3 in the middle;
- 4 at the end.

Like this:
strWhere = "AgencyCaseNumber = """ & !AgencyCaseNumber & """"

Explanation:
http://allenbrowne.com/casu-17.html

 

[cemjs via AccessMonster.com]

So this is what I have so far:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."

Else
strWhere = "AgencyCaseNumber = """ & !AgencyCaseNumber & """"
varResult = DLookup("LeadNumber", "tblLead", strWhere)

Debug.Print !AgencyCaseNumber
Debug.Print varResult
Debug.Print strWhere
Stop

Me!LeadNumber = Nz(varResult, 0) + 1
End If
End With

This is what the debug reeturns:

AgencyCaseNumber = "Test Case # 1"
Test Case # 1
Null
AgencyCaseNumber = "Test Case # 1"


It still sets the lead number to 1, the next number should be 3

The Debug.Print should yield something like this:
AgencyCaseNumber = "Test Case 1"

Therefore you have the quote marks wrong on the line that creates the
strWhere string. You need:
- one opening quote;
- 3 in the middle;
- 4 at the end.

Like this:
strWhere = "AgencyCaseNumber = """ & !AgencyCaseNumber & """"

Explanation:
http://allenbrowne.com/casu-17.html

If I add the "agencycasenumber" field to my subform the debug then returns
the following.

[quoted text clipped - 3 lines]

Null
AgencyCaseNumber = "" & !AgencyCaseNumber & ""

 

[Allen Browne]

So your task is to understand why the DLookup() returns Null for the
condition:
AgencyCaseNumber = "Test Case # 1"

That implies that no records match the condition.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

So this is what I have so far:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."

Else
strWhere = "AgencyCaseNumber = """ & !AgencyCaseNumber & """"
varResult = DLookup("LeadNumber", "tblLead", strWhere)

Debug.Print !AgencyCaseNumber
Debug.Print varResult
Debug.Print strWhere
Stop

Me!LeadNumber = Nz(varResult, 0) + 1
End If
End With

This is what the debug reeturns:

AgencyCaseNumber = "Test Case # 1"
Test Case # 1
Null
AgencyCaseNumber = "Test Case # 1"


It still sets the lead number to 1, the next number should be 3

The Debug.Print should yield something like this:
AgencyCaseNumber = "Test Case 1"

Therefore you have the quote marks wrong on the line that creates the
strWhere string. You need:
- one opening quote;
- 3 in the middle;
- 4 at the end.

Like this:
strWhere = "AgencyCaseNumber = """ & !AgencyCaseNumber & """"

Explanation:
http://allenbrowne.com/casu-17.html

If I add the "agencycasenumber" field to my subform the debug then
returns
the following.

[quoted text clipped - 3 lines]

Null
AgencyCaseNumber = "" & !AgencyCaseNumber & ""

 

[cemjs via AccessMonster.com]

Still don't understand why the DLookup() retunrs the null value. Only thing I
can come up with is that the Parent/Child link between the main form and sub
form is through the CaseID and not the AgencyCaseNumber.

So your task is to understand why the DLookup() returns Null for the
condition:
AgencyCaseNumber = "Test Case # 1"

That implies that no records match the condition.

So this is what I have so far:
Dim strWhere As String

[quoted text clipped - 47 lines]

 

[Allen Browne]

So should you be using this:
"CaseID = " & !CaseID
for the critiera in DLookup(), assuming CaseID is a Number field?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Still don't understand why the DLookup() retunrs the null value. Only
thing I
can come up with is that the Parent/Child link between the main form and
sub
form is through the CaseID and not the AgencyCaseNumber.

So your task is to understand why the DLookup() returns Null for the
condition:
AgencyCaseNumber = "Test Case # 1"

That implies that no records match the condition.

So this is what I have so far:
Dim strWhere As String

[quoted text clipped - 47 lines]

Null
AgencyCaseNumber = "" & !AgencyCaseNumber & ""