Access 2007 - vb code

Posted on 2014-09-10
Medium Priority
Last Modified: 2014-09-11
i am trying to create a password creator app.  And the code is as attached.  I wish to split the strCharBase
into 3 variables :
eg: strCharBase1 = "0123456789" ' this is for the numeric'
strCharBase2 = "ABCDEFGHIJKLMNOPQRSTUVWXYVZ" ' this is for the upper case of the Char letters '
strCharbase3 = "abcdefghijklmnopqrstuvwxyvwxyz" ' this is for the lower case of the Char letters'
strCharbase4 = "~!@#$%^&*()_+|}{:<>?/" ' this is for the keyboard's extended chars. '

I would like the user to specify the total length of the  password to be,
then he to specify the total length of the strCharbase1, 2, and 3;
 (eg he might want strCharBase1 to be 2 chars in length, which means I would need to randomize 2 numbers, to be passed to strCharBase1.
then strCharBase2 3 chars in length,
Thus the total being what the user wants the total length to be.
I am lost at where to start, maybe I guess to capture what the total length to be first.
Then starting from the strCharBase1 to use a variable what length this should be .
Hope the experts can please give me an initial help with just one module and let me see if i can complete the rest.  
Attached are the main code and also the code for the module where the other code is.
Thank u.
Question by:jegajothy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 40316788
Seems you would first ask the user for the specifics - i.e. how many UpperCase characters, how many LowerCase characters, etc etc.

From there, you'd just randomize your character selections, and stick them together to create the password.

I think I'd modify the Random function to allow you to pass in the character sets:

Public Function Random(RLength As Integer, CharacterBase As String) As String
' This function creates a string of random characters, both numbers
' and alpha, with a length of RLength.  It uses Timer to seed the Rnd
' function.

' Random() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact support@extramiledata.com.
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_Random

    Dim strTemp As String
    Dim intLoop As Integer
    Dim strCharBase As String
    Dim intPos As Integer
    Dim intLen As Integer
    ' Build the base.
    strCharBase = CharacterBase' "01234ABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+|}{:<>?/" _
    & "abcdefghijklmnopqrstuvwxyz56789"
    ' Get it's length.
    intLen = Len(strCharBase)
    ' Initialize the results.
    strTemp = String(RLength, "A")

    ' Reset the random seed.
    Rnd -1
    ' Initialize the seed using Timer.
    Randomize (Timer)

    ' Loop until you hit the end of strTemp.  Replace each character
    ' with a character selected at random from strCharBase.
    For intLoop = 1 To Len(strTemp)
        ' Use the Rnd function to pick a position number in strCharBase.
        ' If the result exceeds the length of strCharBase, subtract one.
        intPos = CInt(Rnd() * intLen + 1)
        If intPos > intLen Then intPos = intPos - 1
        ' Now assign the character at that position in the base to the
        ' next strTemp position.
        Mid$(strTemp, intLoop, 1) = Mid$(strCharBase, intPos, 1)

    ' Return the results.
    Random = strTemp

    On Error Resume Next
    Exit Function
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Random"
    Random = ""
    Resume Exit_Random

End Function

Open in new window

Then use it like this:

[General Declarations]
Private Const conUpper As String = "ABCDEFGHIJKLMNOPQRSTUVWXYVZ"
Private Const conLower As String = "abcdefghijklmnopqrstuvwxyvwxyz"

[In code, perhaps a button click]
Dim sUpper As String
Dim sLower As String
sUpper = Random(Me.txNumUpperCase, conUpper)
sLower = Random(Me.txNumLowerCase, conLower)
etc etc

Once you get the component pieces, you just add them together:

Me.txPassword = sUpper & sLower & etc etc

Of course, this would give you a password like this: MYPas*&%

If you want to mix those up, so the password would look like %Ma&YPs*, then you'd need another function to "scramble" those ...
LVL 46

Assisted Solution

aikimark earned 400 total points
ID: 40316796
* Looks like you have four charbase variables, not three.

then he to specify the total length of the strCharbase1, 2, and 3
* These variables already have a length.  Do you mean the number of characters to be taken from each of the categories?
* Do the characters from these categories have to be consecutive?
* I think you might be better off if you changed your four distinct charbase variables into an array

You should be aware of the short-comings of the pseudo-random number functions in the VBA environment.  Please read my article: An Examination of Visual Basic's Random Number Generation,  http:A_11114.html
LVL 26

Assisted Solution

Nick67 earned 1200 total points
ID: 40317309
So far what you got looks ok.
You've got something that will randomize the random number generator
(given the same seed the rnd function will generate the same sequence of 'random' numbers again)
But you are working with strings -- which makes your life much, much harder than it needs to be :)

We have the Chr() function for generating characters.
For capitals, the code
Dim caps as string
For x = 64 to 90
    caps = caps & chr(x)
next x

will generate

