Setting Read Only Privlages for Users

[timryder]

Hi Guys,

I've read many posts here which have helped me tremendously but this is my first time using the forums.

I've created a DB program in Access 2007. I built the program to manage my companies inventory as we have dragged on forever trying to do this without every making any headway.

So I have a "Main_Form" which is simply a full screen pop-up form which has many buttons on it which link to open up a new pop-up form. These buttons are forms which display results from a query designed to display only the components for a particular machine which you have selected by pressing that button. So the form opens up and displays only the records associated with Machine 1. Each record has something like 15 fields on the end with a check box in each field. I just set the check box as True if the record is to be associated with the Field Name EX: record A is used in Machine 1 Machine 2 and Machine 3. So i checked the box in the fields for those 3 machines. This way if you open up the form for Machine 3, it'll show this record, as well as if you open up the form for Machine 2 or Machine 1. This helps so that there are no duplicates and I don't have any overlapping inventory values since this Component (record) is used in so many other machines.

Alright so that is my basic structure of my program and how it works. I've created another small form which I've set to load before any of the other forms load first, and it has a combo box with a listing of usernames from a table. Then a text field which you type in a password to "Login" as it were. So what I need to do is setup some VB code which indicates that if the person Logs In correctly, he has privilages to read/write. However if you don't Login but rather you press my "Read Only" button, you can view the Database and ONLY read the values.

I've accomplished this so far by Duplicating every single form including my Main Form and set one set of the forms as a Read-Only form, where all of the components are Locked for editing....... As you can imagine this is a huge pain in the arse if i need to make a change!!!

So I need some code to setup sort of a global variable indicating the status of the user currently logged in, if he has the ability to write or only read.

Help??

Oh I will say that the MAIN form is loaded only after the Login has completed. The mainform is always in the background of all other forms open on top of it. So help!

There are many other functions I want to encorperate in this program/DB but theyll be asked at a later date.

Thanks

 

[CyberLynx]

In a quick nutshell.....

Create a global database variable, let's say: UserPrivilage. To do this, open an existing or start a new Database Code Module (not a Form Module). Under the Option Compare Database or under the Option Explicit if it exists (if it doesn't....it should), enter tis line of code:

Public UserPrivilage As Integer

Now, for each button that opens a Form make sure the Forms are opened in with the following arguments displayed below:

If UserPrivilage = 0 Then
   DoCmd.OpenForm "[COLOR="Red"][I]TheFormNameToOpen[/I][/COLOR]", acNormal, , , acFormReadOnly
Else
   DoCmd.OpenForm "[I][COLOR="Red"]TheFormNameToOpen[/COLOR][/I]", acNormal
End If

In your Login Form, if the User has successfully logged into the Database then set the global UserPrivilage Variable to 1. You may have a OK button located within your Login Form that tests the Password. if the password is good then the variable is set: UserPrivilage = 1

If the Read Only button is selected within the Login Form then: UserPrivilage = 0

Realistically, The User Name, Date, etc, should be placed into a Login Table so an DB administrator can easily see who is logged in a and logged out but, that's another scope.

.

 

[timryder]

Wow Perfect answer! That was exactly what I was looking to see! So you're saying that when I press the Login button I set a Public variable to 1. So when the Program is closed does this value become = NULL automatically until someone logs in again?

Because that is great! I can really use this.

Also, you mentioned that you think i should create a table with Username DateTime like a login LOG as it were? and this should be applied to the OnClick Aciton of the Login button. I like this very very much so do you have any reference code that I can use which would perform this action? Say the table is laid out like this.


Tablename = tblActivityLog
Field1 = Username
Field2 = DateAndTime

The "Login" button is named cmdLogin and here is the code I have written for it already.

Private Sub cmdLogin_Click()
    
'Check to see if data is entered into the UserName combo box

    If IsNull(Me.txtUsername) Or Me.txtUsername = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.txtUsername.SetFocus
        Exit Sub
    End If

'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Sub
    End If

