How to Limit Record Navigation Based on User Level? 2

[jcmv007]

Hi,

How do I code a form so that when a User with a security Level 2 (level set by me in tblUsers) only can see info regarding his store, but when a User with a security Level 3, which is a Manager can see info for all stores?


Here is the code for the first part of the question, don't know how to do it for the managers thou?

Private Sub Form_Load()

Dim lngUserDpto As Byte
Dim lngAnoFisD As Long
Dim lngAnoFisH As Long

    SetTitle (Me.Name)
    
     'DLookup("User", "tblCurrentUser")
     LocalUser = DLookup("User", "tblCurrentUser")
     strLocalUserSecurityLevel = DLookup("SecurityLevel", "tblCurrentUser")
     lngUserDpto = DLookup("Depnum", "tblUsers", "User =" & LocalUser)
     lngAnoFisD = DLookup("AnoFiscalD", "tblCurrentUser", "User =" & LocalUser)
     lngAnoFisH = DLookup("AnoFiscalH", "tblCurrentUser", "User =" & LocalUser)
     Me.UserName = CLng(LocalUser)
     
     'Intantiate recordset object
     Set objRec = New ADODB.Recordset
          
     Me.Filter = "Depnum=" & CByte(lngUserDpto) & "And AnoD=" & lngAnoFisD & "And AnoH=" & lngAnoFisH
     Me.FilterOn = True
End Sub

Thanks,

 

[JeremyNYC]

Well, first things first: your system will be more secure and you will have ready-made code for doing this if you stop using a home-made security system and start using the user-level security built into Access. Everything you need to know on the topic is in the Security FAQ, which you can get from MS or from my web site.

Next, you should get rid of all of those DLookupStatements because they are quite slow. You could build a recordset or two to replace all of that and it would be much quicker.

Finally, you'll have to build the filter string using an if/then structure that tests for membership in the correct group--here's some pseudocode:
if IsUserInGroup(sUser, sGroup) = False then
sFilter = sFilter & " Store = '" & sUser & "'"
end if

This code will NOT run. You probably haven't named your stores and users the same (nor should you). But there's the idea.

Hope this helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[MadJock]

What's the recordsource of your form - table or query?

If it's a query, can you post the SQL from it?

The reason I ask is I've achieved the effect you are after the following method (assuming that form is based on tblStudents).

This is an example only, but you should get the point I'm driving at.

Private Sub Form_load()

Dim intUserLevel as integer

  'Establish level of access
  intUserLevel = getCurrentUserLevel

  'Determine Record Source Accordingly
  Select Case intUserLevel
    Case 1 'Head teacher - show all
      me.RecordSource = "SELECT * FROM tblStudent"
    Case 2 'Class Teacher - show relevant student only
      me.RecordSource = "SELECT * FROM tblStudents WHERE teacher = " & CurrentUser
  end select

end sub
[code]

Hope this helps,

Graeme


website: [URL unfurl="true"]www.graeme-taylor.co.uk[/URL]

 

[jcmv007]

Thank you both for your prompt response!

Now I'll answer questions.
Jeremy
Regarding the home made security I've read a couple of threads about it, actually I've visited your site. Also I download a sample database you shared.
I've found it easier to use a home system because of sample code provided.

About the Dlookup's I would appreciate if you could give some sample code on how to use Recordset and how extract values from them. I am working with out any manual and have not taken any courses for VBA.

Was not able to figure how to make your code work.

Thanks for your time.

MadJock
The form is based on a table, but your code did work great!

Thank you also for your time.


James

PS: Star for both!

 

[JeremyNYC]

James,

Thanks much for the star. I would say that you should go out and buy a book. There's tons of stuff to be said about recordsets. The help files will show you what you need to get started, but, assuming you have food in your cabinets, there's no reason to not have a good book. I recommend the Access [version number] Developer's Handbook.

As for sample code, there's all the sample code you could hope for in the Security FAQ.

I have to say, there are a lot of people here who get new developers to use home-grown security systems, and it's a real shame--you will always have fewer features, less shared code available, and fewer developers who can help you out in a bind than if you use the built in security.

But, evidently there are enough people out there who are scared by Access's security that it persists. As I said, I think it's a real shame--if you're going to get a job with your access skills you'll most definitely need to know user-level security. It's difficult, yes, but it's nothing that a few hours of studying won't crack.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.