Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Help coding For Next Loops 1

Status
Not open for further replies.
calihiker

calihiker

Technical User
Jun 13, 2003
96
US
I need to display a 4 character keycode sequence in a textbox... each postion should start with 0 to 0 then A to Z. For example..

0001
0002
0003
0004
0005
0006
0007
0008
0009
000A
000B
|
|
000Z
0010
0011
and so on...
I created some code and it is cycling through ok until the third character get to the letters and then the fourth character letters are skipped. I see the flaw in the control structure, but I can't figure out a way to get around it... Please Help!!! :)

Here is my code..

Private Sub Command0_Click()

Dim FirstDigit As Integer
Dim SecondDigit As Integer
Dim ThirdDigit As Integer
Dim FourthDigit As Integer

Dim FirstDigit2 As Integer
Dim SecondDigit2 As Integer
Dim ThirdDigit2 As Integer
Dim FourthDigit2 As Integer

Dim FirstLetter As String
Dim SecondLetter As String
Dim ThirdLetter As String
Dim FourthLetter As String

Dim flag As Boolean
flag = False

Dim Keycode As String
Dim z As Long

For FirstDigit = 0 To 9
For SecondDigit = 0 To 9
For ThirdDigit = 0 To 9
For FourthDigit = 0 To 9
If FourthDigit = 9 Then
For FourthDigit2 = 1 To 26
FourthLetter = Choose(FourthDigit2, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
Me.txtKeycodes.SetFocus
Me.txtKeycodes.Text = FirstDigit & SecondDigit & IIf(ThirdDigit = 10, ThirdLetter, ThirdDigit) & FourthLetter
For z = 0 To 500000
Next z
Next FourthDigit2
Else
Me.txtKeycodes.SetFocus
Me.txtKeycodes.Text = FirstDigit & SecondDigit & ThirdDigit & FourthDigit
For z = 0 To 10000000
Next z
End If
Next FourthDigit
If ThirdDigit = 9 Then 'after textbox displays the 9 for the third digit then test
For ThirdDigit2 = 1 To 26 'takes care of third position letters
ThirdLetter = Choose(ThirdDigit2, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For z = 0 To 1000
Next z
Me.txtKeycodes.SetFocus 'needs to cycle in here (with this existing code) the fourth place character
Me.txtKeycodes.Text = FirstDigit & SecondDigit & IIf(ThirdDigit = 9, ThirdLetter, ThirdDigit) & FourthLetter 'works
Next ThirdDigit2
Else
Me.txtKeycodes.SetFocus
Me.txtKeycodes.Text = FirstDigit & SecondDigit & ThirdDigit & FourthDigit
For z = 0 To 10000000
Next z
End If
Next ThirdDigit
Next SecondDigit
Next FirstDigit

End Sub
 
Sort by date Sort by votes
Hi,

What you have is a Base 36 number system. So, it's just a matter of setting up a basic variable base "number" generator.
Code: 
Private Sub Command0_Click()
    Dim sPrefix As String, sNbr As String, iNbr As Byte
    Const NBR_BASE = 36
    
    For i = 1 To NBR_BASE ^ 4
        k = i
        sNbr = ""
        For j = 3 To 0 Step -1
            n = Int(k / NBR_BASE ^ j)
            If n > 9 Then
                sNbr = sNbr & Chr(55 + n)
            Else
                sNbr = sNbr & n
            End If
            k = k - n * NBR_BASE ^ j
        Next
        Me.txtKeycodes = sNbr
    Next
End Sub
You might want to set a limit in the loop to exit before generating 36^4 numbers.

Hope this is what yer looking for :)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
BTW,

This will generate sequential numbers in any base up to 36 to 4 places...

hexadecimal - 16
octal - 8
binary - 2

Could make it a bit more general by substituting a value for the number of places too...
Code: 
Private Sub Command0_Click()
    Dim sPrefix As String, sNbr As String, iNbr As Byte
    Const NBR_BASE = 16
    Const NBR_PLACES = 4
    
    For i = 1 To NBR_BASE ^ NBR_PLACES
        k = i
        sNbr = ""
        For j = NBR_PLACES - 1 To 0 Step -1
            n = Int(k / NBR_BASE ^ j)
            If n > 9 Then
                sNbr = sNbr & Chr(55 + n)
            Else
                sNbr = sNbr & n
            End If
            k = k - n * NBR_BASE ^ j
        Next
        if i = activesheet.cells.rows.count then exit for
        ActiveCell.Value = "'" & sNbr
        ActiveCell.Offset(1, 0).Select
    Next
End Sub


Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
Thanks! It works great! I wish I had thought of code as simple as that. Though I have to admit, I don't fully understand it yet...
 
Upvote 0 Downvote
I tried adding this code to append into a table that would hold all the values, but it appends "0 records" when run each time... Is there something wrong with this statement?


DoCmd.RunSQL "INSERT INTO All_Keycodes ( AllKeys ) " & _
"SELECT [Forms]![KeycodeSequence2]![txtKeycodes].[Text] AS Keycode_Text " & _
"FROM All_Keycodes"

Thanks,
Andre
 
Upvote 0 Downvote
You may have to do something like this assuming that tstKeyCodes is your Key Code variable...
Code: 
    DoCmd.RunSQL "INSERT INTO All_Keycodes ( AllKeys ) " & _
    "SELECT [Forms]![KeycodeSequence2]![" & txtKeycodes & " ].[Text] AS Keycode_Text " & _
    "FROM All_Keycodes"
But I would thing that the form of INSERT would be
Code: 
"INSERT INTO All_Keycodes (AllKeys) " & _ 
"VALUES (" & txtKeycodes & ")"
done in a loop until all the key codes are inserted.

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
For some reason only this worked, after some trial and error ...

DoCmd.RunSQL "INSERT INTO All_Keycodes ( AllKeys ) " & _
"SELECT [Forms]![KeycodeSequence2]![txtKeycodes].[Text] AS Keycode_Text "

I guess the line "FROM ALL_Keycodes" messed it up..

Thanks for all your help :)
 
Upvote 0 Downvote
Yes, but initially I wanted to make sure it was cycling through the way I wanted to -via viewing the values on the text box and then I was going to take care of the insert stuff. By the way, what a good way to stop code when it is cycling thought so many times? I tried Ctrl + Brk, but it still takes a while...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
281
Andrzejek
Andrzejek
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
317
combo
combo
remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
502
strongm
strongm
Sanjee1989
  • Locked
  • Question
Need coding in vba to password protect .zip files
Replies
3
Views
99
strongm
strongm
sxschech
  • Locked
  • Question
For Each Loop works once then gives error 91 2
Replies
19
Views
349
sxschech
sxschech

Part and Inventory Search

Sponsor

Back
Top