DMax fails to Increment beyond "10"

[Scott]

I have a database with an input form. The field "DailyID" on the form
is to increment automatically with each new entry, then reset itself
back to "1" at midnight and/or the change of date.

However, the DAILYID increments automatically to "10" just fine, but
does not go beyond "10" until the date changes.

I have the following code in my form's CURRENT event:

Private Sub Form_Current()

Dim datCur As Date
Dim datMax As Date
Dim intDailyID As Integer

datCur = Me!DateIn
datMax = Nz(DMax("DateIn ", "Documents" ), Date)

If Me.NewRecord Then
If datCur = datMax Then
intDailyID = Nz(DMax("[DailyID]", "Documents" , "[DateIn]=
#" & Me!DateIn & "#"), 0) + 1

Else
intDailyID = 1
End If

Me!DailyID = intDailyID

End If

End Sub
Any clues/ideas how I can solve this? I'm running Access 2002 (XP) on
a network.
Thanks in advance!

[TC]

This probably does not solve your problem, but:

(1) The Current event is not particular appropriate for what you are doing.
I would use the form level BeforeUpdate event, testing for Me.NewRecord.
That event fires when all the data has been entered, & the user has asked to
save the record.

(2) This code is not very safe: "[DateIn]=#" & Me!DateIn & "#"
The #...# format requires the date to be in U.S. (month/day/year) format,

regardless of your PC's settings<. With your code, you can't be certain that DateIn will be the right format. Use a Format$() function to convert
the date to month/day/year format for use in the SQL statement.

HTH,
TC
"Scott" <he************ *@yahoo.com> wrote in message
news:8e******** *************** ***@posting.goo gle.com... I have a database with an input form. The field "DailyID" on the form
is to increment automatically with each new entry, then reset itself
back to "1" at midnight and/or the change of date.

However, the DAILYID increments automatically to "10" just fine, but
does not go beyond "10" until the date changes.

I have the following code in my form's CURRENT event:

Private Sub Form_Current()

Dim datCur As Date
Dim datMax As Date
Dim intDailyID As Integer

datCur = Me!DateIn
datMax = Nz(DMax("DateIn ", "Documents" ), Date)

If Me.NewRecord Then
If datCur = datMax Then
intDailyID = Nz(DMax("[DailyID]", "Documents" , "[DateIn]=
#" & Me!DateIn & "#"), 0) + 1

Else
intDailyID = 1
End If

Me!DailyID = intDailyID

End If

End Sub
Any clues/ideas how I can solve this? I'm running Access 2002 (XP) on
a network.
Thanks in advance!

[Rick Brandt]

"Scott" <he************ *@yahoo.com> wrote in message
news:8e******** *************** ***@posting.goo gle.com...

I have a database with an input form. The field "DailyID" on the form
is to increment automatically with each new entry, then reset itself
back to "1" at midnight and/or the change of date.

However, the DAILYID increments automatically to "10" just fine, but
does not go beyond "10" until the date changes.

Sounds like DAILYID is a text field. Once you get to 9 you get stuck there
because the text string "10" is actually less than 9.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[troy.scheh]

you rock. i just changed the field in my table to number from text and
bingo...11 12 13... thanks.
Rick Brandt wrote:

"Scott" <he************ *@yahoo.com> wrote in message
news:8e******** *************** ***@posting.goo gle.com...

I have a database with an input form. The field "DailyID" on the form is to increment automatically with each new entry, then reset itself back to "1" at midnight and/or the change of date.

However, the DAILYID increments automatically to "10" just fine, but does not go beyond "10" until the date changes.
Sounds like DAILYID is a text field. Once you get to 9 you get stuck

there because the text string "10" is actually less than 9.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com