'Check value of password in tblEmployees to see if this matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("password", "tblLogin", "[employeeID]=" & Me.txtUsername.Value) Then

        employeeID = Me.txtUsername.Value

'Close logon form and open splash screen
        
        DoCmd.close acForm, "login", acSaveNo
        DoCmd.OpenForm "Main Screen"

        Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        
        Me.txtPassword.SetFocus
    End If
    
'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttemps + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

I Commented some of that code so you get the just of what it's already doing... Where should I apply this new code to add a record to this log?

Thanks

 

[timryder]

Hey,

Also I'm having another problem on a different function. I wrote a simple search button function which uses an unassigned text box and a 2 CMD buttons to search the filtered records on the form for a specific record. It searches any and all of the fields. The search works Kinda... ok....... there are a few problems with it though and I can't figure it out.

First here is the code for the button OnClick event.

Private Sub Command21_Click()
    Dim strSearch As String
    strSearch = Me.txtSearch
    Me.txtSearch = Null ' this avoids to have the value matching what we want to search
    DoCmd.FindRecord strSearch, acAnywhere, False, acSearchAll, True, acAll, True
    Me.txtSearch = strSearch ' this refill our input in order to rememeber which criteria we used
End Sub

This is the "Search" buttons event code. This seems to work find but I was trying to write a line in there that states if the strSearch String is "" or Null then it will prompt with a MsgBox error and not just error out the Macro so the user has to end the runtime error.

I tried doing this :

Private Sub Command21_Click()
Dim strSearch As String
strSearch = Me.txtSearch
Me.txtSearch = Null ' this avoids to have the value matching what we want to search
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
MsgBox "Error! Please enter a Seach Criteria.", vbOKOnly, "SearchError"
Else
DoCmd.FindRecord strSearch, acAnywhere, False, acSearchAll, True, acAll, True
Me.txtSearch = strSearch ' this refill our input in order to rememeber which criteria we used
End If
End Sub

I keep getting an error on the "strSearch = Me.txtSearch line saying "Invalid use of Null" how do I ask Access If the text box is empty, then Msg box Else do the search...?

 

[CyberLynx]

So you're saying that when I press the Login button I set a Public variable to 1.

Yes....for that Database instance

So when the Program is closed does this value become = NULL automatically until someone logs in again?

Yes....for that Database instance.

you mentioned that you think i should create a table with Username DateTime like a login LOG as it were? and this should be applied to the OnClick Aciton of the Login button. I like this very very much so do you have any reference code that I can use which would perform this action?

Yes...I feel this is good way to keep track of who is or has been on the Database. Although this can get quite in depth (such as who is viewing which Form etc.), a simple Table structure might be:

tblActivityLog (Table Name)
LogInID (AutoNumber) (PrivateKey)
UserName (Text)
LogInDate (Date/Time)
LoggedIn (Yes/No)

When the User has successfully logged into the Database then you simply update the tblAvtivityLog Table.

I Commented some of that code so you get the just of what it's already doing... Where should I apply this new code to add a record to this log?

This can be done from the code you currently have within the OnClick event of your "Log In" button. The section of code to modify is:

[COLOR="DarkGreen"].........................
.........................
.........................[/COLOR]
'Check value of password in tblEmployees to see if this matches value chosen in combo box
If Me.cboPassword.Value = DLookup("password", "[B]tblEmployees[/B]", "[employeeID]=" & Me.cbotUsername.Value) Then
   employeeID = Me.txtUsername.Value

    'Update the tblAvtivityLog Table with the User that Successfully Logged in.
    Dim StrgSQL As String
    StrgSQL = "INSERT INTO tblActivityLog (UserName,LogInDate,LoggedIn) VALUES ('" & _
              Me.cboPassword.Value & "',#" & Now() & "#,TRUE);"
    CurrentDB.Execute StrgSQL, dbFailOnError

   'Close logon form and open splash screen
    DoCmd.OpenForm "Main Screen"    
    Me.Visible = False
Else
    MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
    Me.txtPassword.SetFocus
End If
[COLOR="DarkGreen"].........................
.........................
.........................[/COLOR]

