Incrementing a text field

[Ultimateneo]

Hello I have at work a sharepoint list with an ID field.

The next field is a Serial Number field that is stored as text even though it is a number field.

I cant change the dat type fo the field as i didnt create the list

I want to open the form and it is at new record for the ID field but gives me the next available serial number from the table.



So it finds out the last serial number and then adds 1 to it. The share point list is accessed by many people so i need to make sure that no one else has taken that serial number before I save it, if it has gone to add 1 to that so it is the next serial number that hasn't been used before.

Serial number example is 172345

Any help would be appreciated, thank you for your time

[Micron]

You have a slightly difficult problem. IF you generate the number and a split second later someone else commits before you, you will either duplicate the number if the design permits or you will raise an error if not. The solution to that would be to create the number as the last thing in the operation but often a user needs that number at the start. The only solution to when the number is needed is to create it first and foremost, then it is locked in. That would allow you to continue entering data or park it for later. However, that number can never be released if the process is canceled. It can only be archived or otherwise flagged (e.g. Rejected or Canceled).

Either way, you could use DMax function on the field as long as it will never contain alpha. There should be a means whereby that cannot happen.

[Ultimateneo]

Hello I have so far

Me.Serial_No_.Value = DMax("Serial_No_", "Serial Numbers") + 1

Sadly they have a default text in the field Serial_No_, so i have to get the previous value and add 1 to it

Is that possible ?

Thank you

[Micron]

Not sure I understand what you're reporting, or what the issue might be. DMax is a domain aggregate function (it operates on the domain, be that table or query). If the field or the form control has a value for the default value property, that should have no effect on the function because New Records are not actually in the table. They are only indications of what the new record will be when it is saved, and that depends on what other fields might be required. It can only return what is in the table, and what you see in the table view for a new record is of no consequence. By chance are you saying that if your maximum value is 172345, your next uncommitted (new) record in the table is showing 172346?

[CJ_London]

you say the numbers are stored as text, in which case providing all the possible numbers have the same number of digits then dmax should work, but then you will need to convert it to a number to add 1 then convert back to text.

If on the other hand you don't have the same number of digits then dmax will not work unless you convert it to a number first

in text 2 is greater than 12

suggest try using the clng function which converts text to a long number which should be fine if your maximum number of digits is 6

DMax("clng(Serial_No_)", "Serial Numbers") + 1