Ad sequence number to new record in MS Access

You could have a table that holds Years and Last Record No
For example
Year     LastRecordNo
2019            5137
2020            7319
2021             1238
So when the form opens a simple Dlookup would do the job to ge the LastRecordNo , increment it by 1 .assign it to the control and update the table with the Year/LastRecordNo

John  the year can be created automatically as a default value as it is right now. I just need to carry forward the last record, and also this is not an impossible task that I could figure out. My issue is that each year the record should start from 0001. If I create a table to comply with this it will complicate a lot. I was aiming and hoping for a lighter solution.

First off its a good idea to seperate function from presentation.
If the PC.CPR is always there, its a FORMAT issue.

It's also part of best practice for database design to only have 1 information in 1 field. When you combine the ID and the year into a single field, you are really having 2 pieces of information there in a single field.

To sum up, I would have a year column in the table, as well as an sequence nr, e.g.named seq_no

Whenever we add a new record, we can then simply to a DMAX on the data
You can then use the before_update event to store the seq_no for the record

If Me.NewRecord then
   seq_no=Nz(dmax("seq_no","myTable","Year=" &year(Date()))"),0)+1
end if

Select allOpen in new window

The Nz is there to handle the special case of the first record of the year.

Then you can have either your query or your textbox on the form combine the information. The controlsource for the textbox could be:
="PCR.Nr" & [Year] & "." & [seq_no]

Anders Ebro
="PCR.Nr" & [Year] & "." & [seq_no]

is exactly what I wish to implement and I am not sure but I think with an IF we could somehow solve the sequence issue starting from zero when we enter in the new year?

On the beginning of each year either automatically or manually you could check the current Year and insert a new record...e.g. first time open of application on new year
2019            5137
2020            7319
so 2021
insert new record
2021              0

John
yes I could indeed. Checking if we can find a way for this to happen automatically.

the easiest "automatic" way is to have on the Open event of your very first form (or a macro) to check the year of the current date and if you lack a matching entry on the underlying table that holds yers/records to insert it..

John
I think with an IF we can bypass the macro to check the year. But I also could be wrong. Let's see if anyone has a solution, otherwise I will simply edit the 0001 every first record of the year manually.

You need the format as well:

="PC.CPR." & [Year] & Format([seq_no], "\.0000")

Select allOpen in new window

Gustav
What is the [seq_no].
I have given at the end of the question all the control, form and table names.
I am a little confused on how to implement it practically. Sorry. And also how to solve the issue of the new year which should make the sequence start from 0001?

1. Agree with Anders that you should use separate fields for PCRYear and SeqNum.
2. Alot depends on when you want to see this number displayed, and what you want to happen if the user decides to cancel creation of this new record.  
     a. If you don't care about gaps in your SeqNum you can fill this value in as soon as the record is created (Form_Current event).  
     b.  But if you don't want gaps, you should not fill it in until the record is written (Form_BeforeUpdate event).
3.  If you use option 2a, I strongly advise against the method of using DMAX to retrieve the current maximum SeqNum from your existing table, as that provides the opportunity for a second user to create a record with the same number, before the first record is saved.
4.  Because of this, I use the approach of storing this info in a table (tbl_NextNum) that looks something like:
ID  (auto)
ItemName (text) - used to describe which record you want to extract
ItemYear (int) - used to store the year
Next_SetNum (long)
and an example of the data in that:
ID    ItemName        ItemYear       Next_SeqNum
1      PCR_Seq          2021             23
5.  To extract this data, I would use a function:

Public Function GetNextSeqNum(ItemName as string) as long
    Dim strCriteria as string
    Dim rs as dao.recordset
    strSQL = "SELECT * FROM tbl_NextNum " _
           & "WHERE ([ItemName] = '" & ItemName & ')" AND " _
                 & "([ItemYear] = " & Year(Date()) & ")"
    set rs = currentdb.openrecordset(strsql, dbopendynaset)
    if rs.eof then
        rs.addnew
        rs.ItemName = ItemName
        rs.ItemYear = Year(Date)    
    else
        .edit
    endif
    GetNextSeqNum = NZ(rs!Next_SeqNum, 1)
    rs!Next_SeqNum = rs!Next_SeqNum + 1
    rs.update
    rs.close
    set rs = nothing
End Function

Select allOpen in new window

And to get this value you simply need to run
me.txt_SeqNum = GetNextSeqNum("PCR_Seq")

Hope this helps.
Dale

From the code from Anders.

As this kind of identifier is an external one, while also being a candidate key, it should not be the primary key.
Thus using a data macro or code-behind to calculate it after saving is the way to go.

The only open question is how to handle deletions, when they are possible. Cause in this case a naïve approach would allow two identical identifiers for different payload. But this depends on the process, which requires explanation.

My general approach is here to extract the keys to their own table, where you never delete rows, but carry the state in a separate column. Then this table is the main, key providing table for all other tables (foreign key relationships).

Dale Fye

I am obviously doing something wrong.

I have created two more fields as follows:
Considering, what should I do now?

You must Dim strSQL, and year should be number, not text.

Anders Ebro 
As per my comment above I have created two new tables, one for the year and another for the CPR sequence number.
As per Gustav suggestion I have also made year number instead of text.

Elaborating further:

If Me.NewRecord then
   seq_no=Nz(dmax("cpr_nr","myTable","Year=" &year(Date()))"),0)+1
end if

Select allOpen in new window

What else am I missing?

You have some typos:

seq_no = Nz(DMax("cpr_nr","myTable","Year=" & Year(Date())), 0) + 1

Select allOpen in new window

Gustav Brock 
Should I insert this on AfterUpdate? Current?

As is, it would be OnCurrent.

like I said, I strongly recommend against using the Current event go get this number using DMAX unless your system will only ever be used by one user at a time.  
If your application will be used by multiple, simultaneous users then the GetNextSeqNum method is far better.
And if you don't care about gaps, you can use the Current event, test for new record and call the function.
But if you are concerned about lots of gaps in your sequence numbers, I would not fill this field until the Form_BeforeUpdate event.

Gustav is correct, I failed to dimension strSQL

Public Function GetNextSeqNum(ItemName as string) as long
     Dim strSQL as string     
     Dim rs as dao.recordset
     strSQL = "SELECT * FROM tbl_NextNum " _
            & "WHERE ([ItemName] = '" & ItemName & ')" AND " _
                  & "([ItemYear] = " & Year(Date()) & ")"
     set rs = currentdb.openrecordset(strsql, dbopendynaset)
     if rs.eof then
         rs.addnew
         rs.ItemName = ItemName
         rs.ItemYear = Year(Date)
     else
         .edit
     endif
     GetNextSeqNum = NZ(rs!Next_SeqNum, 1)
     rs!Next_SeqNum = rs!Next_SeqNum + 1
     rs.update
     rs.close
     set rs = nothing 
End Function

Select allOpen in new window

Next time, don't take a screen shot of your code, copy it, paste it into the EE text window and then use the  code snippet button on the formatting bar (between the quotes and image controls) to format it as above.

If your application will be used by multiple, simultaneous users then the GetNextSeqNum method is far better.

As no one mentioned to obvious:

- You need a unique constraint (index) for this column/these columns in your table.
- You need pessimistic locking for using a sequence generator table. Otherwise it won't guarantee unique numbers in certain scenarios.

ste5an 
No, the application is used by a single user at a time.

Dale Fye 
I am lost. Mind you that I am an amateur and not an advanced user.
I copy the code you have suggested in your last answer somewhere in the module of the Form. Right?
Then...

strSQL = "SELECT * FROM tbl_NextNum " _
            & "WHERE ([ItemName] = '" & ItemName & ')" AND " _
                  & "([ItemYear] = " & Year(Date()) & ")"

Select allOpen in new window

I have created the two fields on the main table, one is CPR_nr and another is CPR_year. So would change this part of the code as following:

strSQL = "SELECT * FROM tbl_NextNum " _
            & "WHERE ([CPR_nr] = '" & CPR_nr& ')" AND " _
                  & "([CPR_year] = " & Year(Date()) & ")"

Select allOpen in new window

Am I right so far?
Then I should call the function GetNextSeqNum. Where? How?

Nicola,

The [ItemName] and [ItemYear] in my function relate to those fields in my table used for tracking these numbers (tbl_NextNum).

Then all you have to do is call the function, you could call it from the immediate window if you wanted.  The neat thing about this technique is that you can pass it any value for the  ItemName argument of the function so you can test this with one value and then in your form, use the appropriate value.  The other nice thing is that you don't have to worry about the year, it automatically handles that, and if the year changes, it will create a new record for the ItemName and ItemYear values.

?getNextSeqNum("TestItem")

Then take a look at your table.  Then do it again.

Then, in your form, you can use the Form_Current event you do something like:

Private Sub Form_Current()
    if me.newrecord then 
        me.txt_CPRNum = getNextSeqNum("CPRNum")
        me.txt_CPRYear = Year(Date)
   endif
end sub

Select allOpen in new window

Dale Fye
I have attempted to paste the function but it's going wrong as you can see:

WHERE ([CPR_nr] = '" & CPR_nr & "') AND " _

Select allOpen in new window

Dale Fye 
Here's what the error I get:

that line is supposed to set the value of the control on your form with displays the number, so in that case, replace:

me.CPR_nr

Select allOpen in new window

with a reference to the control, it might be:

me.txt_CPR_nr

Select allOpen in new window

or something else.

BTW, if you are going to use the GetNextSeqNum function for other tables as well, then you will need to move that code from behind the form, to a code module all.

I don't have any control or field named txt_CPR_nr
As advised I have created a new table field called CPR_nr 

Still same issue:

Use the full syntax:

Me!txt_CPR_nr.Value = 

Select allOpen in new window

Gustav Brock 
Why txt_CPR_nr? I don't have any field named like this.

Well, then use the name you have. And it is not a field but the textbox.

Gustav Brock 
I did use the name I have.
I have created two new data on the table:

OK. But "Me" is the form, not the table.

I know Me is the form and there was no doubt on that.
Still what comes after for some reason doesn't work.

Then double-check the names of your textboxes and use the posted full syntax.

Nicola,

You have been mixing apples and oranges.  Back at the beginning of this discussion, several of us recommended that instead of having a single field in your table, which contains a combination of information, you should have separate fields for CPR_Year and CPR_SeqNum.  But it sounds like you created those fields in an entirely new table, this is not what we recommended.

The code I gave you is designed to fill in the NextSeqNum into a field the is in your forms recordset.  Normally, this field would be displayed in a control (I would name it txt_CPR_SeqNum or something like that).  However, if you don't have a separate control on your form, but have included that field in your forms RecordSource, then you can fill in the value of the field with code similar to:

me.CPR_nr = GetNextSeqNum("CPR_nr")

Select allOpen in new window

if you still have a control where you want to display the combined values of year and seqnr,  Then as soon as you set the value of me.cpr_nr, that control should concatenate the new values of Cpr_Year and CPR_nr to give you your desired display value.

Dale
I have followed the advice and created two new fields on the same table, not a new one.
I'm now out of station and will sook work on it again and give it a try.

I think I am losing hope and would be a first time in MS Access that I am not able to find a solution.

The message is pretty clear. That field isn't in your recordset.
Copy the content of strSQL and paste it into a new query in SQL view to debug.

Gustav Brock
I have stressed more than once that I am not a programmer. I enjoy using access to help in my duties. I am not familiar with all the terminology and therefore my capability to understand is very basic and therefore also the logic to understand. It is clear that what I am trying to implement is more complex than what I can handle and unlike other answers (one of which you have contributed to solve in a separate question) in this case I have consumed plenty of everyone's time and I am still looking a foul while everyone knows what's going on except me and this is beyond frustration. As I said I think I will give up on this one and will continue to number the new records manually.

OK. Insert this line line after strSQL has been filled:

Debug.Print strSQL

Select allOpen in new window

This will put the string in the Immediate window for you to copy.

Where?

Up one line, ideally, though it should be fine as is.

And what was printed in the Immediate window?

Copy that into a new query in SQL view to debug.

I have pasted the screenshot exactly how it appears.

That's the code module. Press Ctrl+G to view the Immediate window.

It's empty

So the code won't compile?

You may have to out-comment the offending lines:

' rs.cpr_nr = cpr_nr
' rs.cpr_year = cpr_year

Select allOpen in new window

I have created a table named tbl_NextNum and two colums as instructed.
But here's what happens:

It should be:

rs.Edit

Select allOpen in new window

I am thinking of creating a database by having the essential tables and modules and upload it here. I can't see the light otherwise.

you need a third column in that new table with a name [Next_SeqNum]

You are getting close, stick with it.

I am not getting errors anymore.
In the main Table this is what happens:

The year is automatically filled. Also the cpr_nr but as you can see at a certain point I have numbered it manually 0111 then it has started from 0007 while it should have continued 0112. Then if I open the table and delete the last records, it won't start again from 0005 but from the last one it had created, so from 0008. Mind you that I have pre-existing records for the last 6 years. So the sequence of numbering is very important.

Secondly, it needs to populate the CPR control as per the requirement described initially i.e. PC.CPR.2021.0223 and then on the next record would be PC.CPR.2021.0224.

Sorry ok, found the GAP. I need to set the last number manually on the tbl_NextNum table.
Presumably this is solved. Now I need to populate automatically CPR column i.e. PC.CPR.2021.0223 whereas PC.CPR. is fixed and 2021 + 0223 will be variable.

Here is how I would do that.  I don't know whether you are simply using the table, or a query for the recordsource of your form.  I would use a query which could be as simple as:

SELECT T.*, "PC.CPR." & Format(T.cpr_Year, "yyyy") & "." & Format(T.crp_nr, "0000") as [
FROM yourTable as T

Select allOpen in new window

This should ensure that your textbox associated with the CPR # label displays the format that you want.  And it should automatically update when you do:

me.CPR_year = Year(Date())
me.CPR_nr = GetNextSeqNum("CPR_nr") 

Select allOpen in new window

Dale Fye 
The forms source is a table.
How and where would I place the query?

Found a simple way:

Private Sub CPR_DblClick(Cancel As Integer)

    Me.CPR = "PC.CPR." & cpr_year & "." & cpr_nr

End Sub

Select allOpen in new window

Now there's one last issue I need to resolve. But compared to everything else it's minor. I open the form and it creates the next cpr_nr which is let's say 0123. If I need to delete the record, the next would be again 0123 but instead it gives 0124. Any solution for this?

Any solution you create which applies this number before you save the record, especially if you want to start filling in the record and then cancel the new record.  Which is why, in my original response to you was wait until just before you save the record to create this number.  This way, if you hit [ESC] while editing the record, you have not already retrieved the CPR_Nr and incremented the NextSeqNum in the table.  To do this, I use the Form_BeforeUpdate event to check to make sure all required fields have been entered.  If not, I set the Cancel argument to True; if so, then and only then do I generate the value for the CPR_Nr.

Dale Fye the issue is not the number itself because I could update it with a double click or a button. The issue is that the record is created. Form closed. Then later we need to delete that record for any reason. In that case the record remains and the progressive will continue as nothing has happened. However I can also deal with this so for me the issue is closed. 

Nicola,  glad to be of assistance.

It is generally never a good idea to reuse these types of numbers.  If you need to keep track of all of the numbers, why don't you just mark that record as deleted, but keep it in the system.  Ignore it in queries unless you want to display it in a report.