Notice the DLookup function in the code above? The Employee Password should be pulled from the Employees Table (in my opinion).

Now, in the OnUnload event of the LogIn Form you would have:

'Update the tblAvtivityLog Table with the User that is Logging Out.
Dim StrgSQL As String
StrgSQL = "INSERT INTO tblActivityLog (UserName,LogInDate,LoggedIn) VALUES ('" & _
          Me.cboPassword.Value & "',#" & Now() & "#,FALSE);"
CurrentDB.Execute StrgSQL, dbFailOnError

Notice how the Login Form is merely hidden and not closed? This is so that when the User slams shut the Database then the the Login Form is closed and the OnUnload event is fired which in turn updates the tblActivityLog Table the fact that the User has quit the Database.

I keep getting an error on the "strSearch = Me.txtSearch line saying "Invalid use of Null" how do I ask Access If the text box is empty, then Msg box Else do the search...?

This is what the code should look like:

Private Sub Command21_Click()
   Dim strSearch As String

   'If there is nothing entered into the Search Criteria TextBox
   'then Message the User and exit the event.
   If Nz(Me.txtSearch, "") = "" Then
      MsgBox "You must enter search criteria before a Records Search can be carried out.", _
             vbExclamation, "No Search Criteria"
      Exit Sub
   End If

   strSearch = Me.txtSearch
   Me.txtSearch = Null ' this avoids to have the value matching what we want to search
   If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
      MsgBox "Error! Please enter a Seach Criteria.", vbOKOnly, "SearchError"
   Else
      DoCmd.FindRecord strSearch, acAnywhere, False, acSearchAll, True, acAll, True
      Me.txtSearch = strSearch ' this refill our input in order to rememeber which criteria we used
   End If
End Sub

.

 

[timryder]

Wow huge help! Thanks for you're on-going efforts to assist me in this project. I'd like to ask a few more questions of advice if you don't mind. There are a few things I've done which are being counteractive in what I want my end goal to be.

First the Login Form. Its nothing special it just has a Combo box for the Username where the data is bound to

"SELECT [tblLogin].employeeID, [tblLogin].username FROM tblLogin;

In this table there are 3 records, the very first is the "employeeID" which is my PK and is Autonumber. The second is called "username" and its text, the third is "password" and its also text. Eventually im going to add another for user permissions but thats a little ways down the road as i'm still very new to VB coding and learing alot!

So what I've got is the combo box for username setup with 2 columns, the first column has a width of 0 and the 2nd column is 3" (wide enough to fill the combo box display). All of the login scripting is working just fine but i'm running into a problem when i try to fill a Public Variable. I wanted to use this Public String called "CurrentUser" to be displayed on the splash in the Heading Form. Well, because of the architecture of my Login Table, its displaying the employeeID number which is a single number "1" or "2" .... i want to display the 2nd Column in the Combo box which is the TRUE username.

So the code I tried goes as follows...
(In a public Module)

Public CurrentUser As String

Function GetCurrentUser()
    GetCurrentUser = CurrentUser
End Function

Then somewhere on my splash I just have e label and set its data as
=GetCurrentUser()

THIS WORKS! but it displays the employeeID#...

