VBA Macro Copy Table and Increment by One Letter

[earlstaley]

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.

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.

 

[earlstaley]

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

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