Prevent "employee" from deleting some controls 4

[BvCF]

Have the following module that I had planned to not allow the end-user to delete data within the controls on my multi-tabbed form, frmTabbed. The employees are only able to access the "Review Accounts" page of the multi-tabbed form (TabCt10.Pages.Item(2)) and two other pages. There are about 50 controls on the "Review Accounts" page.

The only controls that the "employee" should be able to enter data into should be

Status (Allow additions and edits)
Reason (combo box) (Allow additons)
Amt Remitted (Allow additions and edits)
Date Remitted (Allow additions)
Comments (Allow additions, deletes and edits)

Other fields such as AcctNo, Name, and Balance should not allow for any additions, edits or deletions. But, it should allow the importation of data.

What modifications do I need to make that will allow the importation of data from Oracle via a passthru query but not allow the employee or the administrator to delete data from several controls?

Other Questions

1. Is the properties of "AllowEdits, AllowAdditions, and AllowDeletes available at the control level or only at the form level?
2. It appears that it would be preferable to lock all of the controls initially and individually unlock the 5 controls displayed above (Status, Reason, Amt Remitted, Date Remitted, and Comments). What are your thoughts?



Current code

Public Sub set_privs()

Forms!frmTabbed.AllowAdditions = False
Forms!frmTabbed.AllowDeletions = False
Forms!frmTabbed.AllowEdits = False
Forms!frmTabbed.AllowFilters = False

Select Case GBL_Access_Level
Case "M" ' manager
Me.TabCtl0.Pages.Item(1).Visible = True
Me.TabCtl0.Pages.Item(2).Visible = True
Me.TabCtl0.Pages.Item(3).Visible = True
Me.TabCtl0.Pages.Item(4).Visible = True

Forms!frmTabbed.AllowAdditions = True
Forms!frmTabbed.AllowDeletions = True
Forms!frmTabbed.AllowEdits = True
Forms!frmTabbed.Requery

Case "E" ' employee

Me.TabCtl0.Pages.Item(2).Visible = True
Me.TabCtl0.Pages.Item(4).Visible = True
Forms!frmTabbed.AllowAdditions = True
Forms!frmTabbed.AllowEdits = True

Forms!frmTabbed.Requery

End Select
End Sub


Currently, I am experimenting with the modification of teh code above to something like the following

Forms!frmTabbed.TabCtl0.Pages.Item(2).AllowAdditions = False
Forms!frmTabbed.TabCtl0.Pages.Item(2).AllowDeletions = False
Forms!frmTabbed.TabCtl0.Pages.Item(2).AllowEdits = False
Forms!frmTabbed.TabCtl0.Pages.Item(2).AllowFilters = False

 

[acent]

You might have fun with the enabled properties of the controls.

Or, you can tick off the end users by using the onLostFocus and programatically undoing their change. I always get a kick out of doing that, but it's not the most professional track to take.

Hope this works.

"If you say you can, or you say you can't, you're right!"
-- Henry Ford

 

[AccessGuruCarl]

Just a thought,

You can lock the controls add a variable to the TAG property of the controls. Then loop the controls and if the tag property is set to allow the user to edit, then you unlock those fields.

Here an example

    Dim frmForm As Form
    Set frmForm = Me
    Dim ctlControlToChange As Control
    
If GBL_Access_Level = "M" Then
    For Each ctlControlToChange In frmForm.Controls
        With ctlControlToChange
            Select Case .ControlType
                Case acTextBox
                    If .Tag = "Allow" then
                       .Locked = False
                    End If
                Case Else
            End Select
        End With
    Next ctlControlToChange
End If

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[BvCF]

Okay,

After tinkering around with this some more,

I have the following

Public Sub fLockAll()
'Will lock all controls
On Error GoTo fLockAll_Err
Dim I As Integer

For I = 0 To (Me.Controls.Count - 1)
Me.Controls.Item(I).Locked = True
Next

fLockAll_Exit:
Exit Sub
fLockAll_Err:
'If Err.Number = 438 Or Err.Number = 2448 Then
Resume Next
'End If
MsgBox Err.Description, , conAppName & " - Error Number " & Err.Number
Resume fLockAll_Exit
Resume
End Sub

Public Sub fUnLockAll()
'Will unlock all controls
On Error GoTo fUnLockAll_Err
Dim I As Integer

For I = 0 To (Me.Controls.Count - 1)
Me.Controls.Item(I).Locked = False
Next

