CODEWITHSUNDEEP
vbams-accessreplacems-access-forms
soldier2gud4me
soldier2gud4me

Reputation: 79

How to replace the last characters depending on the count of another textbox

I am trying to create a custom Autonumber for a registration form.

In my form I have an unbound textbox (txtposition) that gets the current record number. This number is the actual record number. If I have 10 records and I am at the last record it will display as 10.

I have another text box (txtref) with a default value as 0000. What I need to get is the count of txtposition (if 10 means 2 and 100 means 3) and replace txtref from 0000 to 0010 or 0100 depending on the count of txtposition.

I already have another unbound textbox which will concatenate the results if required. I am trying to get this done in a query with: 

spacepos: InStr([Forms]![Jobsheet]![Text516],"0000")

FN: Left([Forms]![Jobsheet]![Text516],[spacepos])

I know this will require a combination of INSTR, LEN, RIGHT or LEFT and surely REPLACE functions.

My knowledge of coding is too basic but I am able to follow.

The code used for getting the current record number for txtposition:

Private Sub Form_Current()
    On Error GoTo err_form_current

    Dim rst As Recordset
    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Bookmark
    Me.txtPosition = rst.AbsolutePosition + 1

exit_form_current:
    Set rst = Nothing
    Exit Sub

err_form_current:
    If Err = 3021 Then 'no current record
        Me.txtPosition = rst.RecordCount + 1
    Else
        MsgBox Error$, 16, "Error in Form_Current()"
    End If
    Resume exit_form_current
End Sub

Upvotes: 0

Views: 50

Answers (1)

June7
June7

Reputation: 21370

Just save a normal number then use formatting to display with leading zeros. Format(10,"0000") will display as string 0010. No VBA required. Can even format an autonumber field using Format property or Format function. However, if cannot allow gaps in sequence, then an autonumber might not meet your requirements.

Upvotes: 1

Related Questions