I have this code below to generate custom numbers

Private Sub Address_AfterUpdate()

Dim cw1

If NewRecord Then

cw1 = Nz(Right(DMax("[Reference]", "Table1"), 3))
Me.Reference = "HPS" & Format(cw1 + 1, "000")


End If


End Sub

but when it reached 1000, it wont add any further, it just keeps repeating 1000.
Iv tried to change the 3 after the table1 to 4 no use and change the 3 zeros to four no use.

any help please

I would expect changing both to work. Are you saying this doesn't work?

cw1 = Nz(Right(DMax("[Reference]", "Table1"), 4))
Me.Reference = "HPS" & Format(cw1 + 1, "0000")

Existing records with only 3 digits could affect things. Personally, I would store the reference number by itself, and just add the "HPS" on forms and reports.

why not make an autonumber in your table, and simply append the HPS to the beginning? there's a very recent thread (http://www.access-programmers.co.uk/forums/showthread.php?t=169692&page=2)covering this step-by-step (starts from page two). it is a little more complex than you need b/c it is also generating a custom three letter pre-fix, but it should help you.

why not make an autonumber in your table, and simply append the HPS to the beginning? there's a very recent thread (http://www.access-programmers.co.uk/forums/showthread.php?t=169692&page=2)covering this step-by-step (starts from page two). it is a little more complex than you need b/c it is also generating a custom three letter pre-fix, but it should help you.You are aware that AutoNumbers do *not* guarantee an unbroken sequence, right?

You are aware that AutoNumbers do *not* guarantee an unbroken sequence, right?

i certainly am :)

custom reference numbers also don't have to be in an unbroken sequence ;)

Hi pbaldy

I tried exactly what you said but it keeps giving me the same reference number everytime i enter a new record hps0766

Can you post the db? Is there a reason you don't want to store the number by itself and add the text as needed, since it's always the same?