?
Solved

VBA to Increment Revision Letters (Access 2000)

Posted on 2005-03-10
4
Medium Priority
?
695 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
  • 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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

580 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