Sequential numbering on form by date 1

Mudskipper1000 · Oct 2, 2004

I know you've had similar, however, I need to number each new record added to a form, display in on the form throughout the month from 1st - ...
Then reset count on form to "0" for next new month and so on. Can this be done???? Thanks in advance.

MichaelRed · Oct 3, 2004

check (search) the faqs re autonumber.

MichaelRed

TheAceMan1 · Oct 3, 2004

How are ay Mudskipper1000 . . . . .

The source for the [blue]Month[/blue], will that come from a [blue]Date Field[/blue], [blue]Actual Current Date[/blue], or what?

See Ya! . . . . . .

Mudskipper1000 · Oct 3, 2004

Thanks guys...
Ace. The way this form is suppose to work is this....
I have a date field, Text box field (this will be for number???) and Surname field. Each record must be numbered (1,2,3 etc...)
numbering all new cases for full month, starting back at
zero next new month and so on. If this can't work then what I will need to do is continually (i think) recreate that one table every month.

TheAceMan1 · Oct 5, 2004

Mudskipper1000 . . . . .

Sorry to get back so late.

In the [blue]Before Update[/blue] event of the [blue]Date Field[/blue] copy/paste the following code (you substitute proper names in [purple]purple[/purple]):

Code:

[blue]   Dim maxIdx, Criteria As String
   Dim namIdx As String, namDate As String, namTable As String
   
   If Me.NewRecord Then
      namDate = "[purple][b]DateFieldName[/b][/purple]"
      namIdx = "[purple][b]IndexFieldName[/b][/purple]"
      namTable = "[purple][b]TableName[/b][/purple]"
      Criteria = "Month([" & namDate & "]) = " & Month(Me(namDate)) & _
                 " And " & _
                 "Year([" & namDate & "]) = " & Year(Me(namDate))
      maxIdx = DMax("[" & namIdx & "]", namTable, Criteria)
      
      If IsNull(maxIdx) Then
         Me(namIdx) = 0 [green]'Start over on new month & year[/green]
      Else
         Me(namIdx) = maxIdx + 1
      End If
   End If[/blue]

After you type in the date [blue]for a new record[/blue], the index is posted.

I found it [blue]necessary to ping against month & year[/blue] to allow the month to be zeroed properly. This way, if you find you forgot to add records to a previous month & year, [blue]the code will pickup the proper index![/blue]

[blue]Cheers![/blue]

See Ya! . . . . . .

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sequential numbering on form by date 1

Mudskipper1000

Technical User

MichaelRed

Programmer

TheAceMan1

Programmer

Mudskipper1000

Technical User

TheAceMan1

Programmer

Similar threads

Part and Inventory Search

Sponsor