DMax Syntax (?)

[tbl]

On a data-entry subform, there are two fields involved:
"PageNumber" (Integer) and "LineNumber" (Integer).

I've managed to get buttons on the main form to nudge the
PageNumber DefaultValue up or down by one, and that seems to
be working well enough.

I can get the LineNumber DefaultValue to ratchet up one, by
putting some code (using DMax) in the subform's OnCurrent
event.

But what I need is for the DMax's Where statement to filter
for the maximum stored PageNumber as well as
mainform/subform link. I just can't seem to get this to
work:

Dim strWherePage As String
Dim strWhere As String
Dim PageMax As String

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

Any help appreciated.

 

[Douglas J. Steele]

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

 

[tbl]

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

Thanks for the reply, Douglas.

I tried that syntax without success. This message comes up:

Microsoft Access
----------------

Syntax error (missin operator) in query expression

'[CountId]==nz(DMax("Page","tblCountDetail","[CountId]
= Formsk![frmCount]![CountId"))'.


I had to retype the above from the message box. I think I
got it 100%, but my fingers have ways of making errors that
my eyes can't find. I'll test this group's rules for
attachments.

Could it be that "Page" is a reserved word? I've tried
searching the 2002 help file for "Reserved" ("reserved
words" came up with no hits) and came up with 13 help pages
to read, none of which contained the word "reserved".
Sigh...

 

[Douglas J. Steele]

Yes, Page is a reserved word, but you've got it enclosed in square brackets.
(For a comprehensive list of reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Post exactly what your code looks like.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

Thanks for the reply, Douglas.

I tried that syntax without success. This message comes up:

Microsoft Access
----------------

Syntax error (missin operator) in query expression

'[CountId]==nz(DMax("Page","tblCountDetail","[CountId]
= Formsk![frmCount]![CountId"))'.


I had to retype the above from the message box. I think I
got it 100%, but my fingers have ways of making errors that
my eyes can't find. I'll test this group's rules for
attachments.

Could it be that "Page" is a reserved word? I've tried
searching the 2002 help file for "Reserved" ("reserved
words" came up with no hits) and came up with 13 help pages
to read, none of which contained the word "reserved".
Sigh...

 

[tbl]

Yes, Page is a reserved word, but you've got it enclosed in square brackets.
(For a comprehensive list of reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Post exactly what your code looks like.

Ok:

Dim strWherePage As String
Dim strWhere As String

If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Me![txtPage].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId] & "
And [Page] = " & Me![txtPage].DefaultValue

Me![txtLine].DefaultValue = Nz(DMax("CountLine",
"tblCountDetail", strWhere), 0) + 1

End If

 

[Douglas J. Steele]

What's actually in strWhere?

Use either Debug.Print or MsgBox after you set it, but before you use it.
(Probably better to use Debug.Print so that you can copy what you see in the
Immediate Window into your response, as opposed to retyping)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, Page is a reserved word, but you've got it enclosed in square
brackets.
(For a comprehensive list of reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Post exactly what your code looks like.

Ok:

Dim strWherePage As String
Dim strWhere As String

If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Me![txtPage].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId] & "
And [Page] = " & Me![txtPage].DefaultValue

Me![txtLine].DefaultValue = Nz(DMax("CountLine",
"tblCountDetail", strWhere), 0) + 1

End If

 

[tbl]

What's actually in strWhere?

Use either Debug.Print or MsgBox after you set it, but before you use it.
(Probably better to use Debug.Print so that you can copy what you see in the
Immediate Window into your response, as opposed to retyping)

I don't know how to do those things!

 

[Douglas J. Steele]

Please don't trim so much from your replies: it's a real nuisance to have to
find your previous posts to get the details.

Dim strWherePage As String
Dim strWhere As String

If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Me![txtPage].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId] & _)
" And [Page] = " & Me![txtPage].DefaultValue
Debug.Print strWhere
Me![txtLine].DefaultValue = _
Nz(DMax("CountLine", "tblCountDetail", strWhere), 0) + 1
End If

Once the code runs, use Ctrl-G to get to the debug window to see what's been
printed there.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I don't know how to do those things!

 

[tbl]

Please don't trim so much from your replies: it's a real nuisance to have to
find your previous posts to get the details.

Oops! Sorry, I've used a newsreader for so long that I
forget that not everyone does.

Dim strWherePage As String
Dim strWhere As String

If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Me![txtPage].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId] & _)
" And [Page] = " & Me![txtPage].DefaultValue
Debug.Print strWhere
Me![txtLine].DefaultValue = _
Nz(DMax("CountLine", "tblCountDetail", strWhere), 0) + 1
End If

Once the code runs, use Ctrl-G to get to the debug window to see what's been
printed there.

Thanks Doug. The office is crazy right now, but I'll get on
that ASAP.

 

[tbl]

Please don't trim so much from your replies: it's a real nuisance to have to
find your previous posts to get the details.

Dim strWherePage As String
Dim strWhere As String

If Me.Parent.NewRecord Then
Me![txtLine].DefaultValue = 1
Me![txtPage].DefaultValue = 1
Else
strWhere = "[CountId] = " & Me.Parent![CountId] & _)
" And [Page] = " & Me![txtPage].DefaultValue
Debug.Print strWhere
Me![txtLine].DefaultValue = _
Nz(DMax("CountLine", "tblCountDetail", strWhere), 0) + 1
End If

Once the code runs, use Ctrl-G to get to the debug window to see what's been
printed there.

Not to dodge your question, but I found and interesting
entry in the property page for [txtLine] on the form: the
DefaultValue had an entry starting with "nz", that looked
like something from my code behind the form's OnCurrent
event. It's possible that I put that in there at some
point, although I sure don't remember doing so. I deleted
that, smoothed out a couple of other nits, and now all is
working exactly as I like!

My code for the OnCurrent event of the subform is now like
this:

Dim strWhere As String
Dim strWhere2 As String

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

Exit_Form_Current:
Exit Sub

Err_Form_Current:
MsgBox Err.Description
Resume Exit_Form_Current

End Sub


Thanks so much for your help.