Increment number automatically

[Henley12]

What is the easiest way, besides an autonumber field, to automatically increment a number when you create a new record?

 

[khawar]

You can use dmax

 

[GaryPanic]

have a go at this
Nextnum is a field in my table OrgansierPolicynumber table

basically it looks at the last number adds one and records that in Me.Policyno on my form/table
then updates the table
i have this on create new record so
"DoCmd.GoToRecord , , acNewRec" makes a ew record and gives it the number I want rather than auto number --(still keep autonumber but don't use it for anything ) so that on the very rare occasion that 2 people press the get new record button at the same time you can still sepearate them out

I have used this over 10,000 times in a different set up over 5 years and i think we managed to get 5-6 dups


Private Sub Command9_Click()
On Error GoTo Err_Command9_Click


DoCmd.GoToRecord , , acNewRec

'OrganiserPolicynumber
Dim NextNo As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT NextNum FROM OrganiserPolicynumber")
NextNo = rs!nextnum + 1
'now update the table
rs.Edit
rs!nextnum = NextNo
rs.Update
rs.Close
Set rs = Nothing

Me.PolicyNo = NextNo

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

 

[Henley12]

How do I use dmax?

 

[khawar]

You can also check my sample Here

 

[missinglinq]

This shows DMax() used to increment an ID field. The first example is the syntax for an ID filed defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = "1"
Else
Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
End If
End If
End Sub

Here's the same code for an IDNumber defined as Numerical:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1
Else
Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
End If
End If
End Sub

 

[RexesOperator]

Here's the same code for an IDNumber defined as Numerical:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1
Else
Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
End If
End If
End Sub

I have a number that I need to increment with each new record.

I've been plowing around on this topic for some time and came across the above code.

I have a manual kludge where a popup form displays the last number used so the user can see what number to use.

This code is the closest to what I want (and I might add the simplest). However the current number starts at 12192. Does this code start the count at 1 and then increment? Or does it start with the last number used (presumably DMax)?

 

[Mike375]

How do I use dmax?

This what I use

=DMax("[NameNumber]","MainTable")

Thusd a new record gets that number +1

 

[missinglinq]

If this is the first record

If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1

assigns the number 1 to the record.

If this is any record other than the first one

Else
Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1

finds out what the highest number in use is and adds 1 to it.

In other words, if your last record has a number of 12192, entered in whatever manner you've used until now, the above code will assign the next record the number 12193.

Was that what you were asking?

 

[RexesOperator]

YES.

I think what confused me was If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1. Does this refer to the first record in the Recordset? How would that be determined - since recordsets aren't ordered?

I ran the code based on a query and it works exactly as I need it to.

 

[missinglinq]

Couple of questions, Mike375.

How /where are you using this?

=DMax("[NameNumber]","MainTable")

will return Null if there are no records in the table.

Because of this, if you use it when adding the first record, it'll error out with "Invalid use of Null" because you're trying to add one to a Null value.

And why are you answering Henley12's question, that was posted 4 months ago, when the question was answered in detail, the nest day?

 

[RexesOperator]

Mike's reply to Henley12 might have been my fault for reviving an old thread. I found the thread using search and just needed clarification before dropping the code into my form.

My apologies for not making it clearer.

 

[Mike375]

Couple of questions, Mike375.

How /where are you using this?

=DMax("[NameNumber]","MainTable")

will return Null if there are no records in the table.

Because of this, if you use it when adding the first record, it'll error out with "Invalid use of Null" because you're trying to add one to a Null value.

There are two SetValue action lines each with a condition.

And why are you answering Henley12's question, that was posted 4 months ago, when the question was answered in detail, the nest day?

Because I did not make an in depth detailed analysis of the thread.

 

[missinglinq]

Understood your need for clarification, RexesOperator!

Fair enough, Mike!

Y'all have a great weekend!