Vb coding problem (1 Viewer)

abz_y2k · Apr 29, 2009

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 · Apr 29, 2009

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 · Apr 29, 2009

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 · Apr 29, 2009

wiklendt said:
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 · Apr 29, 2009

RuralGuy said:
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 · Apr 29, 2009

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 · Apr 29, 2009

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?

Vb coding problem (1 Viewer)

abz_y2k

Registered User.

pbaldy

Wino Moderator

wiklendt

i recommend chocolate

RuralGuy

AWF VIP