[hwinceFL]

This not really a Quick Answer; it's more a Slow Question.Like Lisa, I need help with VBA (rather than VB) coding since I'm using Access 2007 under Win 7 Home. I have copied (modified slightly to fit my project) Scottgem's login security procedure and have the first part working fine. However, I run almost immediately into a coding error.On the Main Menu, I have modified the third tab/button to be Volunteer Access. Then, I have copied Main Menu, pasted it to prepare a new frmVolunteerMenu. Now, I am trying to modify the first tab on this form to allow the Volunteer to perform a task.Here's the OnClick event procedure coding for Volunteer Access button:Private Sub cmdVol_Click() If DLookup("[AccessLevelID]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser) = 7 Then MsgBox "You have Volunteer access!", vbOKOnly ' Open Volunteer Activity Form DoCmd.OpenForm "frmVolunteerMenu", , , "[UserID] =" & Me.cboUser Me.Visible = False Else MsgBox "You do not have Volunteer access!", vbOKOnly End IfEnd SubWhen I execute this procedure, clicking on Volunteer Access button, I get a "Compile Error: Method or data member not found" highlighting in blue "Me.cboUser" after "Me". Clearly, I need to know more about VBA. Thus, my question/plea: will someone point me to a resource whereby I may begin to develop skill in understanding/using VBA coding?

[ScottGem]

You can purchase my book, http://www.amazon.com/Microsoft-Offi.../dp/0789737310

First, you have to make sure the login form remains open, but hidden. Did you change the name of the form or the control where the volunteer enters their name?

Do you want to attach a copy of your DB so I can look at it?

[hwinceFL]

You can purchase my book, http://www.amazon.com/Microsoft-Offi.../dp/0789737310

First, you have to make sure the login form remains open, but hidden. Did you change the name of the form or the control where the volunteer enters their name?

Do you want to attach a copy of your DB so I can look at it?

Thanks, Scott. I have acquired your book and will be downloading it to my iPad and iPhone shortly. I would like to send you my DB, but when I try to attach it, I get an indication that this an invalid file.

I can't say for certain, but I don't believe I changed anything regarding the Login form, except to add a descriptive label. Also, you will note that the security part of the login is working fine, i.e. I open the Main Menu just fine. Further, I copied the original Main Menu form and re-named the copy as frmVolunteerMenu. Then, I have added the coding attempting to open the frmVolunteerMenu and this is where I am encountering the error.

I don't know what happened to the format in my earlier message, but I appreciate your attention to my issue.

[ScottGem]

You have to Zip it first. You can't upload an Accdb. You may also need to strip out some parts to get it under the minimum size.

The error are you getting it on the logn form or the menu. If its on the men, change Me.cboUser to Forms!frmLogin!cboUser.

[hwinceFL]

You have to Zip it first. You can't upload an Accdb. You may also need to strip out some parts to get it under the minimum size.

The error are you getting it on the logn form or the menu. If its on the men, change Me.cboUser to Forms!frmLogin!cboUser.

Thanks. The problem was in the Menu coding and the suggested change corrected that. I don't see the need to upload my DB at this point, but what is the maximum size for an attached ZIP file?

[hwinceFL]

I have another question regarding VBA coding. I have set up a For loop as follows:
Dim ICount As Integer
ICount = 1
For ICount 1 to 8
[Intermediate coding without error]
Next ICount
Here I get a "Compile error: Next without For". What I understand from my reference, it seems this should work. I would appreciate help. If more detail is needed, I will be glad to attach my zipped DB.

[ScottGem]

Looks fine to me. I suspect, that the intermediate coding is what is causing it. I would like to see the entire code snippet.

[hwinceFL]

Looks fine to me. I suspect, that the intermediate coding is what is causing it. I would like to see the entire code snippet.

Hello Scott, Thanks for your response. Obviously, I should not, at my level of skill, state that my coding is "without error."
My effort relates to my attempt to modify your Login Security procedure at the point of frmPasswordChange. Here I am trying to require an entry of a new password of maximum 8 alphanumeric characters with at least one upper-case and one number. I am trying to test the txtpassword in the AfterUpdate event procedure with the following coding, which is where I get the previously-described Compile Error. I appreciate your consideration and clarification of my effort.
Option Compare Database
Option Explicit
Private Sub txtConfPW_AfterUpdate()
' Verify new password match
If Me.txtPassword = Me.txtConfPW Then
MsgBox "Password updated!", vbOKOnly
Me.PWReset = False
' The following is intended to record the effective date of PW change.
' Dim NewDate As Date
' MsgBox "The new password effective date is " & [NewDate]
' Me.PWDate = NewDate
DoCmd.Close acForm, "frmPasswordChange"
Else
MsgBox "Passwords don't match; please re-enter!", vbOKOnly
Me.txtPassword.SetFocus
End If
End Sub
Private Sub txtPassword_AfterUpdate()
' Test new password for valid format
If Len(Me.txtPassword) < 2 Then
Err_PW_Num:
MsgBox "New password must be 2 to 8 alphanumeric; re-enter", vbOKOnly
CleanUp:
' Me.cboUser = Null Re-set Select User box
Me.txtPassword = Null
Me.txtPassword.SetFocus
ElseIf Len(Me.txtPassword) > 8 Then
Go to Err_PW_Num
End If
Dim NumCheck As Integer
Dim CapCheck As Integer
Dim ICount As Integer
Dim intChar As Integer
NumCheck = 0
CapCheck = 0
ICount = 0
intChar = 0
For ICount = 1 To Len(Me.txtPassword)
' For ICount = 1 To 8
intChar = Asc(Mid(Me.txtPassword, ICount, 1))
Select Case intChar
Case 48 - 57 'Select a number
NumCheck = NumCheck + 1
Go to LoopOn
Case 65 - 90 'Select an upper-case letter
CapCheck = CapCheck + 1
Go to LoopOn
Case 96 - 122 'Select lower-case letter
Go to LoopOn
' Case Is < 65 Or 91 - 96 Or 123 - 267
MsgBox "Non-alphanumeric characters are invalid.", vbOKOnly
Go to CleanUp
LoopOn:
Next ICount
End Select
If NumCheck = 0 Then
MsgBox "Password must contain at least one number."
Go to CleanUp
' End If
ElseIf CapCheck = 0 Then
MsgBox "Password must contain at least one uppercase letter."
Go to CleanUp
End If
End Sub

[hwinceFL]

Hello Scott, Thanks for your response. Obviously, I should not, at my level of skill, state that my coding is "without error."
My effort relates to my attempt to modify your Login Security procedure at the point of frmPasswordChange. Here I am trying to require an entry of a new password of maximum 8 alphanumeric characters with at least one upper-case and one number. I am trying to test the txtpassword in the AfterUpdate event procedure with the following coding, which is where I get the previously-described Compile Error. I appreciate your consideration and clarification of my effort.
Option Compare Database
Option Explicit
Private Sub txtConfPW_AfterUpdate()
' Verify new password match
If Me.txtPassword = Me.txtConfPW Then
MsgBox "Password updated!", vbOKOnly
Me.PWReset = False
' The following is intended to record the effective date of PW change.
' Dim NewDate As Date
' MsgBox "The new password effective date is " & [NewDate]
' Me.PWDate = NewDate
DoCmd.Close acForm, "frmPasswordChange"
Else
MsgBox "Passwords don't match; please re-enter!", vbOKOnly
Me.txtPassword.SetFocus
End If
End Sub
Private Sub txtPassword_AfterUpdate()
' Test new password for valid format
If Len(Me.txtPassword) < 2 Then
Err_PW_Num:
MsgBox "New password must be 2 to 8 alphanumeric; re-enter", vbOKOnly
CleanUp:
' Me.cboUser = Null Re-set Select User box
Me.txtPassword = Null
Me.txtPassword.SetFocus
ElseIf Len(Me.txtPassword) > 8 Then
Go to Err_PW_Num
End If
Dim NumCheck As Integer
Dim CapCheck As Integer
Dim ICount As Integer
Dim intChar As Integer
NumCheck = 0
CapCheck = 0
ICount = 0
intChar = 0
For ICount = 1 To Len(Me.txtPassword)
' For ICount = 1 To 8
intChar = Asc(Mid(Me.txtPassword, ICount, 1))
Select Case intChar
Case 48 - 57 'Select a number
NumCheck = NumCheck + 1
Go to LoopOn
Case 65 - 90 'Select an upper-case letter
CapCheck = CapCheck + 1
Go to LoopOn
Case 96 - 122 'Select lower-case letter
Go to LoopOn
' Case Is < 65 Or 91 - 96 Or 123 - 267
MsgBox "Non-alphanumeric characters are invalid.", vbOKOnly
Go to CleanUp
LoopOn:
Next ICount
End Select
If NumCheck = 0 Then
MsgBox "Password must contain at least one number."
Go to CleanUp
' End If
ElseIf CapCheck = 0 Then
MsgBox "Password must contain at least one uppercase letter."
Go to CleanUp
End If
End Sub

I'm not sure what happened to the formatting above, but all of the Go to are with a capital T included.

[ScottGem]

OK, what I would do is remove the check for strong password to a global module. So your code to change the password would be like so:

Private Sub txtPassword_AfterUpdate()

If StrongPassword(Me.txtPassword) Then
MsgBox "Password changed1"
Else
MsgBox "Password must be 2-8 characters with at least 1 number and 1 Uppercase characters!"
Me.txtPassword.SetFocus
End If
End Sub

then create your function to test the password (StrongPassword)

I would loop through each character and if you find a number increment a variable intNumber by 1 and if you find an upper case do the same with another int variable.

After the loop is complete test to make sure both of those variable are not 0. If either one is zero, then return false.

[hwinceFL]

OK, what I would do is remove the check for strong password to a global module. So your code to change the password would be like so:

Private Sub txtPassword_AfterUpdate()

If StrongPassword(Me.txtPassword) Then
MsgBox "Password changed1"
Else
MsgBox "Password must be 2-8 characters with at least 1 number and 1 Uppercase characters!"
Me.txtPassword.SetFocus
End If
End Sub

then create your function to test the password (StrongPassword)

I would loop through each character and if you find a number increment a variable intNumber by 1 and if you find an upper case do the same with another int variable.

After the loop is complete test to make sure both of those variable are not 0. If either one is zero, then return false.

I have prepared my module as you suggest. But, I'm having trouble debugging it. Trying to follow you guidance in your book, I open the coding, place the cursor at the first If statement and select F5. This brings up the panel shown in the attached screenshot. The referenced coding "macro" is a sample training code. I would appreciate help in how I get the Debug routine to work. Incidentally, neither MyCode nor StrongPassword are reflected as "macros" in the listed objects in the side panel of my project.

[ScottGem]

The way to debug is to put a break point on the left hand border by just clicking on the border next to an executable statement. Then execute the code the way you normally would from your form. When execution reaches the breakpoint, it stops and the VBE opens. From there, you can use F8 to step through the code executing each line. You also need to keep the Locals Window open, so you can see the value of each variable as the code executes.

As for the macro message, do you possibly have a macro the same name as your procedure?

Another point. I would make Strong Password a function that returns a Boolean:

Public Function StrongPassword(strPassword AS String) As Boolean

In the beginning of the Code add:

StrongPassword = False

At the end, if the password passes the test then set it to True is not set it to False. Then call the function this way:

If StrongPassword(Me.txtPassword) Then
accept password
Else
MsgBox "Password not strong enough!"
etc.
End If

But frankly, Access is just not that secure. I really don't see the need to require a strong password as there are other ways to get around VBA security. What you are doing makes for a good learning experience however.

[hwinceFL]

The way to debug is to put a break point on the left hand border by just clicking on the border next to an executable statement. Then execute the code the way you normally would from your form. When execution reaches the breakpoint, it stops and the VBE opens. From there, you can use F8 to step through the code executing each line. You also need to keep the Locals Window open, so you can see the value of each variable as the code executes.

As for the macro message, do you possibly have a macro the same name as your procedure?

Another point. I would make Strong Password a function that returns a Boolean:

Public Function StrongPassword(strPassword AS String) As Boolean

In the beginning of the Code add:

StrongPassword = False

At the end, if the password passes the test then set it to True is not set it to False. Then call the function this way:

If StrongPassword(Me.txtPassword) Then
accept password
Else
MsgBox "Password not strong enough!"
etc.
End If

But frankly, Access is just not that secure. I really don't see the need to require a strong password as there are other ways to get around VBA security. What you are doing makes for a good learning experience however.

Thanks, Scott, for your very helpful guidance; however, I'm still hitting a snag. I have updated my StrongPassword coding as suggested and prepared it for Debug by setting a breakpoint at the first executable statement in the function coding. Thus, when I execute my Login process and select a User, if there is no need to update the password, the Login proceeds to open the Main Menu. However, when a change is needed, I go to another form requesting a new password entry with a confirmation entry. At this point, I call the If StrongPassword(Me.txtPassword) function. Here I get a "Compile error: Expected variable or procedure, not module". Here, the StrongPassword name is highlighted in blue. Access Help explains: "There is no variable or procedure in the current scope, but there is a module by this name. The error has the following cause and solution." Etc, Etc. The processing ends at this point and so I never get to the breakpoint in debugging the StrongPassword function. In case you want to look at my DB, I have attached it for you. Here you will see the Login form is where I start. I will appreciate your help.

[ScottGem]

Never name a module the same as the proc or function name. I generally have a global module named modUDF (for User Defined Functions) and I put most of my functions and procs in there. That is the source of your error.

[hwinceFL]

Never name a module the same as the proc or function name. I generally have a global module named modUDF (for User Defined Functions) and I put most of my functions and procs in there. That is the source of your error.

Thanks for your patience. I have tried to follow some of your guidance in your book and have prepared my modUDF as shown in the attached screenshot. However, I am able to debug (insert cursor; select F5) in either ModLevel1 or ModLevel2 completing the debug by "printing" the appropriate message. However, when I try the same process for the StrongPassword function, I get the "error" panel regarding macros, without any listed. Further, there is no reference to StrongPassword. Of course, I do have some macros in my object list, but these are not shown, even though they're not what I'm looking for. How do I debug this StrongPassword function before going any further?

[hwinceFL]

Thanks for your patience. I have tried to follow some of your guidance in your book and have prepared my modUDF as shown in the attached screenshot. However, I am able to debug (insert cursor; select F5) in either ModLevel1 or ModLevel2 completing the debug by "printing" the appropriate message. However, when I try the same process for the StrongPassword function, I get the "error" panel regarding macros, without any listed. Further, there is no reference to StrongPassword. Of course, I do have some macros in my object list, but these are not shown, even though they're not what I'm looking for. How do I debug this StrongPassword function before going any further?

Is there anything further I need to provide in support of my request for help in solving this macro error message blocking my attempt to debug my StrongPassword function?

[hwinceFL]

Is there anything further I need to provide in support of my request for help in solving this macro error message blocking my attempt to debug my StrongPassword function?

Hello Scott,
May I continue to seek your help in trying to get my StrongPassword function to work? I have made some changes from my previous version as follows:
Public Function StrongPassword(strPassword As String) As Boolean
StrongPassword = False
' Test strPassword for valid format
' Length must be 2-8
' Stop
If Len(strPassword) < 2 Then
Go to NotStrong
ElseIf Len(strPassword) > 8 Then
Go to NotStrong
Else
MsgBox "The number of characters in password is " & Len(strPassword), vbOKOnly
' Length valid; continue to test for strength
' Strong password must be alphanumeric only; at least 1 uppercase & 1 number
Dim NumCheck As Integer
Dim CapCheck As Integer
Dim intChar As Long
Dim ICount As Integer
NumCheck = 0
CapCheck = 0
intChar = 0
ICount = 0
' Check validity of each character
For ICount = 1 To Len(strPassword)
intChar = Asc(Mid(strPassword, ICount, 1))
MsgBox "The character value is " & intChar, vbOKOnly
' This is a debugging tool
MsgBox "We are here in the testing process.", vbOKOnly
' Select Case intChar
If intChar > 47 Then
ElseIf intChar < 58 Then
Number:
' Case 48 - 57 ' Select a number
MsgBox "The character value is " & intChar, vbOKOnly
NumCheck = NumCheck + 1
MsgBox "Number count is " & NumCheck, vbOKOnly
Go to KeepGoing
ElseIf intChar > 64 Then
ElseIf intChar < 91 Then
UpperCase:
' Case 65 - 90 ' Select an uppercase letter
CapCheck = CapCheck + 1
MsgBox "The uppercase value is " & CapCheck, vbOKOnly
Go to KeepGoing
' Case 97 - 122 ' Select a lowercase letter
ElseIf intChar > 96 Then
ElseIf intChar < 123 Then
' Case Is < 48 Or 91 - 96 Or 123 - 267
Go to KeepGoing
ElseIf intChar < 48 Then
ElseIf intChar > 90 Then
ElseIf intChar < 97 Then
ElseIf intChar > 122 Then
MsgBox "Non-alphanumeric characters are invalid.", vbOKOnly
Go to NotStrong ' Abort Function
End If
KeepGoing:
' End Select
Next
MsgBox "Capital count is " & CapCheck & " & number count is " & NumCheck & " & loop count is " & ICount, vbOKOnly
If NumCheck = 0 Then
Go to NotStrong
ElseIf CapCheck = 0 Then
Go to NotStrong
End If
strPassword = True
Go to Finish
NotStrong:
MsgBox "Invalid; Strong password must be 2-8 characters of at least 1 capital letter and 1 number.", vbOKOnly, vbCritical
strPassword = False
End If
Finish:
End Function

You may need to refer to my previously submitted DB, but hopefully I can explain sufficiently my process.
First, I open frmLogin and select my record from tblUser. Here my record is set to require an updated password. Therefore, when I enter my current password, this opens frmPasswordChange. Here, I enter "Henry9" as the new password and when I tab to the "confirm" box, this invokes the StrongPassword function. Here I readily confirm that the new password containing 6 characters is valid and I proceed to test for strength. You will see that I have not been able to get the Select Case function to work as I would expect, so I have comment that out. Then, I have attempted to use "If" testing. Thus, I have confirmed that the Asc conversion of strPassword results in intChar values of 72, 101, 110, 114, 121 and 57. So I would expect the first test to fail/bypass the If test for a number value and branch to the following ElseIf test. Here I would expect it to test greater than 64 and thus ElseIf to be less than 91. Therefore, I would expect to "Then" go to CapCheck to be incremented. Of course, I continue the For loop for all the characters. However, when this is completed, I show NumCheck and CapCheck to have remained 0 at and the loop count to be 7. Processing then continue to reflect "an invalid password" and end the function with StrongPassword = False.

I would appreciate your guidance on where my logic is failing in the If testing process. I would really prefer to use the Select Case procedure, since it appears so much simpler in your blog example, but I am completely unfamiliar with that function.

I really appreciate your patience during my working to go through my learning process of VBA coding. Your book has been very helpful, but I'm still struggling.

[hwinceFL]

OK, I have corrected the coding in my StrongPassword function and it is now working as expected. But, either Scottgem is unavailable or simply ignoring my earlier requests. In any case, therefore, I'm asking for anyone's help with this question. My StrongPassword requires 2-8 alphanumeric characters with at least one uppercase letter and one number. Thus, if I test entering a new password, "Henry9", the StrongPassword function returns a boolean True . However, my Login form will accept "HENRY9" as valid. Is there a setting in Access I'm missing to require a password to be case-sensitive?