Open Specific Form Depending On Who Is Logged Into System

[MFS]

Hello all,
My database has multiple users and I'm trying to figure out a way that if this user logs in only "His / Her" form opens.
I must also be able, if a user quits, be able to reassign a new user to access that form. Of course reassigning users would be done under the Administrators form.
Attached is a copy of my login VB.


I hope someone can help with this.

Private Sub combo256_AfterUpdate()
'After selecting user name set focus to password field
    Me.Text254.SetFocus
End Sub
Private Sub Command247_Click()

'Check to see if data is entered into the UserName combo box
    If IsNull(Me.Combo256) Or Me.Combo256 = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.Combo256.SetFocus
        Exit Sub
    End If
    'Check to see if data is entered into the password box
    If IsNull(Me.Text254) Or Me.Text254 = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.Text254.SetFocus
        Exit Sub
    End If
    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box
    If Me.Text254.Value = DLookup("strEmpPassword", "Users", _
            "[lngEmpID]=" & Me.Combo256.Value) Then
        lngMyEmpID = Me.Combo256.Value
        'Close logon form and open splash screen
        DoCmd.Close acForm, "Logon", acSaveNo
        DoCmd.OpenForm "Test"
        
    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.Text254.SetFocus
    End If
    'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

[Rawb]

I'd add a text field to your Users Table called something like "FormName" that stores the name of that user's specific Form.

That way once the user has logged in, you can just query their Record and run the following code (assuming your Recordset is called "rst"):

DoCmd.OpenForm rst("FormName")

[MFS]

Rawb,
I think I understand what you are stating, just not exactly sure about "That way once the user has logged in, you can just query their Record and run the following code (assuming your Recordset is called "rst")"

[ssanfu]

First a comment... It would be better if the control names were changed from the Access default. For example, which is easier to understand/trace:

Private Sub Command247_Click()

    If IsNull(Me.Combo256) Or Me.Combo256 = "" Then
        MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.Combo256.SetFocus
        Exit Sub
    End If
    'Check to see if data is entered into the password box
    If IsNull(Me.Text254) Or Me.Text254 = "" Then
        MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.Text254.SetFocus
        Exit Sub
    End If

Or

Private Sub cmdLogOn_Click()

     If IsNull(Me.cboUserName) Or Me.cboUserName = "" Then
        MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboUserName.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

I always rename controls/events to meaningful names.



I was also curious about this

if a user quits, be able to reassign a new user to access that form.

Does each user have a form? So that if there are 50 users, there are 50 forms?

[MFS]

" If a user quits, be able to reassign a new user to access that form. "
I should have stated that if I have a current user that leaves the company.


I would like to only have one form if possible and the data that populates on that form would be based upon who is logged in. Example, First_name, Last_name, Hours Worked, Supervisor (Which is the logged in user)
I wish there was a way to post my DB, But I cannot get the size of it down to the minimum 550 +/-

[ssanfu]

I understand "If a user quits (leaves the company), be able to reassign a new user to access that form."
It just sounds like EACH user has a log in form.



Stolen from June7's sig:

To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.

[Rawb]

@speck:
A Recordset is a VBA Object that allows you to query and modify information from the database. For example you could use a Recordset to look up the user's password instead of that DLookup(). It's basically just another way to get at the data.

I'd need to see the DB itself to be sure, but I'm assuming the data comes from the same Tables no matter who the user is? If that's the case, then you can use the same Form for all the users and just change the Query used to populate that Form.

@ssanfu:
Thief! I'm telling June7!

[MFS]

Okay Compressed and zipped,
OT_1.zip

[MFS]

Any Help Out There?

[Rawb]

I've taken a look at your DB and have a few questions before we get started with Forms/etc.

