Auto Incrementing a field without using Autonumber

[MsLady]

Hi Rural Guy.

Thanks. I swayed from that approach since i wasn't getting anywhere.

I created a field in my table [jobIdSequence] and use this bound control on my form with controlsource (=Nz(DMax("[jobIdSequence]","tblJobDetails",Format([timeIn],'mmyy')=Format[timeIn],'mmyy')),0)+1)

I have a date field [timeIn].

Then my jobId (which is what i need to have the format of mmyy-0001 and restart at each month), i set the control source as (=Format([timeIn],"mmyy") & Format([jobIdSequence],"0000"))

1. The numbers don't restart at teh beginning of the month.
2. secondly, what do you think of this approach

 

[RuralGuy]

Hi M'Lady,
I'm not a big fan of values that combine several elements but if I were to do it I would probaby keep the elements in separate fields and only put them together when I needed them, like in reports or on forms. As I said earlier, if your system will ever be multi-user then you have left yourself a big window for duplicates that you will have to deal with.

 

[MsLady]

But in this case, i need it to be the JobId tho i have an autonumber primarykey, but thre's a search field in my application, where the users would like to type in jobIDs e.g. 08060001 (mmyy0001) to search for that ID.
I was using the autonumber for the search field but the nubmers are going to grow really fast.
I have connected this jobID to many forms and queries already, so im just tyring to build this new customID around it, so the jobID is always going to be in the format of mmyy0000.

Any ideas?


Anybody?

 

[RuralGuy]

One method for restarting the numbering every month would be to have another table with the current Month and Number in it. If you have no record for the current month then create a new record and start the numbering over again.

 

[MsLady]

One method for restarting the numbering every month would be to have another table with the current Month and Number in it. If you have no record for the current month then create a new record and start the numbering over again.

Thanks RG. I don't know how to handle that u suggested, so here's what i found. It seems to work most people, but not me...
I've posted it here too

http://access-programmers.co.uk/forums/showthread.php?t=31046&p=519211

You can please take a look also

 

[MsLady]

If you are multi-user then it *needs* to be in the BeforeUpdate event. There is a very small window between the event and the actual write where two users could get the same number. As I said earlier, there can be a *huge* window between the BeforeInsert event and the actual update of the record. Do yourself a favor and put it in both events and fix the BeforeUpdate event to only work on NewRecords.

I have some questions about the before insert/before update short lesson u have here

Do i need to put "If Me.NewRecord Then" in the before insert too?

 

[RuralGuy]

Hi M'Lady,
The BeforeInsert *only* occurs on NewRecords, but it occurs the 1st keystroke into the 1st control. Using that event populates your control for the next number so the users can see something but it could be a long time before the record is actually saved. There is a very tiny window between BeforeUpdate (which occurs *every* time a record is saved - hense the need to limit your code to NewRecords only) and the actual write to disk. In a multi-user environment, it is possible the actual number used in the update and the number displayed in the control will be different but unless the user is writing down the number somewhere, they will probably never notice that anomaly.

 

[MsLady]

Hi M'Lady,
The BeforeInsert *only* occurs on NewRecords, but it occurs the 1st keystroke into the 1st control. Using that event populates your control for the next number so the users can see something but it could be a long time before the record is actually saved. There is a very tiny window between BeforeUpdate (which occurs *every* time a record is saved - hense the need to limit your code to NewRecords only) and the actual write to disk. In a multi-user environment, it is possible the actual number used in the update and the number displayed in the control will be different but unless the user is writing down the number somewhere, they will probably never notice that anomaly.

I see.
Thank you, your kindness warms my heart