Automatic adding sequential numbering

[lguelcher]

I am trying to add a button to a form that will automatically create a new entry with sequential numbering. Currently the numbering function is on the table "Client TP Tracker" in the field TPID. Once the button is clicked, a new form should be created and the new TPID number should be the next one available. The code I've tried is this:

Private Sub Command167_Click()

Dim TPID As Variant
Dim strMax As String
Dim IngNewNum As Long
Dim OpenDate As Variant

 'Copy fields to variables
  TPID = Me!TPID.Value
  
 'Go to a new record
  DoCmd.GoToRecord , , acNewRec

 'Reverse the process and plug old values into new record
  Me!TPID.Value = DMax("TPID", "Client TP Tracker")
  Me!OpenDate = Date
    
  If Me.NewRecord = True Then
  strMax = Nz(DMax("TPID", "Client TP Tracker"), "CLT-00000")
  IngNewNum = CLng(Right(strMax, 5)) + 1
  Me.TPID = "CLT-" & Format(IngNewNum, "00000")
  End If

I'm getting a Compile error: Method or data member not found over the Me.TPID section right before the End If.

I'm not sure what I have wrong here.

 

[MarkK]

Just use an auto incrementing AutoNumber, and construct your fancy human readable code at retrieval time in a field in a query. It's much, much easier.

Here's how you'd calculate your field in a query.

FancyHumanReadableCode: "CLT-" & Format([YourRowID], "0000000")

Simple. Done.

Hope this helps,

 

[lguelcher]

Thanks MarkK-

But I don't follow what you're suggesting. I'm relatively new to writing macros and borrowed the code I have from a co-worker (and changed the references) who had it working with a different table (SBT instead of CLT).

To use the code you suggest, where would I enter the code?

Thanks for your help!!!
Leslie

 

[MarkK]

"CLT-00045" is two pieces of data. There is a string, and a number. If you store them in one field, then you have to expend effort to merge them, and effort to separate them, which is a waste of time and adds complexity to your system. Instead, save them in separate fields, and join them if and when you need to.

Keep in mind that maintaining a fancy human readable code like this is completely unimportant to your data. It is a liability that you implement so humans can attach meaning to an object, but life would be easier without such a code. Keep in mind you have a computer now, so you can show any data you want. Why show "CTB-00002?" Why not show "Charlie's Tango Bravo #2?" To me the latter is clearer than the cryptic code.

Does that make sense?

 

[lguelcher]

I can follow that and it does make sense. The reason for the abbreviation is that it is used across a number of platforms and reports, so they want to keep it. But, I can certainly separate it into two parts. Thanks.