Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Value-Based Incrementing 1

Status
Not open for further replies.
mtownbound

mtownbound

Technical User
Jan 28, 2002
293
US
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!!
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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!!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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]

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MrMode
  • Locked
  • Question
Table locked after inserting a record with autonumber field value
Replies
2
Views
250
MrMode
MrMode
taxmanrick
  • Locked
  • Question
Reset incrementing table field when another field changes
Replies
11
Views
551
PHV
PHV
Moss100
  • Locked
  • Question
Which value do I store in the primary table from a support or lookup table? 4
Replies
15
Views
574
MajP
MajP
lamarw
  • Locked
  • Question
Multi value table field, a real problem for duplicating 1
Replies
12
Views
418
Darrylles
Darrylles
Ken
  • Locked
  • Question
Update column based on constraints
Replies
4
Views
271
Ken
Ken

Part and Inventory Search

Sponsor

Back
Top