Solved

Auto Increment Text Field

Posted on 2014-04-08
4
48 Views
Last Modified: 2016-05-29
Hello,

I have a 2 character text field that I need to increment automatically in a table via VBA.  For example, I have a record with the pseudo key AA that I need to create a new record for under pseudo key AB.  I have thousands of records that I need to do this for.  Here are some examples of the keys I'm working with: AA, CB, AZ, ZA, PQ, et...the records that are created look at the alphabet for the second digit and adds the next consecutive letter.   How would I be able to do this via VBA?

Thank you.
0
Comment
Question by:axfernand
4 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you need to use VBA codes to achieve this

test this function,

Function getNextValue(vStr As String)
Dim vChar As Long, firstChar As String
firstChar = Left(vStr, 1)
vChar = Asc(Right(vStr, 1))

getNextValue = firstChar & Chr(vChar + 1)
End Function

Open in new window

?getNextValue("AA")
AB
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 500 total points
Comment Utility
this one goes a bit further: (increments to ZZ)

Function getNextValue(vStr As String) As String
  Dim vChar As Long, firstChar As String, secondChar As String
  firstChar = Left(vStr, 1)
  secondChar = IIf(Len(vStr) = 2, Right(vStr, 1), "")
  If Asc(secondChar) = 90 And Asc(firstChar) = 90 Then MsgBox "We have a ZZ problem!", vbCritical
  If Asc(secondChar) < 90 Then
    secondChar = Chr(Asc(Right(vStr, 1)) + 1)
  Else
    firstChar = Chr(Asc(firstChar) + 1)
    secondChar = Chr(65)
  End If
  getNextValue = firstChar & secondChar
End Function
0
 

Author Comment

by:axfernand
Comment Utility
Hi Guys,

Haven't had a chance to apply your code but will do next week.  Thanks for the responses.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now