How to Password Protect a Checkbox? 2

[Buckar00B0nzai]

I have a form that tracks project status, and the individuals who use the form are not supposed to move from phsae 1 to phase 2 (all the way through phase 5) without approval from management. I have a checkbox for each phase (I-V) listed on the bottom of the form, and would like these to be password protected (for management only). If someone checks (or "un-checks) the box and enters the correct password, the check (or "un-check") should stay. If they enter the incorrect password, I would like their Check (or "un-check") to be reversed to the original setting. I am a novice and am wondering if anyone can help. Here is the code that I have so far in the "On Click" event for the checkbox named "Phase I".

Private Sub CtlPhase_I_Click()
If InputBox("Enter Password to Delete Record") <> "Password" Then
Exit Sub
Else
GoTo Err_CtlPhase_I_Click
End If

On Error GoTo Err_CtlPhase_I_Click

Exit_CtlPhase_I_Click:
Exit Sub

Err_CtlPhase_I_Click:

If Me.CtlPhase_I.Value = -1 Then
Me.CtlPhase_I.Value = 0
Else
Me.CtlPhase_I.Value = -1

End If

End Sub

Please help.

Thanks in advance.

 

[kjv1611]

First thing I would suggest is to not send your code direct to the error handler just b/c someone enters the correct password. That just seems like a waste of an error handler to me. Maybe I'm wrong, but I think that's off to a bad start...

Maybe this will be a little better:

Private Sub CtlPhase_I_Click()
On Error GoTo Err_CtlPhase_I_Click

    If InputBox("Enter Password to Delete Record") <> "Password" Then
        GoTo Exit_CtlPhase_I_Click
    Else
        If Me.CtlPhase_I.Value = -1 Then
            Me.CtlPhase_I.Value = 0
        Else
            Me.CtlPhase_I.Value = -1
        End If
    End If
    
Exit_CtlPhase_I_Click:
    Exit Sub

Err_CtlPhase_I_Click:
    MsgBox "An Error Occured! " & Chr(13) & _
        "Error Number = " & Err.Number & Chr(13) & _
        "Error Description = " & Err.Description
    Resume Exit_CtlPhase_I_Click

End Sub

Then we can start looking at the specific issue at hand...

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Also, you can get the code working in a Private Sub, but I'd suggest at least one Public Sub Procedure or a combination of a Public Sub Procedure and Public Function which can be called by each of your buttons, rather than typing the code 5 times.. But keeping it in the on_click event initially won't be bad for testing, I suppose..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Other than that, I think we need more information:

1. What precisely do you want to happen after each successful password entry? Surely not just checking/unchecking a check box.

2. You're not storing the password(s) just in the code are you? It'd be best to at least store them in a table, and there may be some better method, I'm not sure.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Buckar00B0nzai]

Thanks, kjv1611.

After successful password entry, I want the box to remain "checked" or "unchecked". Basically - I don't want just anyone to be able to approve the completion of a phase.

Can you walk me through making this a Public Sub Procedure so that I can use it on all 5 phases?

Its ok to store the password in code, but I would also like to know how to store it in a table.

Thanks again.

 

[Buckar00B0nzai]

Also - when I try the code you recommended I get an error message ("Compile Error: Method or data member not found").

Any ideas?

 

[kjv1611]

Basically, all you do to make it Public rather than Private is put it in an existing non-form-based Module (or create a new one, and put it in the new module).

Then change "Private Sub" to "Public Sub"

Then if you had any references, such as Me., change those to reference the form as an external reference...

So, what I posted last would now be:

[BLUE][b]Public[/b][/BLUE] Sub CtlPhase_I_Click()
On Error GoTo Err_CtlPhase_I_Click

    If InputBox("Enter Password to Delete Record") <> "Password" Then
        GoTo Exit_CtlPhase_I_Click
    Else
        If [BLUE][b]Forms!frmMyFormName.[/b][/BLUE]CtlPhase_I.Value = -1 Then
            [BLUE][b]Forms!frmMyFormName.[/b][/BLUE]CtlPhase_I.Value = 0
        Else
            [BLUE][b]Forms!frmMyFormName.[/b][/BLUE]CtlPhase_I.Value = -1
        End If
    End If
    
Exit_CtlPhase_I_Click:
    Exit Sub

Err_CtlPhase_I_Click:
    MsgBox "An Error Occured! " & Chr(13) & _
        "Error Number = " & Err.Number & Chr(13) & _
        "Error Description = " & Err.Description
    Resume Exit_CtlPhase_I_Click

End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Oh... one other change... You'd definitely use a diff procedure name....

Public Sub ChangePhase()
On Error GoTo Err_CtlPhase_I_Click

    If InputBox("Enter Password to Delete Record") <> "Password" Then
        GoTo Exit_CtlPhase_I_Click
    Else
        If Forms!frmMyFormName.CtlPhase_I.Value = -1 Then
            Forms!frmMyFormName.CtlPhase_I.Value = 0
        Else
            Forms!frmMyFormName.CtlPhase_I.Value = -1
        End If
    End If
    
Exit_CtlPhase_I_Click:
    Exit Sub

Err_CtlPhase_I_Click:
    MsgBox "An Error Occured! " & Chr(13) & _
        "Error Number = " & Err.Number & Chr(13) & _
        "Error Description = " & Err.Description
    Resume Exit_CtlPhase_I_Click

End Sub

Then you'd call it with each of your buttons with this one line of code:

Private Sub MyCheckbox_Click()
  ChangePhase
End Sub

And of course, you'll want to change the actual "1" to a variable so you could loop through the necessary controls/pieces...

--

"If to err is human, then I must be some kind of human!" -Me

 

[Buckar00B0nzai]

So I tried this:

Public Sub ChangePhase()
On Error GoTo Err_CtlPhase_I_Click

If InputBox("Enter Password to Delete Record") <> "Password" Then
GoTo Exit_CtlPhase_I_Click
Else
If Forms!Frm_Edit.CtlPhase_I.Value = True Then
Forms!Frm_Edit.CtlPhase_I.Value = False
Else
Forms!Frm_Edit.CtlPhase_I.Value = True
End If
End If

Exit_CtlPhase_I_Click:
Exit Sub

Err_CtlPhase_I_Click:
MsgBox "An Error Occured! " & Chr(13) & _
"Error Number = " & Err.Number & Chr(13) & _
"Error Description = " & Err.Description
Resume Exit_CtlPhase_I_Click

End Sub

And the error message comes up if I put in the correct password ("Password") but still allows the change to the check box. If I enter the wrong password (e.g. ggg) it allows the change to the check box.

Woah.

 

[Buckar00B0nzai]

I would like the box to "uncheck" if the password is incorrect.

 

[Buckar00B0nzai]

Or "check" - basically undo whatever the individual with the incorrect password did.

 

[kjv1611]

Hmm, let's try a slight rewrite... including fixing the remaining naming conventions from it being a Private Sub...

Public Sub ChangePhase(strPhaseNumber As String)
On Error GoTo Err_ChangePhase

[GREEN]'Work this into the procedure for swapping between which button you clicked.[/GREEN]
  Dim ctl As Control
  Dim frm As Form

  Set frm = Forms!frmMyFormName

    If InputBox("Enter Password to Delete Record") =  "Password" Then
        If frm.CtlPhase_I.Value = True Then
            frm.CtlPhase_I.Value = False
        Else
            frm.CtlPhase_I.Value = True
        End If
    Else
        GoTo Exit_ChangePhase
    End If
    
Exit_ChangePhase:
    Set ctl = Nothing
    Set frm = Nothing
    Exit Sub

Err_ChangePhase:
    MsgBox "An Error Occured! " & Chr(13) & _
        "Error Number = " & Err.Number & Chr(13) & _
        "Error Description = " & Err.Description
    Resume Exit_CtlPhase_I_Click

End Sub

And then call it with:

Private Sub Phase_I_Click()
  PhaseChange("I")
End Sub

....

Private Sub Phase_II_Click()
  PhaseChange("II")
End Sub

Well, that's some of the necessary changes, I think. Just to be sure, how are you referring to your controls for each Phase? I'm assuming you're using Roman Numerals... so the control names are something like:

..._I_...
..._II_...
..._III_...
..._IV_...
..._V_...
..._VI_...

?

That will make a difference in the code.. so I need to be sure.. (also, I probably won't be able to post anything additional back on this until tomorrow)..

Post back any specific errors you are getting, and take a look at the changes, above. There is more to be done, so it isn't going to work right out of the gate with the supplied code... try to work on it, and perhaps someone else will be able to assist before I can get back to it..

--

"If to err is human, then I must be some kind of human!" -Me

 

[Buckar00B0nzai]

Thanks again. Have a star for your effort and help.

I will continue to work on it - but please check back in when you can.

Thanks.

 

[Buckar00B0nzai]

OK - I tried this:

The name if the control (check box) is CtlPhase_I.

Public Sub ChangePhase(strPhaseNumber As String)
On Error GoTo Err_ChangePhase

'Work this into the procedure for swapping between which button you clicked.
Dim ctl As Control
Dim frm As Form

Set frm = Forms!Frm_Edit

If InputBox("Enter Password to Delete Record") = "Password" Then
If frm.CtlPhase_I.Value = True Then
frm.CtlPhase_I.Value = False
Else
frm.CtlPhase_I.Value = True
End If
Else
GoTo Exit_ChangePhase
End If

Exit_ChangePhase:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

Err_ChangePhase:
MsgBox "An Error Occured! " & Chr(13) & _
"Error Number = " & Err.Number & Chr(13) & _
"Error Description = " & Err.Description
Resume Exit_CtlPhase_I_Click

End Sub

And I am calling the procedure with this (on click event):

Private Sub CtlPhase_I_Click()
ChangePhase ("I")

End Sub

I get an error message that states: "Compile Error: Label Not Defined" and it highlights the last line of the public module ("Resume Exit_CtlPhase_I_Click").

Hopefully this helps to clarify the issues.

Thanks.

 

[kjv1611]

Ok, that error is pretty strait forward. See, we changed the name of the Exit portion, but didn't change that reference to it.

So change:

Resume Exit_CtlPhase_I_Click

To:

Resume Exit_ChangePhase

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Why not using the strPhaseNumber parameter in your procedure ?
Tip:
With Forms!Frm_Edit.Controls(strPhaseNumber)
.Value = Not .Value
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, I meant this:

With Forms!Frm_Edit.Controls("CtlPhase_" & strPhaseNumber)
  .Value = Not .Value
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Once you've made the change, you'll get another error, since that error is actually coming from the error handler portion.

And actually, until you get the rest of it working, and you're debugging, you can disable the error handler, so that the debug window will highlight the errant code. To do that, just comment out the On Error.... line just under Public Sub. You comment it out with an apostrophe (single quote mark).

Also, you didn't answer this question:

Just to be sure, how are you referring to your controls for each Phase? I'm assuming you're using Roman Numerals..

Are you referring to each Phase with Roman Numerals? If so, it would be easier if you could change the Roman numerals to regular numbers. If we don't use any loops (which we may not need), then it may not be much different, but overall it is better in code this way... You can still Label the controls whatever you want, as far as what the user sees (so the label text can be whatever), but I'm talking about changing the actual name of each control.

So what I mean is actually change the names of your controls, etc, from CtlPhase_I to CtlPhase_1, for instance.. and CtlPhase_II to CtlPhase_2

Also, while we're looking at control names, you might want to look into regular naming conventions, such as the Reddick naming convention. Basically by following these guidelines, you can make your code easier to understand by yourself in the future, and by any others who may need to read it.

http://www.xoc.net/standards/

http://www.microapplications.com/reddick.aspx

For instance, I might would make these changes:

Before: After:
CtlPhase_I chkPhase_I
CtlPhase_1 chkPhase_1

And I'd personally remove the underscore as well. The reason is that when you use the events of each control, the VBA code will automatically add an underscore between the control name and the event...

So if we put all the suggestions together, instead of:

Private Sub CtlPhase_I_Click()
    ChangePhase ("I")
End Sub

We can now have:

Private Sub chkPhase1_Click()
    ChangePhase(1)
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Here's another naming conventions reference as well:

http://www.mvps.org/access/general/gen0012.htm

--

"If to err is human, then I must be some kind of human!" -Me

 

[Buckar00B0nzai]

PHV - Where would I insert this?

My current module looks like this:

Public Sub ChangePhase(strPhaseNumber As String)
On Error GoTo Err_ChangePhase

'Work this into the procedure for swapping between which button you clicked.
Dim ctl As Control
Dim frm As Form

Set frm = Forms!Frm_Edit

If InputBox("Enter Password to Delete Record") = "Password" Then
If frm.CtlPhase_I.Value = True Then
frm.CtlPhase_I.Value = False
Else
frm.CtlPhase_I.Value = True
End If
Else
GoTo Exit_ChangePhase
End If

Exit_ChangePhase:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

Err_ChangePhase:
MsgBox "An Error Occured! " & Chr(13) & _
"Error Number = " & Err.Number & Chr(13) & _
"Error Description = " & Err.Description
Resume Exit_ChangePhase

End Sub

Unfortunately - it is doing the exact opposite of my requirements. When I check the box (CtlPhase_I) and enter the correct password ("Password"), it unchecks the box or vice versa. When I check the box and enter the wrong password (e.g. ssss), it accepts the change.

Almost there...