Vb coding problem

[abz_y2k]

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

 

[pbaldy]

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.

 

[wiklendt]

why not make an autonumber in your table, and simply append the HPS to the beginning? there's a very recent thread 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.

 

[RuralGuy]

why not make an autonumber in your table, and simply append the HPS to the beginning? there's a very recent thread 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?

 

[wiklendt]

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

 

[abz_y2k]

Hi pbaldy

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

 

[pbaldy]

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?