User permissions allowed incorrect access

[DatabaseTash]

@isladogs
Does your tag control method cover split forms where the bottom of the form is datasheet ie; would it make the datasheet part of the fomr read only aswell?

In the code and user table for the password login database sample it notes the computer name. I'm worried what will happen if people login on another computer. Will it still know who they are??

I have been looking at a few other examples of user login authorisation, but they all seem to just lock access to a whole form. I want them to access a form in read only. This is why I am tying to implement your's.

In this example https://www.access-programmers.co.u...n-enabled-disabled-or-locked-unlocked.293439/ you have buttons that disable/hide/lock, how do I adapt it so it will check the tags once the user logs in? Is that something that would be set in a recordset/dLookup when the database loads? Or would it be some type of dLookup when the form loads?

Would really appreciate your help!

 

[isladogs]

1. Both parts of a split form behave in exactly the same way as it is in fact a single part.
If you lock one or more fields (or the whole form) using the property sheet or a tag value, both parts will be locked

2. Yes. It logs the person no matter which workstation they login at

3. Use code similar to this in the load event of your form(s)

Select Case GetUserLevel

Case 1 'allow restricted access
LockControls True, "A", "X"
ShowControls False, "B", "Q"

Case 2 'full editing allowed
Lock Controls False "A","X"
ShowControls True, "B","Q"

End Select

Note that GetUserLevel would be a function in a standard module which uses DLookup to get the user level of the logged in user

Adapt the code as appropriate to your own database.
Hope that helps

 

[DatabaseTash]

I am starting to get excited! It is all starting to come together.

I have loaded all the login form etc into my database and have got that working.

I'm not sure what I have missed as far as the locked controls goes. I have trialled the database on a PC that should have read only access, but it allows them to edit. I have added tags to all the fields in the forms. I have added the letter C in the tag property.

I have added this script to each form:

Private Sub Form_Load()

Select Case GetUserLevel

Case 1 'allow restricted access - READ ONLY USER (C)
LockControls True, "C"
ShowControls False, "B"

Case 2 'full editing allowed - Admin (A)
LockControls False, "A"
ShowControls True, "A"

End Select

End Sub

Have I missed something?
In the tags I use letters A or C, but in tblUsers the accesslevel is 1 or 2. Should they both be letters??

 

[isladogs]

I edited your last post to add code tags.

Read only access is determined by the logged in user ... NOT which workstation they are on.
To test your code, login as someone with read only access.

As the user level and tag properties work independently, you could use the same letters for each but I think it could be confusing for you.
I would stick with what you have.

As for your code, do you have any controls tagged 'B' or should that line be 'C'?
If you do only have those two tags, you don't need to lock/unlock the controls. Just hiding/showing them is sufficient
Having said that there must be some other controls not tagged at the moment which the read only user can see but not edit(?).
Suggest you use a different tag letter for those items.

If you use exactly the same code in multiple forms, there is a better way.
Create a Public Sub/Function with that code in a standard module and call it e.g. ManageControls.
Then remove all that code from your Form_Load events and just write ManageControls instead.
Doing that means if you ever need to modify the code, you only need to change it in one place rather than in each form.

Hope that helps.

 

[DatabaseTash]

@isladogs
Sorry I didn't explain myself very well. I logged in as someone else (who has read only access) on another computer.

I have now removed those show control lines. I don't have any controls tagged 'B'. Just named it 'B' because I thought the line had to be there. I think I will stick to having the code on each form as I'm less likely to stuff it up.

I have just trialled it again and it hasn't worked.

In the Tag Property for each field which I wish to lock, should I have just the letter C in that field? Or would I put C, A ?

Also to get the Logout recorded in the tblLoginSessions I tried to add this to the on close event for the switchboard. It seemed to through me out when trying to view the form in Design View. It eventually caused issues with trying to open the database. Is there away to avoid this? This is the code I had on the switchboard.

Private Sub Form_Close()

On Error GoTo Err_Handler
' Close the session for this user and flag them as logged out.
Call CloseSession
Call LogMeOff(lngLoginID)
DoEvents
Application.Quit

Exit_Handler:

Exit Sub


Err_Handler:
MsgBox "Error " & Err.Number & " in cmdLogout_Click procedure: " & Err.Description
Resume Exit_Handler

End Sub

Where you say:

"Having said that there must be some other controls not tagged at the moment which the read only user can see but not edit(?).
Suggest you use a different tag letter for those items. "

Do you mean that every single thing on the form needs to have a tag??

 

[isladogs]

Sorry but I found the first part of your post difficult to follow.
Use as many or as few tag letters as required to manage your different control groups.
It isn't necessarily essential for all controls to have a tag value but it can be easier to manage what's going on if you do so.

The code you provided will log out users then quit the database when the form is closed.
Are you saying it logs you out when young into design view?

As I'm unclear what you're asking, it may be easier if you can upload a cut down version of your database.

 

