VBA Macro Copy Table and Increment by One Letter

E

earlstaley

Joined
Jul 9, 2011
Messages
4
Reaction score
0
Hi

I have a three part question. I am somewhat familiar with VBA and SQL but not with Access at all. What I am trying to do is take one table of all letters and copy that to a new table incrementing each letter by one. I understand I need to create a function under modules and call that from macro but my head is spinning and I could use some help.

I found the function below that I believe will help with the incrementing by 1 letter. But I am having trouble getting to the next step.

Code: 
Public Function AutoIncrement(LetterIn As String) As String
Dim NumLetterIn As Integer

NumLetterIn = Asc(LetterIn)

If NumLetterIn = 90 Then
    AutoIncrement = "A"
Else
    AutoIncrement = Chr(NumLetterIn + 1)
End If
[FONT=&quot]So I need to take the contents of tblLetters and creates a new table with the next letter. For example, the first row of the newly created table will contain B-C-D-E-F, instead of A-B-C-D-E, and the second row will contain G-H-I-J-K, instead of F-G-H-I-J, and so on. Where Z resets to A. [/FONT]

Thanks in advance for any advice or links.
 
So I've gotten a little bit further but still need some help. I am having one error and one problem.

The error is Invalid use of Null here rs(2).Value = Chr(Asc(rs(2).Value) + 1) even though I am telling it to end the while when it gets to EOF.

The other problem is I can loop through a column but how do I get it to loop through every record and field in the entire table without pasting the code for each column?

Thanks

Code: 
Public Sub Increment()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblLettersIncrement")

While Not rs.EOF

rs.Edit
rs(2).Value = Chr(Asc(rs(2).Value) + 1)
rs.Update
rs.MoveNext

Wend

rs.Close
Set rs = Nothing
Set db = Nothing

' Display Confirmation
MsgBox ("Update complete")

' Open Table tblLettersIncrement
DoCmd.OpenTable ("tblLettersIncrement")

End Sub
 
Back
Top