Value-Based Incrementing 1

[mtownbound]

I need to create a field that serializes and increments based on the value from a combo box, doesn't have to be the PK, just need a "ID" text box. Something like this that's updated when the user selects the value in the Combo:

Type ID
Phone PH00034
Phone PH00035
Email EM00017
Phone PH00036
Email EM00018
Email EM00019
Email EM00020



Thanks!!

 

[Andrzejek]

Do you mean: when you have PH00034, you want the next ID to be PH00035?

If so:

Dim s As String
s = "PH00034"
MsgBox Left(s, 2) & Format(Val(Mid(s, 3)) + 1, "00000")

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mtownbound]

Will that increment the Id's based on the "Type" that is selected? Something like this:

PK Type ID
1 Phone PH00034
2 Phone PH00035
3 Email EM00017
4 Phone PH00036
5 Email EM00018
6 Email EM00019
7 Email EM00020

Thanks!!

 

[MajP]

in your combo afterupdate event

   someFieldValue = getNextID(me.yourcomboName)

Public Function GetNextID(IdType As String) As String
  'Your table name here
  Const tblName = "tblCorrespondance"
  Dim maxID As String
  Dim maxVal As Long
  maxID = DMax("ID", tblName, "Type = '" & IdType & "'")
  maxVal = val(Mid(maxID, 3))
  GetNextID = Left(maxID, 2) & Format(maxVal + 1, "00000")
End Function

 

[Andrzejek]

To answer your question, try my code, replace the value of [tt]s[/tt] with your ID value and see if it does what you want.

If it does not, let me know what you expect to happen.

BTW. To show your data in the post, select the data and click the icon PRE (between TT and colors) to get this:

[pre]
PK Type ID
1 Phone PH00034
2 Phone PH00035
3 Email EM00017
4 Phone PH00036
5 Email EM00018
6 Email EM00019
7 Email EM00020[/pre]

Always use Preview to confirm before Submit.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mtownbound]

Sorry for the VERY delayed response......but MajP, when I use your code, I'm getting 'Invalid use of Null'.

 Public Function GetNextID(IdType As String) As String
  'Your table name here
  Const tblName = "tblSeq_ID"
  Dim maxID As String
  Dim maxVal As Long
  maxID = DMax("ID", tblName, "Type = '" & IdType & "'")
  maxVal = Val(Mid(maxID, 3))
  GetNextID = Left(maxID, 2) & Format(maxVal + 1, "00000")
End Function]

 

[MajP]

I see why. I did not check for the case where there is not an existing record.
So if you pass in Phone and there is no record phone record the below will return null
maxID = DMax("ID", tblName, "Type = '" & IdType & "'")

You need to check that case, and then create the first record PH00001. That can be done with a dcount.

 

[mtownbound]

Got it, let me try that.

 

[mtownbound]

It's sequencing, but I'm not sure of the pattern. Here's what's in the Seq_ID column after my entries:

[pre]
ID Seq_ID Type
35 32000001 Phone
36 33000001 Email
37 35000001 Phone
38 37000001 Phone
39 38000001 Phone
40 36000001 Email
41 40000001 Email
42 41000001 Email
43 34000001 Walk-In
44 43000001 Walk-In
45 44000001 Walk-In
46 39000001 Phone
47 46000001 Phone
[/pre]

 

[MajP]

The field I was calling ID is what you are calling Seq_ID, that is the reason for the strange pattern. This should work and handle cases where there is not a record already.

Public Function GetNextID(IdType As String) As String
  'Your table name here
  Const tblName = "tblCorrespondance"
  Dim maxID As String
  Dim maxVal As Long
  If DCount("Seq_ID", tblName, "Type = '" & IdType & "'") = 0 Then
    maxVal = 1
  Else
    maxID = DMax("Seq_ID", tblName, "Type = '" & IdType & "'")
    maxVal = val(Mid(maxID, 3)) + 1
  End If
  GetNextID = UCase(Left(IdType, 2)) & Format(maxVal, "00000")
End Function

 

[mtownbound]

That worked perfectly!!! Thanks!!