Increase the value of the next record from last record +1 on click

[George82]

I have a text box on a form that I need for every new entry to be automatically increased by 1 on click event.
My first value would be 520000001 and when the user makes a new entry and clicks on the text box I want automatically to take the value 520000002 and for the next record the value 520000003 and so on…
If anyone could help would be much appreciated!

[FONT=&quot]Kind regards[/FONT]

 

[George82]

I have a text box on a form that I need for every new entry to be automatically increased by 1 on click event.
My first value would be 520000001 and when the user makes a new entry and clicks on the text box I want automatically to take the value 520000002 and for the next record the value 520000003 and so on…
If anyone could help would be much appreciated!

[FONT=&quot]Kind regards[/FONT]

Or I could use it in Before Update event....

 

[RainLover]

Suggest you use the Function DMax.

DMax will find the highest Value then you simply add 1 to that value.

Access Help explains the use of DMax.

 

[missinglinq]

If the Target Field is Text:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
  Me.TargetField = "520000001"
 Else
  Me.TargetField = DMax("Val([TargetField])", "TargetTable") + 1
 End If
End If

End Sub

If the Target Field is a Number:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
   Me.TargetField = 520000001
 Else
   Me.TargetField = DMax("[TargetField]", "TargetTable") + 1
 End If
End If

End Sub

Linq ;0)>

 

[George82]

Thank you so very much for your reply!
Anfortunately I am ferly new in access and I will need some additional help.

The is no Before Update event in form…
So the “Private Sub Form_BeforeUpdate” should it be for the Target filed I am using
The name of the field I am referring is MID and is a numeric value.
Shouldn’t this be : “Private Field MID_BeforeUpdate….”
Also the name of the target table is STEP so:

The code with the parameters is:

[FONT=&quot]Private Sub MID_BeforeUpdate(Cancel As Integer)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]If Me.NewRecord Then[/FONT]
[FONT=&quot] If RecordsetClone.RecordCount = 0 Then[/FONT]
[FONT=&quot] Me.MID = 520000001[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] Me.MID = DMax("[MID]", "STEP") + 1[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot]End If[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]End Sub[/FONT]


That does not work and I am getting debug errors.
Cloud you please help.
Again thanks in advance!

 

[George82]

Thank you so very much for your reply!
Anfortunately I am ferly new in access and I will need some additional help.

The is no Before Update event in form…
So the “Private Sub Form_BeforeUpdate” should it be for the Target filed I am using
The name of the field I am referring is MID and is a numeric value.
Shouldn’t this be : “Private Field MID_BeforeUpdate….”
Also the name of the target table is STEP so:

The code with the parameters is:

[FONT=&quot]Private Sub MID_BeforeUpdate(Cancel As Integer)[/FONT]

[FONT=&quot]If Me.NewRecord Then[/FONT]
[FONT=&quot] If RecordsetClone.RecordCount = 0 Then[/FONT]
[FONT=&quot] Me.MID = 520000001[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] Me.MID = DMax("[MID]", "STEP") + 1[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot]End If[/FONT]

[FONT=&quot]End Sub[/FONT]


That does not work and I am getting debug errors.
Cloud you please help.
Again thanks in advance!

It worked fine!!!! THANKS AGAIN!!!