?
Solved

VBA to Increment Revision Letters (Access 2000)

Posted on 2005-03-10
4
Medium Priority
?
684 Views
Last Modified: 2008-01-09
I'm looking for some bullet proof code to increment document (Generic Traveler) revision letters.  The database generates the letters and stores them as a string in a text field.  Right now I've got the capability of a one letter revisions.  For example, document 123 goes from rev A to rev B then rev C up to rev Z.  My code can't handle going beyond Z and of course now (after the DB has been used for 8 years) it needs to.  My simple code is:
    If rstTraveler.EOF Then
      Forms![Generic Traveler Form]!txtTravelerRev = "A"
    Else
      rstTraveler.MoveLast
      'Increment Rev Letter
      intRev = Asc(rstTraveler![Traveler Rev]) + 1
      strRev = Chr(intRev)
      Forms![Generic Traveler Form]!txtTravelerRev = strRev
    End If

I need to be able to generate rev letters beyond Z and I'm think maybe Z, AA, BB...ZZ, AAA, BBB, ZZZ, etc.
Anyone have anything that does something like this.
0
Comment
Question by:schmir1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1500 total points
ID: 13512143
Hmm, let's see:

   Dim Carry As Boolean, I As Long

    If rstTraveler.EOF Then
      Forms![Generic Traveler Form]!txtTravelerRev = "A"
    Else
      rstTraveler.MoveLast
      'Increment Rev Letter
      strRev = rst![Traveler Rev]
      Carry=True
      For I=Len(strRev) To 1 Step -1
         If Carry=True Then
            If Mid$(strRev,I,1)="Z" Then
               Mid$(strRev,I,1)="A"
            Else
               Mid$(strRev,I,1)=Chr$(Asc(Mid$(strRev,I,1))+1)
               Carry=False
            End If
         End If
      Next
      If Carry=True Then strRev="A" & strRev
    End If
    Forms![Generic Traveler Form]!txtTravelerRev = strRev

I *think* that will do it.
0
 

Author Comment

by:schmir1
ID: 13521860
It works great except it generated the letter a little different then my user would like.  It generates letters like:
A...Z, AA, AB, AC...AZ, BA, BB, BC, etc.

My user would like:
A...Z, AA, BB, CC...ZZ, AAA, BBB, CCC, etc.

If it's a big deal I might be able to talk them into the first way.  
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13521900
That's a bit tougher...if you can write down the logic of how you want the letters to be generated, I can try and modify the code for you.
0
 

Author Comment

by:schmir1
ID: 13539995
I took a shot at modifying it.  Not the most concise code in the world but it works.  Thanks for your help


Public Function GenRevLetter() As String
  Dim rst As Recordset
  Dim strQuery As String
  Dim strRev As String
  Dim intLen As Integer
  Dim intRev As Integer

  strQuery = "SELECT [Traveler Rev], [Generic Traveler ID], [Traveler #] FROM [Generic Traveler]" & _
             " WHERE [Traveler #] =" & "'" & Forms![Copy to New Generic Traveler]!txtTravelerNum & "'" & _
             " ORDER BY [Generic Traveler ID]"
  Set rst = CurrentDb.OpenRecordset(strQuery, dbOpenSnapshot)
  If rst.EOF Then
    Forms![Generic Traveler Form]!txtTravelerRev = "A"
  Else
    rst.MoveLast
    rst.MovePrevious
    'Increment Rev Letter
    strRev = rst![Traveler Rev]
    intLen = Len(strRev)
   
    If IsNumeric(strRev) Then
      intRev = strRev
      strRev = intRev + 1
    Else
      Select Case intLen  'Generate rev letters to match current number of letters
        Case 1  '1 letter rev
          If strRev = "Z" Then
            strRev = "AA"
          Else
            strRev = Chr(Asc(strRev) + 1)
          End If
        Case 2 '2 to 3 letter rev
          If strRev = "ZZ" Then
            strRev = "AAA"
          Else
             strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
          End If
        Case 3 '3 to 4 letter rev
          If strRev = "ZZZ" Then
            strRev = "AAAA"
          Else
             strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
          End If
        Case 4 '4 to 5 letter rev
          If strRev = "ZZZZ" Then
            strRev = "AAAAA"
          Else
             strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
          End If
        Case 5 '5 letter rev to numberic rev
          If strRev = "ZZZZZ" Then
            strRev = "1"  'starts the number sequence
          Else
             strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
          End If
        Case Else    ' Other values.
          LogEvt "GenRevLetter can't generate a rev letter for this document", vbCritical, "GenRevLetter Error (CGT-5)"
      End Select
    End If
  End If
  GenRevLetter = strRev
End Function

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 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