An autonumber field that resets itself....?

[DKO]

Is there any way to force an autonumber field to reset itself (go back to "1") on the first day of each month?

***

Or...can anyone recommend an easier way to do the following:

I'm trying to automate a control number comprised of 4 different parts:

first part = 2 letters representing a type of action
2nd part = year and month (yymm)
3rd part = autonumber (this is what the autonumber field will be used for)
4th part = initials of the employee who started the action.

I'm trying to automate the form so that the control number is automatically populated when the employee chooses an action type (from a drop down) and enters their initials. The only problem is part 3.

Any ideas?



edit: On second thought - I don't have any other fields to use as a primary key, so the autonumber field won't even help. So, I'm open to suggestions!

 

[DCrake]

Simple Software Solutions

How will you know what the autonumber field value is before you add a new record? Access only assigns the new autonumber once data is entered into the new record.

 

[Rabbie]

You can't use an auto number field to do exactly what you want. The only guarantee with autonumber fields is that they will be unique.

To get what you want you will need to use some VBA code to reset the number part on the first of the month. It will take a bit of coding. Is the sequence number dependent on the employee or to that months data. ie does it go
0108060001ab
0108060002ab
0108060003cd

or

0108060001ab
0108060002ab
0108060001cd

 

[DKO]

You can't use an auto number field to do exactly what you want. The only guarantee with autonumber fields is that they will be unique.

To get what you want you will need to use some VBA code to reset the number part on the first of the month. It will take a bit of coding. Is the sequence number dependent on the employee or to that months data. ie does it go
0108060001ab
0108060002ab
0108060003cd

or

0108060001ab
0108060002ab
0108060001cd

It's sequential, according to the month - it's not dependent on the action type or the employee. ie, the first action of this month would be RT0806001DO, the second would be CT0806002DO, etc. Next month would be RT0807001DO...

I was trying to do this by SQL (in VBA) on the AfterUpdate event of a combo box on the form.

How would I code that?

edit: Here's the SQL I have so far for the first two parts...

strSQL = "UPDATE ACTION_TYPE INNER JOIN [ACTIONS_MASTER] ON [ACTION_TYPE].[LONG] = [ACTIONS_MASTER].[ACTION_TYPE] SET [ACTIONS_MASTER].[CONTROL_NR] = [ACTION_TYPE].[SHORT]+Format(Date(), 'yymm') WHERE KEY = Forms!ACTIONS_INPUT_FORM.KEY"

How will you know what the autonumber field value is before you add a new record? Access only assigns the new autonumber once data is entered into the new record.

Just so you don't think I ignored you - the control number won't be automatic. At the very least, they'll have to select an action type before the control number is populated - in which case, all you have to do is save the record between the time the field is updated and the time the SQL is run - which will be a part of the VBA code for the event procedure.

 

[neileg]

I would:
1) Assign an autonumber as a primary key, but not attach any meaning to it.
2) Hold the component parts of the control number as separate fields and concatenate them for display purposes. Sooner or later you will want to extract records based on one or more of the component parts.
3) Search in these forums for ideas on creating your own sequential number.

 

[DCrake]

Simple Software Solutions

Maybe it's me, but I always resist using autonumbers as primary keys. With very good reason. This dates back to my time using dBase II (this gives my age away). The way it worked was exactly the same as it works with Access, then one day the table got corrupted and it held mission critical data. The outcome was that we needed to rebuild the table. In doing so new Auto Numbers were assigned to the Auto Number field, which suprisingly enough did not corrolate to the original PK. The child tables did have the correct PK as a FK but as this was the only reference back the parent table we lost all referential integrity.

I tend to use Auto Number fields purely for locating holes in the table (missing numbers) or data collection timeline indexes.

CodeMaster:

 

[DKO]

I would:
1) Assign an autonumber as a primary key, but not attach any meaning to it.
2) Hold the component parts of the control number as separate fields and concatenate them for display purposes. Sooner or later you will want to extract records based on one or more of the component parts.
3) Search in these forums for ideas on creating your own sequential number.

That's pretty much what I'm doing. I already have an autonumber as the primary key and am concatenating 3 separate fields plus the date as the control number - but I want to store the complete control number as a separate field.

If I can get this all to work, I'll make the control number my primary key.

The only thing I need now is a field with a sequential number that resets itself on the first day of every month. I'll do some more searching and see what I can come up with. I know it's not going to be a simple solution. I don't even know if it's possible right now. The sequential part isn't really the problem - getting it to reset every month is the problem.

 

[neileg]

That's pretty much what I'm doing. I already have an autonumber as the primary key and am concatenating 3 separate fields plus the date as the control number - but I want to store the complete control number as a separate field.

Why? You're just duplicating data.

If I can get this all to work, I'll make the control number my primary key.

Why?

The only thing I need now is a field with a sequential number that resets itself on the first day of every month. I'll do some more searching and see what I can come up with. I know it's not going to be a simple solution. I don't even know if it's possible right now. The sequential part isn't really the problem - getting it to reset every month is the problem.

Been covered before in these forums. Not a big issue at all.

 

[DKO]

Been covered before in these forums. Not a big issue at all.

If anyone can point me towards one of those threads, I'd appreciate it. The search function isn't working for me right now.

 

[neileg]

Not working for me either!

 

[DKO]

OK. This is what I've come up with using the Nz and DMax functions:

Me.CTL_NR_MONTH = DatePart("m", Date())
Me.CTL_NR_SQNC = Nz(DMax("[CTL_NR_SQNC]", "ACTIONS_MASTER", [CTL_NR_MONTH] = Me.CTL_NR_MONTH), 0) + 1

- but how do I compare the data from a field in the current record to the data from the same field in the previous record? And would the sorting of the form/table have an impact on this?

ie - instead of "Me.CTL_NR_MONTH", how do I specify that field from the previous record in the table? Do I have to use another field to identify the previous field - like a date/time stamp field or an autonumber field?

I suppose if I store the Control number in the format "yymm000", I could use the Dmax of that field to identify the most recent entry, regardless of the table's sorting....

 

[neileg]

There is no order in a table. What you see on screen doesn't neccesarily correspond with what is actually in the table. As you suggest you will need to use a datestamp or a number feild.

 

[DKO]

OK, I think I got it now.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strSQL, varX As String

varX = Nz(DLookup("[MONTH]", "CTL_NR_HOLD"), Format(Date, "yymm"))

strSQL = "SELECT [ACTIONS_MASTER].[CTL_NR_MONTH] AS MONTH INTO [CTL_NR_HOLD] FROM [ACTIONS_MASTER]
WHERE ([ACTIONS_MASTER].[KEY] = DMax('[KEY]', 'ACTIONS_MASTER'))"

DoCmd.SetWarnings (Warnings0ff)
DoCmd.RunSQL (strSQL)
Me.CTL_NR_MONTH = Format(Date, "yymm")
Me.CTL_NR_SQNC = Nz(DMax("[CTL_NR_SQNC]", "ACTIONS_MASTER", [CTL_NR_MONTH] = varX), 0) + 1
DoCmd.SetWarnings (WarningsOn)

End Sub

Thanks for the help guys.