Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Using VBA in Access 2007 to generate passwords

    Hi, I am attempting to do the following:

    In a form, I have created a command button, the goal behind it being when clicked it will (Using vba) generate a random strong password, displayed in a textbox that the user can then cut, and paste into the appropriate field on the form.

    I'm having a bit of trouble working out how to fit it in however, I'm getting constant expected statement "End Sub" errors.

    I'm not to great at this stuff so any help is appreciated,

    My code somehow looks pretty sloppy but this is what I have so far (Im sure most of it is wrong


    Private Sub Command14_Click()

    Function CreatePassword(nLen As Long) As String
    Dim nRnd As Double
    Dim sPW As String
    Dim bAdd As Boolean

    Randomize
    While Len(sPW) < nLen
    nRnd = Int(Rnd * 75) + 48
    bAdd = False
    Select Case nRnd
    Case 48 To 57 ' Numeric characters
    bAdd = True
    Case 65 To 90 ' Upper case characters
    bAdd = True
    'Case 97 To 122 ' Lower case characters
    ' bAdd = True
    Case Else ' Useless characters
    bAdd = False
    End Select

    If bAdd Then
    sPW = sPW & Chr(nRnd)
    If (Len(sPW) = nLen - 1) And (Asc(Left$(sPW, 1)) < 65) Then
    sPW = Right$(sPW, Len(sPW) - 1)
    End If
    End If
    Wend

    CreatePassword = sPW
    End Function
    End Function
    End Sub

  2. #2
    Join Date
    May 2009
    Posts
    258
    The problem is that you are trying to nest a function within a sub, which will not work in VBA. It's giving you an error because it can't find the End Sub for the Command14_Click sub. You also have an extra End Function statement.

    Regards,

    Ax

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Quote Originally Posted by Ax238
    The problem is that you are trying to nest a function within a sub, which will not work in VBA. It's giving you an error because it can't find the End Sub for the Command14_Click sub. You also have an extra End Function statement.

    Regards,

    Ax
    So how would I go about fixing this, It doesn't seem to let me take out the sub part, i can replace it with function, remove the extra end function statement and I get an error

    "Procedure declaration doesn't match description of event or procedure having the same name"

  4. #4
    Join Date
    Jul 2009
    Posts
    7
    I apologise for double posting, I'd just like to know if what I want done is feasible and if so how =\

  5. #5
    Join Date
    May 2009
    Posts
    258
    This is fine by itself:
    Code:
    Function CreatePassword(nLen As Long) As String
    Dim nRnd As Double
    Dim sPW As String
    Dim bAdd As Boolean
    
    Randomize
    While Len(sPW) < nLen
    nRnd = Int(Rnd * 75) + 48
    bAdd = False
    Select Case nRnd
    Case 48 To 57 ' Numeric characters
    bAdd = True
    Case 65 To 90 ' Upper case characters
    bAdd = True
    'Case 97 To 122 ' Lower case characters
    ' bAdd = True
    Case Else ' Useless characters
    bAdd = False
    End Select
    
    If bAdd Then
    sPW = sPW & Chr(nRnd)
    If (Len(sPW) = nLen - 1) And (Asc(Left$(sPW, 1)) < 65) Then
    sPW = Right$(sPW, Len(sPW) - 1)
    End If
    End If
    Wend
    
    CreatePassword = sPW
    End Function
    You will need to call to that function from within here:
    Code:
    Private Sub Command14_Click()
    ' Call the function here
    End Sub
    Ax

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    Thank you, if I wanted to have the output of the code (the password) displayed in a text box to allow copy/paste is there a way to add this into the code?

  7. #7
    Join Date
    May 2009
    Posts
    258
    Yes, you'd handle that using the return value from the call to the CreatePassword function.

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    I've never actually done a call function code before, and the guides im looking at online aren't being very helpful.

  9. #9
    Join Date
    Jul 2009
    Posts
    7
    Ok so far I've gotten this
    Private Sub Command14_Click()
    Call CreatePassword ("nLen As Long")


    End Sub
    Function CreatePassword(nLen As Long) As String
    Dim nRnd As Double
    Dim sPW As String
    Dim bAdd As Boolean

    Randomize
    While Len(sPW) < nLen
    nRnd = Int(Rnd * 75) + 48
    bAdd = False
    Select Case nRnd
    Case 48 To 57 ' Numeric characters
    bAdd = True
    Case 65 To 90 ' Upper case characters
    bAdd = True
    'Case 97 To 122 ' Lower case characters
    ' bAdd = True
    Case Else ' Useless characters
    bAdd = False
    End Select

    If bAdd Then
    sPW = sPW & Chr(nRnd)
    If (Len(sPW) = nLen - 1) And (Asc(Left$(sPW, 1)) < 65) Then
    sPW = Right$(sPW, Len(sPW) - 1)
    End If
    End If
    Wend

    CreatePassword = sPW
    End Function
    Running it gives me this error
    Type mismatch (Error 13)

  10. #10
    Join Date
    May 2009
    Posts
    258
    I'm guessing you didn't come up with the CreatePassword function yourself. Is this schoolwork?
    Last edited by Ax238; 07-14-09 at 23:24.

  11. #11
    Join Date
    Jul 2009
    Posts
    7
    Not quite, our database guy quit and until we can hire a new guy I got stuck with the job.

    If it was schoolwork I wouldn't be asking for help on a forum where it would become plagiarism.

    My main issue is I have very little experience with vba, that's why I am posting here, all I have are basically this guys old notes and whatnot.

    Edited for clarity/punctuation.
    Last edited by BrianWg; 07-14-09 at 23:25.

  12. #12
    Join Date
    May 2009
    Posts
    258
    OK, just thought I'd ask. Well the function at hand takes a variable (nLen), which is defined as a long integer. This just means it can handle big numbers. You have to figure out how big the password needs to be and put that length in, instead of "nLen As Long".

    VBA Tips: Build Custom Functions for Your Access Applications

    Regards,

    Ax

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •