×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Value-Based Incrementing

thread700-1756457

Value-Based Incrementing

Value-Based Incrementing

(OP)
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!!

RE: Value-Based Incrementing

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

If so:

CODE

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.

RE: Value-Based Incrementing

(OP)
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!!

RE: Value-Based Incrementing

in your combo afterupdate event

CODE

someFieldValue = getNextID(me.yourcomboName)

CODE

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

RE: Value-Based Incrementing

To answer your question, try my code, replace the value of s 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:

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

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.

RE: Value-Based Incrementing

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

CODE

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]

RE: Value-Based Incrementing

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.

RE: Value-Based Incrementing

(OP)
Got it, let me try that.

RE: Value-Based Incrementing

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

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

RE: Value-Based Incrementing

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.

CODE

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

RE: Value-Based Incrementing

(OP)
That worked perfectly!!! Thanks!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close