Solved

VBA to Increment Revision Letters (Access 2000)

Posted on 2005-03-10
Medium Priority
695 Views
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
Question by:schmir1
• 2
• 2

LVL 41

Accepted Solution

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

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

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

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

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
Course of the Month12 days, 23 hours left to enroll

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.