X= 97 to 122 gives the lower case
x= 48 to 57 gives 0-9
X= 33 to 47 and x = 58 to 64 and x = 91 to 96 and x = 123 to 126 gives you all those special symbols
(you can see now why some website password policies aren't big on special symbols!)

So you have a form that asks (and validates) how long a pwd you want
How many numbers
How many lowercase
How Many Uppercase
How many specials.

Then its all loops and concatenations involving the generation of random integers.
Dim TotalLength as integer
Dim  numbers as integer
Dim  lowercase as integer
Dim  Uppercase as integer
Dim  specials as integer
Dim pwd as string
pwd = ""

Dim Top as integer
Dim charnum as integer

Do until TotalLength = 0
    'generate a random number between 1 and 4
       Select case Top
             Case 1 'add a number to pwd
                       if numbers < 1 then goto RegenerateTop  'no more numbers needed
                    'generate a random number integer < 128 and set charnum equal to it
                    'if it isn't between 48-57 then
                           'goto RegenerateNumber
                          pwd = pwd & chr(charnum)
                          numbers = numbers -1
              Case 2
                    'I'll let you thrash these out
              Case 3
                    'I'll let you thrash these out
              Case 4
                    'I'll let you thrash these out
        end select
        TotalLength = TotalLength -1

Open in new window

When the loop completes you've got your password.
That's how I would do it.
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

LVL 26

Expert Comment

ID: 40317349
The snippets are pseudocode.
Some of it will copy-and-paste into the VBE ok, but I do not indicate that all of it will.
I use code blocks strictly for code that I have copied from working apps and I know it'll work.
This is still air-code -- and hence NOT in a code block.
A lot of it will paste in ok-- but pasters need to be vigilant.

I find nothing quite as irritating as copying a block of 'code' to find that it doesn't work :)
LVL 26

Assisted Solution

Nick67 earned 1200 total points
ID: 40317515
See, now this is not aircode
Sample attached
Option Compare Database
Option Explicit

Private Sub cmdGenerate_Click()
Dim TotalLength As Integer
Dim numbers As Integer
Dim lowercase As Integer
Dim Uppercase As Integer
Dim specials As Integer
Dim pwd As String
pwd = ""

Dim Top As Integer
Dim charnum As Integer

' Reset the random seed.
Rnd -1
' Initialize the seed using Timer.
Randomize (Timer)

TotalLength = Me.txtLength
numbers = Me.txtNumbers
lowercase = Me.txtLowercase
Uppercase = Me.txtUppercase
specials = Me.txtSpecials

If TotalLength <> numbers + lowercase + Uppercase + specials Then
    MsgBox "the total length does not equal the number of character types.  Try again!"
    Exit Sub
End If

Do Until TotalLength = 0
    'generate a random number between 1 and 4
    Top = RandNum(1, 4)
    Select Case Top
        Case 1 'add a number to pwd
            If numbers < 1 Then
                GoTo RegenerateTop  'no more numbers needed
                'call RandNum between 48 and 57 for a number and add it
                pwd = pwd & Chr(RandNum(48, 57))
                numbers = numbers - 1
            End If
        Case 2
            If lowercase < 1 Then
                GoTo RegenerateTop  'no more numbers needed
                'call RandNum between 48 and 57 for a lowercase and add it
                pwd = pwd & Chr(RandNum(97, 122))
                lowercase = lowercase - 1
            End If
        Case 3
            If Uppercase < 1 Then
                GoTo RegenerateTop  'no more numbers needed
                'call RandNum between 64 and 90 for a uppercase and add it
                pwd = pwd & Chr(RandNum(65, 90))
                Uppercase = Uppercase - 1
            End If
        Case 4
            If specials < 1 Then
                GoTo RegenerateTop  'no more numbers needed
                'call RandNum between 33 and 126 check if its a special and add it
                charnum = RandNum(33, 126)
                Select Case True
                    Case charnum > 47 And charnum < 58 'number
                        GoTo RegenerateSpecial
                     Case charnum > 64 And charnum < 91 'capital
                        GoTo RegenerateSpecial
                     Case charnum > 96 And charnum < 123 'lowercase
                        GoTo RegenerateSpecial
                Case Else
                    pwd = pwd & Chr(charnum)
                    specials = specials - 1
                End Select
            End If
     End Select
     TotalLength = TotalLength - 1
Me.txtPassword = pwd
End Sub

Private Function RandNum(Lower As Integer, Upper As Integer) As Integer
'Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
RandNum = Int((Upper - Lower + 1) * Rnd + Lower)
End Function

Open in new window


Author Comment

ID: 40318359
i salute to everyone who responded with their solutions and suggestions, I can only gape in awe.  I wish I had that sort of logical mind.
LVL 26

Assisted Solution

Nick67 earned 1200 total points
ID: 40318362
Does it work for you?
I figured that using Chr() would be the most sensible way to go.
No ugly strings to mess with, just a random number generating function and some of my favorite thing

Select Case True

Just about the handiest thing in the VBA universe

Author Comment

ID: 40318369
in response to Nick67, I tried Scott's suggestion, as it was not too complicated for me to implement.   Thank u.  More on this Q in a new Q.

Author Closing Comment

ID: 40318370
Well done everyone for your suggestions and ideas,  I now can build a robust password for my sites where I log onto.  Thank u again.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses
Course of the Month12 days, 5 hours left to enroll

752 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