[DatabaseTash]

Thank you for having a look at my database, I really appreciate it!! I hope I haven't stripped the database down too much. Normally the Database is split but I have included the tables in the database for this sample.

What I am having trouble with is:

• How to make the forms read only. I am the only one who needs Admin access. Everyone should be read only. I have included the tags, but it still allows users who should be read only, to edit.
• The script I have on the switchboard form doesn't allow me to view the switchboard in design view. It just closes the database.

 

[isladogs]

I started to look at this earlier but am busy for the rest of today.
There are a number of issues including:
1. Table tblSJCOldJobData required for qrSearchForSJCJobs is missing. Do I need it to test this or can I ignore it?
2. Query QryUsers had lost its link to your tblUsersNew so each query field began with Expr1 etc. I've fixed that.
3. I've had to empty TblLoginSessions as your users were left logged in on another computer and the app security prevents them logging on elswhere
4. You've misunderstood about how the tag property should be used. Possibly I didn't explain it clearly.
Instead of using tag =C,A you should have just used e.g. tag =C.
I will fix that and upload later when I have it working
5. The switchboard form is not a standard form and I recommend you replace with your own form which provides the same functionality more simply.
I expect that will fix the issue you have changing to design view.
6. As well as my code to control the state of various controls, you have several other modules doing similar things including one by @static. This will cause confusion. Best stick to one method and discard remaining code

Will try to find time to review properly this evening UK time.

 

[isladogs]

Hi Tash
I've spent some time working on this and am somewhat baffled as to why it doesn't work correctly even after making all the changes listed above.
I can't really afford to sped any more time analysing it to find out why its still failing.
However, there is an easier way which does work!

As you want the entire form either locked (AccessLevel=1) or unlocked (AccessLevel2) then you don't need any of the tag code (or indeed the modules used for that purpose)

I've modified the Form_Load events as follows:

Private Sub Form_Load()

Select Case GetUserLevel

'code modified by Isladogs 14/07/2020
'Tag ="C" used for all controls that need to be locked for standard users

Case 1 'allow restricted access - READ ONLY USER
'LockControls True, "C"
Me.AllowEdits = False

Case 2 'full editing allowed - Admin
'LockControls False, "C"
Me.AllowEdits = True

End Select

End Sub

I've replaced the commented sections based on LockControls and the tag property with code to lock/unlock the entire form ... Me.AllowEdits ....
It works!

For info, I've replaced your switchboard form with a new form frmMain which has the same functionality but doesn't need the associated SwitchboardItems table

If you are happy with how this works, you should now delete all redundant code / modules / forms / tables

 

[DatabaseTash]

Thank you SO much! It works!! You are a CHAMPION!

Thank you, I REALLY appreciate your time!

 

[isladogs]

You're welcome.
Forgot to say that I added the missing GetUserLevel function to modFunctions.

Make sure you remove all functions and code that was related to the use of the tag property.
There was something in your app that conflicted with the code I was trying to use on your behalf.
The fact that you had my code module plus others by static and access diva was probably the issue.
Each of those would work fine on its own but there was a likelihood of code conflicting by having all of them at once.

 

[DatabaseTash]

I have just included all those changes in my database and they are working beautifully. It is working a little too well on the the forms where there is a search field though. For example with "Search By Parish Or Partial Plan Number" form there are 2 search fields at the top and it doesn't allow any entry into the search box. Is there a way around that? The rest of the form really needs to be locked as that is were the data appears. Is the only way to make it work to separate those search boxes onto another form? I would rather keep it as one form if possible.

 

[DatabaseTash]

Also what would stop the session from logging in the tblLoginsession? I have been testing it in the one you sent back and it is leaving users logged in.

 

[vhung]

i don't use similar system.
i use Login Form and userTable (table).

the weakness of your system is that any computer can have same UserProfile (username).
or maybe same ComputerName.

you must use userTable and specifically write each username and password.
secretly give each user his username, password to the system.
then use a Login form each time they try to access your db.

yes: sir Arnel has the right idea...
previously i had a Accdb that holds thousands of property list
when i have done the design i run it through 3 computer networks
the problem is i don't have to share all of my form features to the stations
what i made is install all Accdb features to both of them
and then i create a table for restriction "A log-in system" using a link table from my Accdb Source a Split Accdb
then i made changes to the forms "example if they want to edit the address of the data
i used the restriction yes/no through the menu form could be disabled/enabled the buttons for Editing/Deleting
it is very possible because i use the mode to the timer auto load of the form, everytime you change permissions then rule it...
it is very applicable a long run to work but i got it
but if only one computer to use you have to log out your admin account then next user log-in, without dbclose remain open...
hope this could add idea...

 

[DatabaseTash]