1. What's the time period you're looking at? Are these hours worked per day? Per week? Per pay period?
2. Does the time period vary or will it always be the same (one Record is always an employee's hours for a whole week, for example)?
3. Can Employees be assigned to multiple Coordinators (pls say no! )?
4. Can an Employee be assigned to a different Coordinator and then back to their original Coordinator again (can they "jump" around)?

[MFS]

Thank You Rawb, I was beginning to think that maybe my request was way to difficult.
To answer your questions . . .
☺ These hours are worked per pay period. (Each Week)
☺ One record will always be the employees OT per week.
☺ Employees are generally assigned to one Coordinator at a time, but can be transfered due to promotions etc.
☺ Yes, an employee can "jump around"
☺ One additional item I would like and I'm sure I can figure this out is for each Coordinator to be able to run a report along with a graph showing each employees overtime, beginning with the employee with the least amount of O.T.

Rawb, I hope you can help me with this, using Excel is getting old.

[Rawb]

Your DB isn't normalized. Based on what you've said, I've gone back and looked over it and have come up with a suggestion for a new structure. Obviously, you don't have to use it, but if you do, I think it will make things a little easier (and add some extra capabilities that you might like).

Once you've decided if you want to use this or not, I can show you how to set up your Login and Ot_Hours Forms for what you want.

Coordinators:

• Coordinator_ID - Autonumber. Primary Key, Foreign key (Coordinators_x_Employees.Coordinator_ID). The Coordinator's internal ID.
• Name_Last - Text, 64 characters. The last name of the coordinator.
• Name_First - Text, 64 characters. The first name of the coordinator.
• Active - Yes/No. Is the coordinator currently active (can log in).
• Password - Text (Password), 64 characters. The coordinator's password.

Employees:

• Employee_ID - Autonumber, Primary Key. Foreign Key (Coordinators_x_Employees.Employee_ID). The employee's internal ID.
• Name_Last - Text, 64 characters. The last name of the employee.
• Name_First - Text, 64 characters. The first name of the employee.
• Active - Yes/No. Should new records for this employee be created for each date period.

Coordinators_x_Employees:

• Coordinator_ID - Number, Long Integer. Primary Key. Foreign Key (Coordinators.Coordinator_ID).
• Employee_ID - Number, Long Integer. Primary Key. Foreign Key (Employees.Employee_ID).

Timesheet:

• Employee_ID - Number, Long Integer. Primary Key. Foreign Key.
• Coordinator_ID - Number, Long Integer. The ID of the employee's Coordinator during the specified period.
• Period_Start - Date. Primary Key. The starting date of the range over which the hours are being counted/totaled.
• Period_End - Date. Primary Key. The ending date of the range over which the hours are being counted/totaled.
• Hours_Reg - Number, Decimal (7,3). The number of non-overtime hours worked during the period.
• Hours_OT - Number, Decimal (7,3). The number of overtime hours worked during the period.

Note: If all of your periods are the same length, then you technically only need one of the two Timesheet.Period_* Columns. If you're only going to include one, then I'd recommend keeping the one people tend to reference (usually the end date).

The above structure, while more complex, would allow you to do several things your current one doesn't.

• It would let you keep previous periods that could be used for all kinds of statistical analysis:
  
  • Do one Coordinator's employees consistently get more (or less) overtime than other Coordinator's employees.
  • Do specific employees consistently get more (or less) overtime than any others.
  • Are there periods where you consistently have more overtime (or less) than other periods.
  
  
• It would also allow you to easily change Coordinators for each employee while still letting you see both their Coordinator at any given period as well as their current Coordinator.

[MFS]

Thank You Rawb, After reading your suggestions I guess I didn't think this all the way through in a logical sense?
Yes, If you will support me with this I would like to move forward with your suggestions.
Should I start creating the tables with the information above?

Speck

[Rawb]

Sure! Go ahead and post your DB when you're done and we'll get started on your Forms, etc.

[MFS]

OT_1.zipGood Morning Rawb,
Attached are the generated tables,
Please let me know if I need to correct anything.

Thank You again for all of your help, this little DB will be a great asset.

Speck