Solved SIMPLE RECORD NUMBER START FROM 01 EACH NEW MONTH (1 Viewer)

[georg0307]

Dear All,

I have a simple Access Data Base that records truck arrival.
I need a field that automatically give a sequential number to the new record (new truck arrival), 1,2,3,4..etc
And at arrival of the new Month, start again with 1,2,3,4..etc, without any user intervention.

Attached a draft of my DB.

Thanks in advance
Best regards

 

[Ranman256]

AutoNum field does this with no programming.
Sort on this field for that month and you have the order.
In a report ,use a cumulative count if you must have 1 as the start.

Zero programming.


QUOTE="georg0307, post: 1673109, member: 134244"]
Dear All,

I have a simple Access Data Base that records truck arrival.
I need a field that automatically give a sequential number to the new record (new truck arrival), 1,2,3,4..etc
And at arrival of the new Month, start again with 1,2,3,4..etc, without any user intervention.

Attached a draft of my DB.

Thanks in advance
Best regards
[/QUOTE]

 

[ebs17]

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If.MeNewrecord Then
      Me.txtNr = Nz(DMax("Nr", "AnyTable", _
             "Format(ArrivalDate, 'yyyymm') = '" & Format(Me.txtArrivalDate, "yyyymm") & "'"), 0) + 1
   End If
End Sub

Eberhard

 

[arnelgp]

add code to textbox, DATA, AfterUpdate event:

Option Compare Database
Option Explicit

Private Sub Data_AfterUpdate()
    Dim RS As DAO.Recordset
   
    If IsDate(Me.Data & "") Then
   
        Me.NR = Val(DMax("NR", "ARRIVI_CAMION", "Format([Data], 'yyyymm') = '" & Format(Me.Data, "yyyynn") & "'") & "") + 1
   
    End If
   
End Sub

 

[georg0307]

add code to textbox, DATA, AfterUpdate event:

Option Compare Database
Option Explicit

Private Sub Data_AfterUpdate()
    Dim RS As DAO.Recordset
  
    If IsDate(Me.Data & "") Then
  
        Me.NR = Val(DMax("NR", "ARRIVI_CAMION", "Format([Data], 'yyyymm') = '" & Format(Me.Data, "yyyynn") & "'") & "") + 1
  
    End If
  
End Sub

 

[georg0307]

Hi,

sorry but it doesn't increment number...

Thanks for prompt reply

 

[arnelgp]

my fault this need to be changed:

Format(Me.Data, "yyyynn")

to

Format(Me.Data, "yyyymm")

Private Sub Data_AfterUpdate()  
Dim RS As DAO.Recordset     
If IsDate(Me.Data & "") Then         
    Me.NR = Val(DMax("NR", "ARRIVI_CAMION", "Format([Data], 'yyyymm') = '" & Format(Me.Data, "yyyymm") & "'") & "") + 1     
End If 
End Sub

 

[ebs17]

@arnelgp:

What is the meaning of the recordset in the code?
If you can overwrite an existing number with an existing meaning, you leave a sequential numbering because gaps can arise.


@georg0307

We use one (=> 1) date field to determine the month. What do you use?
Your table structure has several and is probably to be corrected: Additional normalization step required?

Eberhard

 

[arnelgp]

the recordset is unused. should be removed.