autonumber field in txt format

[xaysana]

Dear All,

In my asset Database, there is a variable called CIAT in Asia Code, this field has been formated as text. Currently, I am changing it so that everytime user add new record on to a database it can increasingly auto fill a new record. after writting a code (refer to the following) it only fills the last three numbers e.g 875 skipping two zero number at the front.

Here is the code applied:

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!txtCIATAsiaNo.DefaultValue = Nz(DMax("[CIATinAsiaCode]", "Assets"), 0) + 1
Me.cmdDud.SetFocus

Else
Me.cmdDud.SetFocus
End If
End Sub

Question is: Would that be any possibility to combine those two zero and three numbers together such as 00875 ... as new record are added.

Thank you so much in advance for great help.

 

[FLM]

Try this:

Me!txtCIATAsiaNo.DefaultValue = format(Nz(DMax("[CIATinAsiaCode]", "Assets"), 0) + 1,"00000")

Frank

 

[xaysana]

Thank you for suggestion but it does not work. Is there any other way Would you like me to try please.

 

[FLM]

Is your field txtCIATAsiaNo numeric?
If so, change the format of txtCIATAsiaNo to "00000"

It's even better to change the format of the field in your table.
If you create a report or form, then the format will be taken over.

Frank

 

[xaysana]

Hi Frank,

Thank you so much for quick response.

In fact, this field is text formatted in my table. Also, in my form I have "00000" put in format property. I am not sure if this is related to the issue.

Please give me other tries.

Again, Thank you so much for your help, I really appreciate that.

 

[gemma-the-husky]

numbers are numbers - you don't want to store numbers as text - they are much easier to handle as numbers, so let your dbs store the number as 875, 876 etc. - it sounds like its working correctly

its when you display it on a form, you need to set an input mask in the table to display it in the 5 figure format, something like "00000" in the format.

 

[xaysana]

No, that is not going to solve it I have ready done all those.

Could you post me a simple db showing the proper configure and with a text field showing 00000 as default next when a new record entered will be 00001, 00002 .... 00011 so on.

I will appreciate for that
Thank you in advance

 

[gemma-the-husky]

I see

trouble is if you have a TEXT field, you cannot add 1 to a text field.

so your defaultvalue is translating the text field back into a number to add 1 to it, so when you change the number back to a text value to stroe it you need to reformat.

You will only have a problem with your code if you inadvertently store non-numeric data in the table.

try
tablefieldname = Format(CStr(variablename), "00000")

 

[xaysana]

Yes, it is perpectly functioning now.

I really appreciate your gently assistance

Thank you so much to all of you again.

Best,