Increment number automatically
- Thread starter Henley12
- Start date
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
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
missinglinq
AWF VIP
- Local time
- Today, 05:33
- Joined
- Jun 20, 2003
- Messages
- 6,420
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
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
Registered User.
- Local time
- Today, 05:33
- Joined
- Jul 15, 2006
- Messages
- 604
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)?
This what I use
=DMax("[NameNumber]","MainTable")
Thusd a new record gets that number +1
missinglinq
AWF VIP
- Local time
- Today, 05:33
- Joined
- Jun 20, 2003
- Messages
- 6,420
If this is the first record
assigns the number 1 to the record.
If this is any record other than the first one
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?
Code:
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1assigns the number 1 to the record.
If this is any record other than the first one
Code:
Else
Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1finds 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
Registered User.
- Local time
- Today, 05:33
- Joined
- Jul 15, 2006
- Messages
- 604
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.
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
AWF VIP
- Local time
- Today, 05:33
- Joined
- Jun 20, 2003
- Messages
- 6,420
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?
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
Registered User.
- Local time
- Today, 05:33
- Joined
- Jul 15, 2006
- Messages
- 604
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.
My apologies for not making it clearer.
There are two SetValue action lines each with a condition.
Because I did not make an in depth detailed analysis of the thread.
missinglinq
AWF VIP
- Local time
- Today, 05:33
- Joined
- Jun 20, 2003
- Messages
- 6,420
Understood your need for clarification, RexesOperator!
Fair enough, Mike!
Y'all have a great weekend!
Fair enough, Mike!
Y'all have a great weekend!
