sequential alphanumeric order numbers (1 Viewer)

[TecknoFreak]

Touching on what Dave has pointed to; you already have a table with 5000+ records that are prefixed COD. How long has it taken to get to this point? Reason being if it has not been long, it is not gpoing to be long before you run out of numbers 9999+.

Firstly consider the size of your text field, is it set to 7? If so, increase that to 10 for futureproofing.

Or simply convert the field to a number field and format the field contents acordingly

COD:"COD" & Format([CODNumber],"000000")


Next, and again already questioned is the prefix always going to be COD? if so, then why prefix the number in the field. Simply concat this onto the desired field when viewing it. Not only does it save space but also reduces the keystrokes a user will have to make to type it in a search box or combo box.

If you have different prefixes store the code of the prefix in a table and store the PK in the main table as a foreign key and use that.

Something along the lines of

DocumentNo:Choose(DocType,"ORD","COD","INV","GRN","PO","CR","ETC") & Format([CODNumber],"000000")

Hello DC,
I have this exact problem. How you exactly do that in green to be able for the Prefixed to go beyond 9999? Is that done from the table itself or can it be done from the form?
Sorry Im learning still VBA



This is my code

'------------------------------------------------------------
' add_new_record_Click
'
'------------------------------------------------------------
Private Sub add_new_record_Click()
Dim abc As String
Dim ab As Integer
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select MAX(ConfigNo) as maxConfigNo from [IAAIMS DATA ENTRY TABLE]")

ab = Right(rs!maxConfigNo, Len(rs!maxConfigNo) - 1) + 1

Me.txtMaxConfig = "C" & ab
   On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
'    Me.ConfigNo = "C" & abc
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
Me.ConfigNo = Me.txtMaxConfig
'Me.btnComplete.Enabled = True
 

End Sub

 

[Gasman]

You have responded to a 11 year old thread?

 

[TecknoFreak]

You have responded to a 11 year old thread?

I did because I searched the entire forum for my problem and this is exactly my problem.

 

[Gasman]

Increasing a field size?

 

[TecknoFreak]

Increasing a field size?

Is on default at 255

 

[TecknoFreak]

Currently I only have 10255 records. But the ConfigNo is stuck at 10000. Every record auto populate the ConfigNo to the latest ConfigNo used +1 but is not working

 

[Gasman]

That seems to infer it can only hold 9999?
You will likely need to upload a cut down version of your db.
Best to start a new thread and perhaps just link to this one.
I would just have a numeric long field and use that.