User ID & Password

[cocoonfx]

I have successfully created a user id & password prompt which works excellently.

The database i have created has three forms inwhich two departments access. I have an issue where one department is only allowed to view a search screen and the other department is allowed to enter in information and search. I also have a section for managers to login and run reports.

In my userid & password table i have created and field called Level which includes: Administrator, Manager, Bookings,User

Admin - Full Access
Managers - Full Access - But not able to edit queries and tables
Bookings - Order Form, Search Form
User - Search Form

Here is the code i have used:

Login OK Button:-

Private Sub login_Click()

If txtPassword.Value = DLookup("Password", "tblAccess", "[UserId] = txtUser.Value") Then
CheckFlag
DoCmd.Close acForm, "frmUserLogon"
Else
MsgBox "INCORRECT PASSWORD! Please try again"
End If

End Sub

Module1:
Option Compare Database
Public Flag As Integer '1: Reports Opener 2:Log 3:OpenUKSupplier

Function CheckFlag()
Select Case Flag
Case 1
DoCmd.OpenForm "frmReports Menu"

Case 2
DoCmd.OpenForm "frmbooking"
Case 3
DoCmd.OpenForm "Frmsearch"
Case Else
'Should never be here

End Select
End Function

Where would i add the code to the module or to the event?

 

[supercharge]

Wow, this codes look very familiar. I posted a sample with very similar coding as yours.

Anyway, can you be clearer on your question? You said: "Here is the code I have used" then you ask where to add the code? If you have used the code then you should already have it in the right place, right?

 

[cocoonfx]

HELLO


Yep the code is in the right place, but i am having a major problem working out how to get the code to work so that user just types in the user id and password press ok and then the user then only sees the information as per the Level!

So for example in my table i have

userid password Level
Joe bloggs All
John test user

I have tried putting in another if statement below the user id and password dlookup and that doesn't work in the event procedure

if txtpassword = Dlookup("Password","Access","Level",= "All") Then
Checkflag

Which doesn't work!

Then i get more confused by think should i put the if statment in the module or create another module to group the Level.

Any assistance would be greatfull.

 

[The_Doc_Man]

Your problem is that you have reinvented a Ferrari wheel and now wonder why it doesn't fit on the axle of your Ford.

The functionality you described exists in Access. It is called Workgroup security. You define workgroups with the proper permissions. Then you define user accounts. Then you place users in the proper groups. Viola - your restrictions exist as noted.

Now if you want to restrict subsets of a particular table to specific groups, that gets trickier - but still not impossible. But first and foremost, look into using existing Access security technology to perform security-related things within Access.

I don't want to squelch honest attempts to learn about Access, but you must realize by now that security, to be effective, has to be PERVASIVE. And that takes a long time to set up. Particularly since in some cases, there is no way to stop someone from looking at something your add-on security should have protected - but you just weren't pervasive enough.

Now I'll add even more concern to your puzzle. If you are allowing folks to have access to tables and queries, you will NEVER get your third-party security to work right. Because to make such things work, you need to have EVENTS that fire off when something is attempted. Pat talked about some new version of Access that might have a few more events, but if you are AcXP or earlier, I don't belive that is available yet. So you have no place to put your security code. But with Workgroup security that is pervasively used within Access, you don't have to put in ANY code.

 

[cocoonfx]

Thanks Doc man for your reply.