I want my asset numbers with letters to auto-populate as i enter a new asset

[Mando]

my asset numbers look like this MIT000001DT, MIT000002DT, etc is there a way to auto populate my asset number without having o enter it. I was thinking it would look like this $$$000001$$ + 1= (new asset number)? any suggestions?

[Bob Fitz]

Try:
="MIT" & Mid(DMax("[AssetNum]","[tblAssets]"),4,6) & "DT"
Change AssetNum to the name of the field that has the asset numbers and change tblAssets to the name of your table.

[Mando]

I tried entering in the Table Design of the field and it would not accept this expression. I don't think that I set it in the form, but is there a different place to input the formula?

[rpeare]

if you're storing the asset number as a string and not a number (if you're storing it as a number you can make the display format be anything you want)

AssetNum = "MIT" & right("000000" & (Mid(DMax("[AssetNum]","[tblAssets]"),4,6)+ 1),6) & "DT"

I think bob's will give you just the most recent assetnumber (assuming it's stored as a number)

[rpeare]

If you're using a bound form you'd use the code when you have enough data to confirm that it's a valid new record (i.e. all the required fields have been entered)

[Bob Fitz]

The expression I posted will return the next number to be used from a field in which the asset number is stored with both the prefix and suffix that the OP posted.

[rpeare]

Really? how is it incrementing from the maximum record?

let's say his current maximum record is MIT000010DT wouldn't the expression:

="MIT" & Mid(DMax("[AssetNum]","[tblAssets]"),4,6) & "DT"

return
dmax("[AssetNum]", "tblAssets") would return MIT000010DT so the expression would become

"MIT" & mid("MIT000010DT", 4,6) & "DT"

which is

"MIT" & "000010" & "DT"?

I guess I am not catching how this expression alone would increment.

[Mando]

Where do I enter this expression the table property sheet wont accept it and I put it in the form and it didn't do anything. I will keep trrying thanks you guys. it has been helpful

[Mando]

the expression can not be calculated

[rpeare]

You don't enter the formula on the table. If you are using a BOUND form (you are using either a table or a query as the record source for your form) you would have something like

if isnull(AssetNum)
   AssetNum = <put your formula here>
endif

so if assetnum is blank (not filled in) your code will populate it, otherwise it will leave the assetnum alone, if you are using a bound form I would suggest you set the LOCKED property of that field to TRUE so that your users won't accidentally change the value.

[Bob Fitz]

Really? how is it incrementing from the maximum record?

let's say his current maximum record is MIT000010DT wouldn't the expression:

="MIT" & Mid(DMax("[AssetNum]","[tblAssets]"),4,6) & "DT"

return
dmax("[AssetNum]", "tblAssets") would return MIT000010DT so the expression would become

"MIT" & mid("MIT000010DT", 4,6) & "DT"

which is

"MIT" & "000010" & "DT"?

I guess I am not catching how this expression alone would increment.

My apologies. You are of course quite right in stating that it will not increment. Between testing and posting I've managed to loose the +1 in the expression. Sorry