On the OnClick event of my login button there is a line of code which i tried writting after I posted yesterday (so you didn't see it) which reads

.......................
.......................
.......................
CurrentUser = Me.txtUsername.Value

So this is how i'm setting the value of the Public Variable. Since its a Combo Box from which i'm retrieving the data for this Variable, I don't know how to specify the 2nd Column in this Combo Box... So maybe theres a better way to set all this up, maybe there is a simple way to say "Use the 2nd Column"... I don't konw either of them. Help? this is also creating a 1 in the tblLoginActivity column for the Username...

::::SECOND PROBLEM::::

When I close the "Login" form without actually logging into anything, I get prompted with an error saying "The Expression you entered refers to an object that is closed or does not exist." So then I press "DeBug" and here is what it highlights.

So thats something else I'm not quite sure how to fix...

Thanks again for your very useful help!

-Tim

 

[CyberLynx]

If you want to fill the string Variable CurrentUser with the actual name of the Employee rather than the Employee ID then instead of:

CurrentUser = Me.txtUsername.Value

you would use:

CurrentUser = Me.txtUsername.Column(1)

This is how you access the Columns of a Combo Box. Column(0) would contain the EmployeeID and Column(1) would contain the Employee Name.

It's rather confusing when you name a Combo Box txtUserName. A prefix of txt usually implies that the control is a Text Box not a Combo Box. if you are going o prefix your Control Names then they should reflect what the Control is. the Combo Box should be named cboUserName.

For the second problem, modify the code to be like this:

Private Sub Form_Unload( Cancel as Integer)
   'Update the tblActivityLog Table with te User that is Logging Out.
   If Nz(Me.txtUserName.Value, 0) <> 0 Then 
      Dim StrgSQL As String
      StrgSQL = "INSERT INTO tblActivityLog (UserName,LogInDate,LoggedIn) VALUES ('" & _
             Me.txtUserName.Column(1) & "',#" & Now() & "#,FALSE);"
      CurrentDB.Execute StrgSQL, dbFailOnError
   End If
End Sub

.

 

[timryder]

Hrm......... Same error message different Line.

FIRST: I went ahead and took your advice and I renamed the txtUsername Combo box to cboUsername

So after i try to switch to DesignView withouth having logged in at all i get the same error as before "The expression you entered refers to an object that is closed or doesn't exist."

Also I just thought for craps and giggles if you're curious what my stupid program looks like here's a screen shot.

http://www.cmt-engineering.com/program.bmp

So Here's what I gather we're doing from the above section of Code...

If cboUsername is NOT = 0 then create a variable string called StrgSql which gathers its data from my Login Form's values and also the time and date. Then write this data in StrgSql into the existing open record from the Login Attempt.

The only thing here which I can't get is...... whats the Nz in .........
If Nz(Me.cboUsername)............................

 

[timryder]

Another thing I'm trying to do which has gotten me very confused...

I gave you a link to see what my program looks like... on that picture you can see there is a second tab called "reports". ON this page I want to do a search box which search all records in all 3 of my tables for the criteria listed in a text box. Then if at all crazy possible, i'd like for this program to open up the corresponding FORM that the record should normally be displayed in.

For my table structure, I have a very simple way of identifying what data is displayed in which form. The form is based on a query and the query looks at these particular fields in the record which holds a check box. There are 20 or so check box's in each record. I check the boxes in the correct fields so that the record can be seen in the form's I want....

So can I use this to try and open the correct form?

 

[CyberLynx]

So after i try to switch to DesignView withouth having logged in at all i get the same error as before "The expression you entered refers to an object that is closed or doesn't exist."

Make sure the Cobo Box Control on Form is actually named: cboUsername and is not mis-spelled
While in the VBA IDE, do a find & replace on the control name txtUserName and replace with cboUserName.

So Here's what I gather we're doing from the above section of Code...

If cboUsername is NOT = 0 then create a variable string called StrgSql which gathers its data from my Login Form's values and also the time and date. Then write this data in StrgSql into the existing open record from the Login Attempt.

What the code does is check to see if cboUserName actually contains something other that NULL or empty string. It then declares a string variable called StrgSQL which is filled with the gathered data from my Login Form's User Name, the current Date and Time, and sets a flag (False). The Query string (StrgSQL) is then run using the DAO Execute statement (CurrentDB.Execute StrgSQL, dbFailOnError) to Append this data as a new Record within the tblActivityLog Table. The False at te end of the query string will ultimatly set the LoggIn Field in Table to False which therefore indicates that the User has Logged out of the Database.

The only thing here which I can't get is...... whats the Nz in .........
If Nz(Me.cboUsername)............................

The Nz() function Checks the Item supplied to see if it's NULL. If it is, it returns whatever value is supplied after the comma (in this case 0)

As for your last post. I personally would need to have your Database in front of me to see if there is a better way than 20 Check Boxes. I'm certain there is.

.