Reset autonumer first of every month

[rudi7109]

Goodday I'm new to access I need ny autonumber field to reset every month eg 1701-001 next month 1708-001 (yymm- autonumber)

Thank you

[JoeM]

Then it really isn't an autonumber field. An autonumber field is auto-generated, so you don't have any control over its values.
Do you have something now? How exactly does it work?

It sounds like you have an ID field that you are generating, probably by VBA. So VBA is where you would set those values.
How and when those get set depends on your processes and how new data is entered into your system.

[rudi7109]

yes its a id field and I use the autonumber input. How must a approach this?

[JoeM]

I am guessing that you are using a Custom Format on the Autonumber field, as described here: http://www.tomsguide.com/faq/id-1873...cess-2013.html
Is that correct?
If so, what is the format you are using?

Note that if you are using a Custom format to display the month and year, those values really are not part of the value. It really is just a number like 1, 2, 3, etc with a Custom Format to show a fixed prefix that really is not part of the ID. As such, you cannot reset the number back to one, because it would conflict with the other number 1. And if you change the Custom Format, it would change it for ALL records. I don't think you can have different Custom Formats for different months for the different records. It is an "all or nothing" proposition.

Note that the sole purpose of an AutoNumber is to guarantee a unique value, but you really cannot control the value. You cannot reset it unless you first delete ALL the records in your table. So it should not be used for anything other than to guarantee you have some unique field. Try this test. Add some record. Then delete it. Then add another record. You will now have a "gap" in your ID numbers (i.e. 1708-001, 1708-003) that you cannot do anything about. You cannot "force" autonumber to re-use that number that you deleted.

If you want some meaningful ID number (like what you are describing, your cannot use AutoNumber). You will need to create and maintain your own ID field.

[JoeM]

Note: If you do not care about the order or the value, if you are tracking the yymm that a record is added or applied to in some field in a table, then you could use that in addition to the Autonumber to build a calculated ID field to show on queries, reports, forms, exports, etc.

However, if it is based on Autonumber, you cannot reset it, or get rid of the gaps. So your values may look something like:
1708-001
1708-003
1708-004
1709-005
1709-007
etc

But I think that is the best that you can do if you want to use Autonumber. Otherwise, you need to create and maintain your own ID, most likely using VBA to populate it at the time those records are added to your table.

[CJ_London]

Try this test. Add some record. Then delete it. Then add another record.

You don't even need to do that.

go to add a new record, note the autonumber value, then escape out of it. go to add a new record again and you will see you have a gap in your autonumbering,

To do what you want to do, you need to use what is called DMax+1. Have two fields, one for year/month and a transactionID field and set a multifield index on these two fields to not allow dulicates. Then set the transactionID using dmax like this in the form beforeupdate event

transactionID=DMax("TransactionID","myTable","Year Month=" & YearMonth)+1


You can then format the two fields for presentation purposes e.g. yearmonth & "- " & format(transactionID,"000")

I would still have a separate autonumber field as a primary key because this will make your table relationships easier to manage.

[Missinglinq]

Goodday I'm new to access I need ny autonumber field to reset every month eg 1701-001 next month 1708-001 (yymm- autonumber)

Not sure how

1708

would be the next month after

1701

(I assume that's a typo) but as has been suggested, you're talking about an Auto-Incremented Number, here, not an AutoNumber, over which the developer has no control...the Access Gnomes assign these numbers, and not always sequentially!

This code will do what you want:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
   Me.RecordID = Format(Date, "yymm-") & "001"
 End If
 If RecordsetClone.RecordCount <> 0 Then
   If Left(DMax("RecordID", "YourActualTableName"), 5) = Format(Date, "yymm-") Then
    Me.RecordID = Format(Date, "yymm-") & Format(DMax("val(Right([RecordID],3))", "YourActualTableName", "Left([RecordID],5) = '" & Format(Date, "yymm-") & "'") + 1, "000")
 Else
    Me.RecordID = Format(Date, "yymm-") & "001"
  End If
 End If
End If
End Sub

replacing RecordID with the actual name of your Field/Control, and YourActualTableName with just that...the actual name of the Table that holds the data.

This kind of hack simply has to be in the Form_BeforeUpdate event, if this is going to be used in a multi-user environment; otherwise two users could each start a Record and be assigned the same ID number. Placing it in the Form_BeforeUpdate event insures that the assignment takes place in the last nanosecond before the Record is saved. I've had this kind of code running, in this event, in apps for well over a decade and I've never had a duplicate number assigned. The only drawback, with this, is the same thing; the number is assigned as the Record is saved, usually when moving to another Record, closing the Form or closing Access, itself, meaning that you won't see it, on a Single View Form, until you return to the Record.

As Ajax suggested, you should still have a separate AutoNumber field as a Primary Key.

Linq ;0)>