Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Access Database Security Seminar
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   12 years ago

Learn how to properly secure your Access databases. Control and monitor everything your users can do in the database. Manage the complete flow of data into your database. Click here for a Complete Outline of the seminar and the topics covered.

 

Conditional Unlocking Upload Images   Link 
Joseph O'Malley 
25 days ago
Hi Richard. I've watched the seminar all the way though and my database is much more secure now! Quick Question for you regarding unlocking fields. Like in your code I have none of the fields lock in my form and subform if the user is an admin or manager. If the user is in group 3 (a regular employee I have only certain fields unlock for them. However, thinking through it I would like even those fields to be locked for these users once the "record complete" combo box is clicked as "complete". What would the best way to go about this? Right now my code is identical to your from this video.
Adam Schwanz
25 days ago
You can just use an if statement for that. Just say something if user is in group 3 and record complete combo = complete then lock everything else if user is in group 3 and record compete combo <> complete, then lock just a few.
Adam Schwanz
25 days ago
Use your own field names but

If IsUserInGroup(3) and Combo = "Complete" Then
  'Lock Fields Code
ElseIf IsUserInGroup(3) and Combo <> "Compelte" Then
  'Lock Few Fields Code
End If
Adam Schwanz
25 days ago
Minus the spelling error there, Complete not Compelte, my fingers are just hitting what they want today LOL.
Joseph O'Malley
25 days ago
I have updated my code but the last if statement looking at group 3 just seems to disregard my second condition and not lock based on whether the combo box is marked complete, any ideas where I'm going wrong?

Dim ctl As control

   If UserUserInGroup(1) OR Is UserInGroup(2) Then
   'nothing

Else

  For Each ctl In Me
   If ctl.ControlType = acTextbox Or ctl.ControlType = acComboBox Then
      ctl.Locked = True
      ctl.BackColor = RGB(239,242,247) 'grey
   End If
   Next
  For Each ctl In Forms!MainFormName.SubFormName.Form!
     If ctl.ControlType = acTextbox Or ctl.ControlType = acComboBox Then
      ctl.Locked = True
      ctl.BackColor = RGB(239,242,247) 'grey
   End If
   Next
End If


If IsUserInGroup(3) And Forms!MainFormName.SubFormName.Form!CompleteStatus <> "complete" Then
'only lock certain fields from main form and subform inside the main form
Control1.Locked = False
Forms!MainFormName.SubFormName.Form!Control2.Locked = False

End If
End Sub


Joseph O'Malley
25 days ago
Ohh Actually thinking about it more this is form-level locking and the condition I added to say if a certain field with a record is marked as "complete" would be record-level locking which this probably wouldn't do, as any of my 305 records could be marked as complete or not complete. Would Anyone know how to accomplish this?
Juan C Rivera
25 days ago
Hi all, I used the tech help video showing a progress bar on the % of the form completed (extended cut). then I used a checkbox (hidden) if true lock (allow edits no) the form.  very simple Richard walks you through every step with the bar. If you don't have extended cut maybe Richard can allow it once so you guys can see what your missing :)
V/r
Juan

Adam Schwanz
25 days ago
You want Forms!MainFormName!SubFormName.Form!Control
Adam Schwanz
25 days ago
What do you mean record level locking? Record locking is something very different - https://599cd.com/blog/display-article.asp?ID=1659

That user level locking is what you want to do if you just want to stop people from editing any records that are marked "complete" with the form. If you have users that are going into tables directly that can circumvent this, I'd advise to get them out and onto the form.
Joseph O'Malley
20 days ago
Hi All I hate to be a bother but does anyone know what still might be wrong with my code? My fields in the subform are still not locking if the record is marked as complete and now I'm getting an error saying "return without Go-Sub". If I remove the "And Forms!Service.ServiceSubform.Form!Status <> "Complete" part of the If IsUserInGroup(3) statement I don't get that error but that is the past bit of functionality I am trying to tie into my db.

Joseph O'Malley
20 days ago
Private Sub Form_Current()

    Dim ctl As Control

    If IsUserInGroup(1) Or IsUserInGroup(2) Then
    'do nothing; don't lock fields for db committe and managers
    Else

        For Each ctl In Me

            If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
                ctl.Locked = True
            End If
        Next
        
        For Each ctl In Forms!Service.ServiceSubform.Form!
         If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
                ctl.Locked = True
            End If
        Next
        
End If

'testers: unlocking testing fields only if the record is not marked as complete
If IsUserInGroup(3) And Forms!Service.ServiceSubform.Form!Status <> "Complete" Then
'from parent form field unlock - nothing right now

'from subform field unlock
Forms!Service.ServiceSubform.Form!OnOffTrack.Locked = False

End If

End Sub
Adam Schwanz
19 days ago
Did you try the fix I told you earlier a few posts ago?

You want Forms!MainFormName!SubFormName.Form!Control

Change Forms!Service.ServiceSubform.Form!Status
To Forms!Service!ServiceSubform.Form!Status
Adam Schwanz
19 days ago
Does For Each ctl In Forms!Service.ServiceSubform.Form!  work for you?

I would thing that would need to be
For Each ctl In Forms!Service!ServiceSubForm
Adam Schwanz
19 days ago
Actually, just lock the subform much simpler. Don't need to do a loop for each control

Just do ServiceSubForm.Locked = True
Joseph O'Malley
19 days ago
Hi Adam, that you so much for your help. I switched the reference method for the subform controls as you suggested. I think that helped in some ways towards a few of my issues but I was still having the whole subform showing as locked when the status combo box was "not equal to"/ "<>" the phase "complete". I ended up switching some of the driving logic by setting that combo box's default value to " not complete" so then in the vba code I have it as when IsUserInGroup(3) then a nested IF statement saying to unlock the specific fields if the combo box is = to "not complete". So when the record is marked "complete" , which can only be done by a manger, the record will be locked for group 3. It' working like a charm now!
Adam Schwanz
19 days ago
Glad you figured it out
Add a Reply

Show All Comments

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

5/9/2021Access D29 Started
5/9/2021Access Developer 30
5/9/2021Access Developer 30 Lessons
5/9/2021Access Developer 30
5/8/2021Data Entry
5/2/2021Split Commissions
4/30/2021Close Form
4/30/2021Close All Forms
4/29/2021Quick Queries #3
4/26/2021Combining Names
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access database security seminar  PermaLink