String Functions
DLookup
After Update

Thanks very much Alex I'll work my way through those

Hi Stanley, Watch Richard's video "Sequential Numbering" You may need 2 fields (1 for Text (A,B,C etc) and 1 for Numbers. But you can do it.  John

Sequential Numbers

Try this (see screenshot). You may need to make modifications to suit your data. This assume that the Prefix is always on character between A and Z. I called the sub in the On Change event of the Text Box.

Thanks John, Kevin and Alex. I'll get my head round all this and report back soon. Great to get help learning all this stuff. Very much enjoying the challenge of it.

Just a thought -- my first approach was going to be ( yous maybe able to advise if it's a bad idea or not )
1. Make a query that can sort the item number field into separate fields / columns ( which runs or requeries each time the event is called )

This might be impractical cos there's over 2000 items so far.

2. Find Max of each query field and add 1

Maybe adding 1 would return a data mismatch?

Kevin -- I see a reference to SQL on your screenshot of code -- is that a function I can use within access or will that require an external server or is it just another declared variable ?

It's a local variable. Look at the declarations (SQL As String).

Thought that but wasn't sure if there was any other significance wrt Structured Query Language

Try using Len, Right, and Left Access functions
EX:

vString = A12345 '...................................................................Last number in your records (use DLOOKUP)
cStringLength = Len(vString)     '..............................................6
cStringNumber = (cStringLength - 1) '.....................................This give the character count of all but the first letter
LetterLength = Left(vString, 1) '........A................................... (the letter part of the search string, A-Z)
NumberLength = Right(vString, 5) '=12345.............................The number part of the field
NumberLengthPlus = NumberLength + 1 ...............................'.....12345+1=12346
vStringSuggested = LetterLength & NumberLengthPlus '..........  = a & 12346 = A12346

Thank  Tom -- our internet is out cos of storm Eowyn but getting some internet on phone -- will have a look at your approach when we get internet back.

Kevin I tried your code but getting " error on join "
  Checked for spelling mistakes but can't find any. I take it FROM ItemT is from the Table the form text box is bound to?

Forgot to update this thread -- I did eventually use Kevin's code but I found and fixed a couple of problems with the way I typed it in and maybe cos I'm using 2007.

  I've never been able to use the underscore to continue code onto the next line so had to do away with that ( maybe an upgrade in later versions -- I don't know )

  Then I found I needed a space between the quotation marks and the WHERE -- found that from some other forum -- again maybe some kind of compatibility issue between my version and newer versions.

  There were other problems like understanding SQL protocols etc but suffice to say Kevin put me on the right track.

  Thanks Kevin.

All works lovely now

This is an update to my original thread
https://599cd.com/blog/display-comment.asp?CommentID=100496

My question was
" I  think I need help with this one. I have a field in a table with item number. There is a new item form where users can add a new record of a new item. Item numbers are categorized using a letter at the start eg
A12
A13
A25
C82
F22
.......etc

Is it possible, when the user types a C for instance the text box on the form can suggest a new C number which is the last C Number +1 to prevent duplicates.

  The field property of the table is set to 'indexed no duplicates'

  Is there a simple function to do this?

  Your help is greatly appreciated.

  I found a problem with the solution we arrived at after getting help from some great forum members. Will explain that in the next post.

Ah the title meant to be 2.0 or MK2

I see my new update post is now on the previously closed thread so I will continue with the explanation here.

  There are two main problems but I found a handy workaround for both problems.

  The first is that I discovered that the list of item numbers includes some that have 3 letters in the prefix instead of just one. ( I can't really get this changed easily since this data is not set up by me and would need approval at a higher level )

  The bogey item numbers start MXT001, MXT002, ...  etc

  There are no other 3 letter prefixes and I've advised there not be any others introduced ( 3 letter prefixes )

   The second problem is that the code evaluates the numbers bit of the item number which ignores any zero's ie if you have A008 you won't get A009 as the new suggested item number you'll get A9

If looking for the next MXT number you'll get the next M number whatever that happens to be.

  I came up with a completely different solution to the VBA code above.

I used a query with the criteria using the Like function ( as in the search form videos ) and Not Like function to find the subset of records and then make the form only show the last one. So the user can decide what the next item number can be.

  I'll show the query criteria later when I get back home... The solution wasn't as straight forward as I first thought it would be but ended up one of those light bulb moments and may come in handy in future.

OK -- back home and family stuff sorted -- now for that criteria function " Like " and " Not Like "

  My criteria is

Like [Forms]![Add_New_Item_Form]![EnterPrefixTextBox] & "*" And Not Like "*" & [Forms]![Add_New_Item_Form]![EnterPrefixTextBox] & "XT" & "*" And Not Like "*" & [Forms]![Add_New_Item_Form]![EnterPrefixTextBox] & "-" & "*" And Not Like "*" & [Forms]![Add_New_Item_Form]![EnterPrefixTextBox] & "T" & "*"

The button on the form has this code on it's click event

Private Sub FindLastItem_Button_Click()
Dim Prefix As String
Me.Refresh
If IsNull(EnterPrefixTextBox) Then
    Exit Sub
End If
'Get the prefix entered by the user (first letter only)
Prefix = Left(EnterPrefixTextBox, 1)
'Ensure the Prefix is valid (You can adjust this check)
If Not Prefix Like "[A-Z]" Then
    MsgBox "Please Start the item number with a valid letter (A-Z).", vbExclamation, "Invalid Prefix"
    Exit Sub
End If
DoCmd.OpenForm "FindLastItem_Q"
DoCmd.GoToRecord acDataForm, "FindLastItem_Q", acLast

End Sub

Explanation

The query criteria is where the magic happens. The criteria is taking it's variable from the "EnterPrefixTextBox" on the Add Item Form and uses the Like function to find all the Item numbers that begin with whatever the user enters. This is simple for one letter prefixes but will also list all the 3 letter prefixes that happen to start with that letter.

  Eg when the user types M in the text box they'll get all the MXT's listed as well.

  The way to stop that is to use the And Not Like function along with the initial Like function but you don't put in And Not Like MXT cos you'll get nothing for M listed. What you do is to say Like (M) & and Not Like (M) & and XT and Not Like (M) & and T.

  You'd be forgiven for thinking you'd lose T, MXT and X  items but what you're actually saying is give me M prefix items but not MXT ie M & XT so when you want MXT items only what you're asking it to bring back is MXT but not MXTXT or MXTT.

  Clever or what?

Incidentally any unwanted other character can be included in the Not Like bit eg I needed to include a hyphen sign as someone had listed an item number with an B prefix that also had an A - E  on the end of it so it would appear on A lists and E lists.  ( on previous system so couldn't be changed or ruled out by my DB )

   Going down this route of using the Like and Not Like functions means there's no problem with that Val( ) function on the previous VBA method shown missing out the zero's on item lists ie only returning whole numbers.

   The information for the user is also a bit more reassuring ie seeing the last item with it's description maybe or maybe the last 5 items --  you can decide what to show.