yes: sir Arnel has the right idea...
previously i had a Accdb that holds thousands of property list
when i have done the design i run it through 3 computer networks
the problem is i don't have to share all of my form features to the stations
what i made is install all Accdb features to both of them
and then i create a table for restriction "A log-in system" using a link table from my Accdb Source a Split Accdb
then i made changes to the forms "example if they want to edit the address of the data
i used the restriction yes/no through the menu form could be disabled/enabled the buttons for Editing/Deleting
it is very possible because i use the mode to the timer auto load of the form, everytime you change permissions then rule it...
it is very applicable a long run to work but i got it
but if only one computer to use you have to log out your admin account then next user log-in, without dbclose remain open...
hope this could add idea...

Thanks for the reply. I have isadog's login system in place which seems to be working well except for a couple of small issues. Almost have it sorted.

 

[isladogs]

Hi Tash
Oops - I forgot to copy the Form_Close event to the new frmMain. Just fixed that.

Just to clarify, please confirm that you want the search feature available to all users on the two search forms but that the rest of the form should still be read only in each case. The search button still works on the IS Book form as you've used a different method here

I recommend changing the two search textboxes to combo boxes so your users have a list of available values to choose from. is that ok with you?
I'll look at it again this evening UK time. Hopefully you could reply to the above before then

 

[DatabaseTash]

@isladogs

Just to clarify, please confirm that you want the search feature available to all users on the two search forms but that the rest of the form should still be read only in each case. The search button still works on the IS Book form as you've used a different method here

Yes, that is correct. It needs to be available to all users. Everyone need to be able to enter text to search for. The search result is what I don't want the read only user to change.

I recommend changing the two search textboxes to combo boxes so your users have a list of available values to choose from. is that ok with you?

I'm not sure the combo box would work well in this situation. There are more than 27,000 plan numbers in the database. Also when they search for the plan number they don't always know the letters and so do a partial search by the number. For example if I am looking for SP552233 and don't know if it is a SP or RP I would just search by 552233.

 

[DatabaseTash]

I am still battling trying to get this to work.

The code I had previously was locking the forms well, but didn't allow the READ ONLY USER to perform a search from the Search box on the form or select the Hyperlink. I am getting an error saying

Run-Time error '438'. Object doesn't support this property or method

. Then when I debug, it highlights the line that says ctrl.Enabled = False.

Option Compare Database
Option Explicit

Private Sub Form_Load()
Dim ctrl As Control

Select Case GetUserLevel

Case 1 'allow restricted access - READ ONLY USER
  For Each ctrl In Me.Controls
    If (TypeOf ctrl Is TextBox) Or (TypeOf ctrl Is CheckBox) Or (TypeOf ctrl Is ComboBox) Or (TypeOf ctrl Is BoundObjectFrame) Or (TypeOf ctrl Is CommandButton) Or (TypeOf ctrl Is ListBox) Or (TypeOf ctrl Is ObjectFrame) Or (TypeOf ctrl Is OptionButton) Or (TypeOf ctrl Is Rectangle) Or (TypeOf ctrl Is SubForm) Or (TypeOf ctrl Is TabControl) Or (TypeOf ctrl Is Label) Then
     If ctrl.Tag <> "DoNotLock" Then
       ctrl.Enabled = False
       ctrl.Locked = True
       Else
       ctrl.Enabled = True
       ctrl.Locked = False
     End If
     End If
          Next
Case 2 'full editing allowed - Admin
Me.AllowEdits = True

End Select

End Sub

I have attached an image of the form I am working on. The search boxes I mentioned are at the top.
The field with the hyperlink is called plan link. What would the plan link be classed as with the controls. Am I missing that from the TypeOf Ctrl part of the code above?? The plan link field also has the following code on it. Not sure if that is having an impact on what I am trying to do.

Private Sub Plan_link_Click()
FollowHyperlink Me.Plan_link
End Sub

 

[isladogs]

Hi Natasha
Due to ongoing computer issues, I don't have time to revisit your application. Sorry.

However, there are several issues with the code you supplied
1. I never use TypeOf ctrl syntax and without checking am unclear whether it is valid here
I would replace it with e.g. ctrl.ControlType and use e.g. acTextBox, acListBox etc ... NOT TextBox, Listbox etc
NOTE: Someone wrongly referred to ctrl.Type earlier in this thread. That syntax is incorrect

2. Several of your control types (acLabel, acImage, acLine, acRectangle, acPageBreak) cannot be disabled so the code will error
To remind you, this is the code I use for this purpose:

Public Sub EnableControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
       
        Case acLabel, acImage, acLine, acRectangle, acPageBreak
            'no code here - these can't be disabled
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                    Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Enabled = State
        End Select
       
    Next ctrl

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in EnableControls procedure: " & Err.Description
    Resume Exit_Handler
   
End Sub

3. As mentioned previously when you contacted me via PM, I believe your application has some corruption in either form code or controls
I would STRONGLY recommend creating a new form on which to work.
Alternatively you could try decompiling your project. That will fix code corruption but not corrupted controls. http://www.fmsinc.com/microsoftaccess/Performance/Decompile.asp