Solved Increment a table field by reading the value in the last field (1 Viewer)

kengooch

Member
Local time
Today, 01:40
Joined
Feb 29, 2012
Messages
137
I have a database that has a separate index ID so that each Accession has a unique number.

I need to track the Accession numbers by the Prefix "S" and then the last two digits of the year ie 2019 would be "S19".

So the system tracks Accession numbers by the Year "S19" and then in a numeric sequence from 0001 to 9999. It is mandatory that the leading zeros appear. So the number would be S19-0001, S19-0002 etc.

I created a field called tAccYr and set the default value as
"S" & Right(Year(Date()),2)

I then created a field Called tAccNum that needs to begin with 0001 and increment by one for each new Accessioned item.
When the year changes, in field tAccYr the field tAccNum needs to restart at 0001 again.

I was thinking that I could us DMax or DLast to get the previous number in the Default Value Field Property of the tAccNum field. But I can't seem to make it work, much less tell it how to restart at 0001 if tAccYr changes to the next year.

Would appreciate very much some help with this.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:40
Joined
Oct 29, 2018
Messages
21,357
Hi. The way I would do this is use two separate fields: one date and one number. I might call the date field, CreateDate and the number field Serial. The CreateDate might have a Default Value of Date() to store the current date when a new record is created. The number field, Serial, will then get populated using code as new records are created. Then, whenever/wherever I need to display this index ID as S190001, I would simply use a query and add a calculated column in it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:40
Joined
Feb 28, 2001
Messages
26,996
I was thinking that I could us DMax or DLast to get the previous number in the Default Value Field Property of the tAccNum field. But I can't seem to make it work, much less tell it how to restart at 0001 if tAccYr changes to the next year.

Using theDBguy's idea plus your stated desire to use domain aggregate functions, you would do something SIMILAR to this. First, store just the year in the CreateDate. Then

Code:
CurrentYear = DatePart( "yyyy", Now() )
If DCount( "*", "AccessionTable", "[CreateDate]=" & CurrentYear ) <> 0 Then
    NxtSerial = 1 + DMax( "[Serial]", "AccessionTable", "[CreateDate]=" & CurrentYear )
Else
    NxtSerial = 1
End If

In VBA it might resemble this.

An alternative is that you have STRICTLY MONOTONIC numbering (no gaps) then you can do

Code:
NxtSerial = 1 + DCount("*", "AccessionTable", "[CreateDate]=" & CurrentYear )

Of course this doesn't work correctly if you have any gaps in the numbering.
 

kengooch

Member
Local time
Today, 01:40
Joined
Feb 29, 2012
Messages
137
Wow! thanks so much for the Quick response!!! So where does the block of code go that you provided? It looks like VBA code rather than a single line of code that could be entered in the "Default Value" field of the tAccLog table under tAccNum?



I was trying to accomplish this in the table so that if someone tried to enter data through the table it would still correctly handle the record creation.



Again I appreciate so much your further help with this.
 

vba_php

Forum Troll
Local time
Today, 03:40
Joined
Oct 6, 2019
Messages
2,884
I think Richard might have lost you on this one, ken:
STRICTLY MONOTONIC
what does that mean!? :p Obviously that's a level of intelligence I'm not familiar with! Richard, see the attached image for a diagram given to me on facebook by someone who's as smart as you. This dude was dealing with low level issues as far down as the actual KERNEL. When I asked him why he likes doing the work, he just said he's become obsessed with it. and my response was:
you probably are addicted to low level because you have a serious amount of brain power and you want to use it to challenge yourself for fun. no?
and I got this in return:
ken, I think, again think only, Richard means to put this:
Code:
function NxtSerial()
 CurrentYear = DatePart( "yyyy", Now() )
If DCount( "*", "AccessionTable", "[CreateDate]=" & CurrentYear ) <> 0 Then
    NxtSerial = 1 + DMax( "[Serial]", "AccessionTable", "[CreateDate]=" & CurrentYear )
Else
    NxtSerial = 1
 End If
end function
in a VBA module, then in a query object's sql statement, write:
Code:
SELECT [common fields here], NxtSerial as CreateDate FROM table
but you should ask Richard if that's what he meant, ken.
 

Attachments

  • windows kernel intelligence.jpg
    windows kernel intelligence.jpg
    26.3 KB · Views: 100
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:40
Joined
Feb 28, 2001
Messages
26,996
Adam, "Strictly Monotonic" can be looked up in a dictionary if that is a term you didn't learn. However, to clarify:

If the numbers start at 1 and increase by 1 with no gaps, they are contiguous. Which is also monotonic, but in this case "contiguous" is an even stronger word.

I admit having posted that one while a bit drowsy and probably should have used contiguous. IF you have contiguous numbering then the DMax function and the DCount function would return the same candidate for the next number in sequence, which is why you could use the DCount directly. That way you wouldn't have to check for the count first and then compute the max. You could just take the count immediately.

As to where the code goes, it depends on how you use the form, but I wouldn't put that as a default value. If the form can be used not only to create but also to edit records in that table, I MIGHT put that little snippet in the Form_Current routine but have a test to see if that field already has something in it (in which case you are editing, not creating). If editing, do nothing. If creating, compute the next number.

NOTE that if you can ever change your mind, you will need to force an UNDO. It might also be better (to avoid a pesky little implied loop) to have an UNBOUND field to hold the tentative number and only store the number if you store the record. In which case you would have code in the Form_BeforeUpdate event to see if the number had been stored yet or whether you needed to store it in the bound version of that field.

In other words, if you want to keep contiguous numbering, you need to consider ways to avoid accidental assignment of an otherwise empty record.

If contiguous numbering is NOT required, then number generation is easier since you can just use the DMax+1 method.
 

vba_php

Forum Troll
Local time
Today, 03:40
Joined
Oct 6, 2019
Messages
2,884
always a pleasure, Richard. You're a smart cookie. :)
 

Users who are viewing this thread

Top Bottom