Can DMax Statement be added to Where Statement?

[tbl]

On a data entry form for entering data from paper "field"
forms, we want to record the paper's "line numbers" for the
rows.

It is very handy to have the DefaultValue for the Line
Number field be incremented automatically, so that only in
rare cases does the field need to be dealt with by the
person doing the data entry.

Allen Browne's CD Library database example gave me a
framework learn from that helped me to get this code
working:


Private Sub Form_Current()

Dim strWhere As String
If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId]
Me![txtLine].DefaultValue = Nz(DMax("CountLine",
"tblCountDetail", strWhere), 0) + 1
End If

End Sub


But now data is coming in larger quantities, where more than
one paper page is used. We want to also record the Page
Number, and whenever the Page Number changes, the Line
Numbers have to start over again.

So I need to modify the above code so that it also considers
the max. Page Number, providing the highest existing Line
Number for the current Page Number, plus 1.

I've taken a number of whacks at this, but it's just not
within my skill's range.

Anyone care to help?

 

[Marshall Barton]

On a data entry form for entering data from paper "field"
forms, we want to record the paper's "line numbers" for the
rows.

It is very handy to have the DefaultValue for the Line
Number field be incremented automatically, so that only in
rare cases does the field need to be dealt with by the
person doing the data entry.

Allen Browne's CD Library database example gave me a
framework learn from that helped me to get this code
working:


Private Sub Form_Current()

Dim strWhere As String
If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId]
Me![txtLine].DefaultValue = Nz(DMax("CountLine",
"tblCountDetail", strWhere), 0) + 1
End If

End Sub


But now data is coming in larger quantities, where more than
one paper page is used. We want to also record the Page
Number, and whenever the Page Number changes, the Line
Numbers have to start over again.

So I need to modify the above code so that it also considers
the max. Page Number, providing the highest existing Line
Number for the current Page Number, plus 1.

I think you only need to change it like:

strWhere = "[CountId] = " & Me.Parent![CountId] _
& " And [Page Number] = " & Me.Parent![Page Number]

 

[tbl]

On a data entry form for entering data from paper "field"
forms, we want to record the paper's "line numbers" for the
rows.

It is very handy to have the DefaultValue for the Line
Number field be incremented automatically, so that only in
rare cases does the field need to be dealt with by the
person doing the data entry.

Allen Browne's CD Library database example gave me a
framework learn from that helped me to get this code
working:


Private Sub Form_Current()

Dim strWhere As String
If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId]
Me![txtLine].DefaultValue = Nz(DMax("CountLine",
"tblCountDetail", strWhere), 0) + 1
End If

End Sub


But now data is coming in larger quantities, where more than
one paper page is used. We want to also record the Page
Number, and whenever the Page Number changes, the Line
Numbers have to start over again.

So I need to modify the above code so that it also considers
the max. Page Number, providing the highest existing Line
Number for the current Page Number, plus 1.

I think you only need to change it like:

strWhere = "[CountId] = " & Me.Parent![CountId] _
& " And [Page Number] = " & Me.Parent![Page Number]

Thanks Marsh. I'll give that a go when I get back in the
office tomorrow.