Access 2013 Increment a text ID field in a form

Question

I have created a database from an excel file, one of the columns (PK) is the license number however this is in text format XX/XX/XXXX

When a new record is created I would like the form to auto-populate the license number field with a similar ID containing both text and numbers. This has to be incremented with every new record (only the number part)

This is exactly what I want to achive however I am getting the error shown below; https://www.youtube.com/watch?v=kPz-n5w5YtE

Any help would be appreciated, I have experience with VB however I am familiar with other programming languages.

Maybe I'm missing something but this seems remarkably simple? Can you give some examples of what kind of characters this "text number" contains, and how it increments? I'm thinking a simple `DMAX` or `DLAST` is basically all you need. — ashleedawg, Feb 08 '18 at 09:04
looks like you try to work with integer but using a string. is this correct or do I misinterpret the code? — Marco Sadowski, Feb 08 '18 at 09:06
(Sorry I'm not clicking a YouTube link and you'll find most here also can't be bothered. It would help if you could summarize to make it easier for others to help you.) — ashleedawg, Feb 08 '18 at 09:06
I managed to find what was causing the error, it was the previous value being different from the new ones in terms or format. I manually inserted a record with the new format and its working, if you have any simpler to suggestions to this I would still appreciate them. — Luke Gatt, Feb 08 '18 at 09:07
how was it different in terms of format? That's a pretty important thing to keep the same! — ashleedawg, Feb 08 '18 at 09:08

score 2 · Accepted Answer · answered Feb 08 '18 at 09:08

2

If your idea is to take a number (8) and convert it to "0008", as in the comment above the error, try like this:

strNextNumber = Format(lngNextNumber, "0000")

or see the whole code:

Public Sub TestMe()    
    Dim strNextNumber As String
    Dim lngNextNumber As Long
    lngNextNumber = 8
    strNextNumber = Format(lngNextNumber, "0000")
    Debug.Print strNextNumber        
End Sub

answered Feb 08 '18 at 09:08

Vityata

42,633
8
55
100

1

I ended up using strNextNumber = Format(lngNextNumber, "0000"), its more understandable and fits my requirements. – Luke Gatt Feb 09 '18 at 12:17

MatSnow · Answer 2 · 2018-02-08T09:30:48.680

If lngNextNumber is greater than 9999, the first argument of the String-function will be -1 or smaller and thus not valid.

So you can either check the value of lngNextNumber first.

If lngNextNumber < 10000 Then
    strNextNumber = String(4 - Len(CStr(lngNextNumber)), "0") & CStr(lngNextNumber)
Else
    strNextNumber = CStr(lngNextNumber)        
End If

Or even simpler, use the Format-function, like already mentioned in Vityata's answer.

strNextNumber = Format(lngNextNumber, "0000")

score 1 · Answer 3 · answered Feb 08 '18 at 09:52

1

Only one line is needed:

' Old license like "WT/7634/2002".
' New license like "ABC2003"
Me![License Number].Value = Format(Val(Right(DMax("[License Number]", [Licenses]), 4)) + 1, "\A\B\C0000")

answered Feb 08 '18 at 09:52

Gustav

53,498
7
29
55

Access 2013 Increment a text ID field in a form

3 Answers3