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.

 

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

I should have mentioned that on the paper forms, the field
clerk fills in page numbers manually, and in the Access db,
the page number doesn't exist in the parent table, only in
the table that corresponds to the sub form. Like:

Page No. Line No. Data1 Data2...

So to get the next appropriate line number, the procedure
has to figure out first what is the highest page number for
this parent record, and then figure out what the highest
saved line number is, add one to it, and make that number
the value (or the default value) for line number.

The proper syntax for all that is simply beyond my skills.

Thanks for your 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]

I should have mentioned that on the paper forms, the field
clerk fills in page numbers manually, and in the Access db,
the page number doesn't exist in the parent table, only in
the table that corresponds to the sub form. Like:

Page No. Line No. Data1 Data2...

So to get the next appropriate line number, the procedure
has to figure out first what is the highest page number for
this parent record, and then figure out what the highest
saved line number is, add one to it, and make that number
the value (or the default value) for line number.

If the user is not entering the page number, then you have
more to worry about than just the line number. You will
also have to determine when to start a new page and what the
new page number should be (max+1?) or does the user decide
when to enter a new page number. But if the user enters the
page number, what I posted was what you asked for.

If the user does not enter the page, then you need to
specify how its handled.

But, there has got to be even more to this than just that.
It seems that these "lines" are probably in a subform, in
which case these DMax functions will have to take the Link
Child Fields property setting into account too. In other
words, how do the pages relate to the main form record?

 

[tbl]

If the user is not entering the page number, then you have
more to worry about than just the line number. You will
also have to determine when to start a new page and what the
new page number should be (max+1?) or does the user decide
when to enter a new page number. But if the user enters the
page number, what I posted was what you asked for.

If the user does not enter the page, then you need to
specify how its handled.

But, there has got to be even more to this than just that.
It seems that these "lines" are probably in a subform, in
which case these DMax functions will have to take the Link
Child Fields property setting into account too. In other
words, how do the pages relate to the main form record?

Sorry Marshall, it just dawned on me that I didn't explain
that part well at all. I'll try again.


This is a form/subform pair, where the main form and it's
table hold the data that comes from the paper form's
"header".

The subform and its table hold itemized details (the "lines"
on the paper form) relating to the main form records.

We must keep all paper indefinitely, and be able to "audit"
back and forth between paper and computerized db, so it's a
real plus to have the line numbers from the paper get put
into the db.

On paper, the field clerk writes in the page number, and all
sheets have lines numbered 1-30 for the detail data.

In the Access db, both Page Number and Line Number are
stored in the table relating to the subform.

It's easy to have the page numer field have a default value
of "1", and I've gone so far as to place buttons on the
data-entry form for bumping the default page number up a
click, or down a click. In my most recent iteration, I've
changed the DefaultValue to:

=nz(DMax("Page","tblCountDetail","[CountId] = _
Forms![frmCount]![CountId]"))

.... but I haven't enough experience with it to know if there
are hidden bombs awaiting me.

When the DE operator gets line 30 filled in, they can click
on the "up" button to bump the default page number up to the
next page number, and they're good to go for the next 30
lines.

But with page numbers in the frey, the default for line
number becomes more problematic. DMax (or something) has to
analyze for maximum Page Number (subform data) for this main
form record (and hold onto it for the moment), and then
figure out the maximum Line Number (also subform data) for
this main form record AND this maximum Page Number, and then
add one to the resulting line number to make the
DefaultValue for the next Line Number.

Rarely does a line on paper get scratched out or otherwise
not used, so it's no big deal if the DE operator has to grab
the mouse and *put* the line number in somewhat manually.

My only problem is that I don't know how to do it! ;-l

Does this make anything clearer? Muddier?