fUnLockAll_Exit:
Exit Sub
fUnLockAll_Err:
'If Err.Number = 438 Or Err.Number = 2448 Then
Resume Next
'End If
MsgBox Err.Description, , conAppName & " - Error Number " & Err.Number
Resume fUnLockAll_Exit
Resume
End Sub

Following questions arise;

1. If I lock all controls on the form, then I don't believe that I can even sort the listbox using the option buttons.
2. If all controls on a form are locked, will I be able to append data from Oracle into the database?
3. It appears that the "Allow edits" and "Allow deletes" is a form-level property not control level.
4. It appears that a "reasonable" option is to specify that the controls on the form are set to "Allow Deletion = False", "Allow Edits = False", and then individually go to the 5 controls that the end-user will input data into and specify them to be "Allow Additions = True" and "Allow Edits = True."

Thoughts? Comments?

 

[BvCF]

acent,

Upon additional research, your suggestion was followed and all the controls that I don't want the end-user to modify are disabled.

Are you aware of a method to change the defaulted graying of disabled fields? For example, could I bold the label of a disabled field or change the backcolor of a disabled textbox?

Thanks for the insight.

 

[AccessGuruCarl]

BvCS

Are you aware of a method to change the defaulted graying of disabled fields? For example, could I bold the label of a disabled field or change the backcolor of a disabled textbox?

I do not think you can change the default qrayed out of a control that is disabled.
However, You should be able to do what you want using this.

You lock all field you do not want modified. Appears your already doing this! Add a variable to the Tag property for the controls. You are already specifying user rights so you check the rights then load the form based on user rights. See the above post!

If you want certain controls disabled - you can load those control names into an array, then check the names when the form is loading. Here is another sample, changing the controls on form load.

Private Sub Form_Load()
    Dim frmForm As Form
    Set frmForm = Me
    Dim ctlControlToChange As Control
    
    For Each ctlControlToChange In frmForm.Controls
        With ctlControlToChange
            Select Case .ControlType
                Case acTextBox
                    If .Tag = "Admin" Then
                        .Locked = False
                        .ForeColor = vbBlue
                        .BackColor = vbYellow
                        .Visible = True
                    End If
                Case acLabel
                    If .Tag = "Admin" Then
                        .FontBold = True
                        .ForeColor = vbBlue
                        .Visible = True
                    End If
                Case Else
            End Select
        End With
    Next ctlControlToChange

End Sub

here are just a few more control types
acCommandButton, acComboBox, acListBox

To view all available types highlight ControlType in the Select Case, then hit F1 to open help.

I use this code consistantly to lock and unlock controls based on user rights.

Hope this helps.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[randy700]

Instead of Disabling the controls, why not set the Enabled property to NO, as suggested by acent? This would prevent users from entering or changing the value in the control without any difference in appearance.


Randy

 

[acent]

I'm still a fan of the enabled property, however, another thought occured to me....

Why have text boxes? Instead of text boxes that by default accept data input, why not change them to labels which only display information?

When the form loads, or other event in which the data gets displayed, use the caption property of the label to display the information:

me.labelxyz.caption = 'code for the field of data

Just a thought...

"If you say you can, or you say you can't, you're right!"
-- Henry Ford

 

[BvCF]

What about unlocking all of the controls before the daily append query (where I am appending records from Oracle) and then locking specific controls after the running of the append query.

Each and every day, this process would occurr.

Adding a variable to the Tag property for specific controls sounds quite interesting.

The other interesting point is the loading of certain control names into a array and then checking the names when the form is loading.

I will definitely look into these two options and post back.

 

[AccessGuruCarl]

BvCF,

What about unlocking all of the controls before the daily append query (where I am appending records from Oracle) and then locking specific controls after the running of the append query.
[/Qoute]
How does this query get started?

If your kicking of the append from the locked form, then use a second form.
--> Kick-off append query
--> Close the form and
--> open the second form, which is just a message telling user to wait for updates to complete.
in the timer event, have if check to see if the query is open. when it reports back that it's closed, close this form and open your orginal form.

You can add a picturebox to this and make a generic progressbar to make it look a little more professional.

Let me know how you make out.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[MichaelRed]

Shouldn't need the timer control. action queries have "still executing" property, use that. No need to close the form for the update, just requery it when the action query is done. Don't lock all controls, as there is (then) no way to undo it (except via the same code which locks them). Arg against using all labels is that labels do not foll w record record changes, you have to code each change to reflect the record.



MichaelRed

 

[BvCF]

The append query is within a macro. I have the macro setup with Windows Scheduler to "kick off" every morning at 9:00 a.m.