Incrementing Text Field (as number)

[constantG]

Hi,

After reading all about Autonumber and what you should not do with them, I am building my DB from scratch. The problem that I immediately hit upon is as follows:
I created a table with a text field and want it to increment from 1 (so when the very first record is started the number 1 is already there).

I have tried putting the Dmax function in the default value, in VBA like this:
Private Sub IDNumber_BeforeUpdate(Cancel As Integer)
IDNumber = DMax("IDNumber", "Form_Input_Fam") + 1
End Sub

and many other methods. What am I doing wrong??

Thanks,

CG

 

[Galaxiom]

DMax on a text field is alphabetical so you get a different answer from a number.

To increment a text field means parsing the string to extract the number, increment it and concatenate this result back into the string.

It is often easier to separate the code into the numerical section and alpha section as separate fields. In mixed strings like this the alpha and numerical sections often have another meaning anyway so can be related to other tables separately by splitting the code into two fields.

If the aplha part is just a standard prefix applied to all codes then don't store it but concatenate it with the field in contol source on the forms and reports.