Access Form - Employee Only See Accounts Assigned 3

[LevelThought]

Hopefully, someone can help with this problem.

I have a multi-tabbed form that is used by Employees and Manager. The Manager assigns accounts for the 6 employees to review. Each employee should only be able to see the accounts that are assigned to him/her.

Currently, upon entering one's name into the login area, the caption of the 1st tab displays the employee name correctly. However, the desired tab doesn't show! The error received is "unexpected error - Object required."

Any idea as to what is the problem and a resolution?

Thank you.

Displayed below is the code on the login button:

Option Compare Database
Private Sub cmdLogin_Click()
'************************ login sequence
On Error GoTo local_err
DoCmd.RunCommand acCmdSaveRecord
'
GBL_Access_Level = "X"
GBL_Access_Level = Nz(DLookup("Access_Level", "L_Employees", "Username='" & Nz(Me.UserName, " ") & "' and password='" & Nz(Me.Password, " ") & "'"), "X")
'
If GBL_Access_Level = "X" Then
MsgBox "Invalid Username or Password... try again."
Exit Sub
End If
'
GBL_Employee_ID = Nz(DLookup("Employee_ID", "L_Employees", "Username='" & Nz(Me.UserName, " ") & "' and password='" & Nz(Me.Password, " ") & "'"), "X")
'
Me.TabCtl0.Pages.Item(0).Caption = "Welcome " & Nz(DLookup("Username", "L_Employees", "Employee_ID=" & get_global("Employee_ID")), "Invalid Login")

' setup privs based on employee or manager

frmTabbed.Requery
'
' call subroutine to set access to forms
'
Call set_privs
'
' reset login screen fields
'
frmTabbed.Requery
Me.UserName = ""
Me.Password = ""
Me.TabCtl0.Pages.Item(1).SetFocus
Exit Sub
'
local_err:
MsgBox "unexpected error= " & Err.Description
Resume ok_exit
ok_exit:
Exit Sub

End Sub

Public Sub set_privs()

frmTabbed.Form.AllowAdditions = False
frmTabbed.Form.AllowDeletions = False
frmTabbed.Form.AllowEdits = False
frmTabbed.Form.AllowFilters = False
'
Select Case GBL_Access_Level
Case "M" ' manager
Me.TabCtl0.Pages.Item(1).Visible = True
Me.TabCtl0.Pages.Item(2).Visible = True
Me.TabCtl0.Pages.Item(3).Visible = True
Me.TabCtl0.Pages.Item(4).Visible = True
Me.TabCtl0.Pages.Item(5).Visible = True

frmTabbed.Form.AllowEdits = True
frmTabbed.Form.AllowAdditions = True
frmTabbed.Form.AllowDeletions = True
frmTabbed.Requery

Case "E" ' employee

Me.TabCtl0.Pages.Item(2).Visible = True

frmTabbed.Requery

End Select

Private Sub Form_Open(Cancel As Integer)

Call set_globals

'Hide tabs
Me.TabCtl0.Pages.Item(0).Caption = "Welcome"
Me.TabCtl0.Pages.Item(1).Visible = False
Me.TabCtl0.Pages.Item(2).Visible = False
Me.TabCtl0.Pages.Item(3).Visible = False
Me.TabCtl0.Pages.Item(4).Visible = False
Me.TabCtl0.Pages.Item(5).Visible = True
End Sub

 

[MajP]

Comment out the Error Checking to see where the code breaks. My guess is it is here:
Me.TabCtl0.Pages.Item(1).SetFocus

Put a msgbox in the "Public Sub set_privs()" to see the value of the "GBL_Access_Level". Rough guess is that you never set page 1 visible and then try to set focus on it.

 

[LevelThought]

Upon commenting out the Error Checking, the code breaks on the first occurrence of "frmTabbed.Requery."

The tabbed form that I want the employee to first see is the page named "Page3" and the index is 2.

Any thoughts?

 

[PHV]

the code breaks on the first occurrence of "frmTabbed.Requery."
Hopefully with a meaningfull error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

What is "frmTabbed". Is frmTabbed a subform control on the main form? There is no requery method for a subform control, you need to requery the form within the control. Maybe

Me.frmTabbed.form.requery

 

[LevelThought]

Okay, thanks for the insight.

This is starting to really come together!

I used "Forms!frmTabbed" instead of "frmTabbed."

There is no subform on any of the pages within the tabbed forms. The tabbed form is named "frmTabbed."

Currently, upon entering the login name and the password, additional tabs are displayed, as desired.

Issue - Upon logging in as a "employee", all of the desired forms are displayed but there is "#Name?" displayed on all of the fields on the form that should contain the employee's accounts that he/she should review. Also, upon double clicking on any of the date-related fields, the calendar control is not displayed.

Question - Is there a quick method to refresh the record source for all of the controls on the tabbed form that the employee sees instead of individually going to each control?

Thanks in advance for any additional insight.

 

[PHV]

You may try this:
Me.Recalc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LevelThought]

Additional context:

I have a multi-tab form that is used by the Manager and 6 employees.

The Manager assigns accounts to the employees by accessing a form on the "Administrator" tab. The control source for this form will be the main table - hence, it contains all of the accounts within the table for the Manager to assign.

Now, I have several controls (textboxes and combo boxes) on the "ReviewAccounts" tab that the employee will access and should only include the accounts that the employee is assigned to.

Issue - the textboxes on the "ReviewAccounts" tab have "#Name?" in them.

Question(s) - What is the problem and possible solution?

Do I need a subform on the "ReviewAccounts" tab?

Could the problem be related to the naming of the textboxes on the "ReviewAccounts" tab?

Thanks in advance.

 

[WB786]

My approach is little different:

1 - capature the login id - windows
2 - show only records that belong to that login id

Your query would be set to use the login id as the creteria to pull up records for that employee only.

Sweet and simple.

WB

 

[LevelThought]

WB786,

Maybe this is the problem. I am selecting the individual textboxes on the "ReviewAccounts" tab and selecting the control source to the following query:

SELECT tblAllAccounts.AccountOrig, tblAllAccounts.AccountCurrent, tblAllAccounts.AccountNo, tblAllAccounts.LastName, tblAllAccounts.FirstName, tblAllAccounts.TotChg,
FROM tblAllAccounts
WHERE (((tblAllAccounts.AssignedTo) in (select employee_id from Employee_List) or
(tblAllAccounts.AssignedTo)=0));

Other textboxes and combo boxes are not based on this query but is control sourced to the tblAllAccounts. These are the textboxes where I am capturing the date-related information such as "Date Resolved" and "Date Closed" using a calendar control.

The end result should be that the employees' actions (selection of specific dates for "Date Resolved", "Date Closed" should be saved to tblAllAccounts.

Any ideas as to how I can resolve the "#Name?" issue and fix the problem so that the records show on the tab titled "ReviewAccounts?"

Thank you.

 

[WB786]

#NAME? = unbounded fields. I would use a subform (I am sure you want to display more than on record on that tab) also after the user logs in where is that info stored? Because you need that info to pass it to your query to pull up the data just for that user. Note: The user validation form would be a hidden form.

Hope this helps.

WB

 

[LevelThought]

Currently, I am reviewing the "unbounded fields" and will post back in a few minutes.

Is a subform necessary?

What are the advantages/disadvantages of a subform on the tab titled "ReviewAccounts?"

 

[WB786]

You are saying AccountS so I am assuming you want to be able to review more than one account under this tab or you are just reviewing only one account? The advantage is the user is able to review all accounts under one tab.

Without looking at your db flow this is how I would do it.

WB

 

[LevelThought]

Okay,

It appears that a multi-tabbed form is really one form - no matter how many tabs might be present. Therefore, if I insert textboxes, combo boxes and any other controls on the "ReviewAccounts" tab, the employee will not be able to see all of the accounts assigned to him/her. The employee will only see the first account assigned.

Is this correct?

(The unusual thing about this is that if I login as one of the employees and go to the tab titled "ReviewAccounts", The record navigation part of the form states the correct number of accounts assigned. For example, 1 of 7, etc.)

So, by creating another form, I can "group" the controls and the employee will be able to toggle through all of his/her accounts.

Is this correct?

I will copy the main form and call it something like "frmSub" and then cut and paste onto the subform to obtain all of the controls that were originally on the main form.

I will try this and post back.

 

[LevelThought]

WB786,

Thanks for the suggestion.

It was successful! Now the employee is able to view all of his/her accounts!

The only thing - if the Manager logs into the database and goes to the "ReviewAccounts" tab, she is able to see all of the accounts that have been assigned to all employees. I locked the textbox titled "AssignedTo." Consequently, not even the Manager can reassign accounts on this form.

How would you do differently?

My thought is that the Manager can assign or re-assign accounts using only using the command button on the "Administrator" tab.

Thoughts? Comments?

 

[WB786]

Glad to see you were able to get it working.

I would create a button that once pressed does couple of things:

1 - But FIRST I would Select the employee from the drop down menu.
2 - then the manager presses this button - gets message are you sure
3 - the AssignedTo gets updated with the employee from the drop down.
4 - plus you can update fields called date and time assigned this way as well or any other fields.

Depending on your business or application requirements I would also make the AssignButton disable if the account has already been assigned. And another button called Re-Assign with Re-assign date and time.

I have used the practice so that when a turnover happens the manager can always go back and see who the record was orginally assinged to. If you don't need this then I wouldn't worry about it.

Hope this helps.

WB

 

[LevelThought]

Followup,

In a response from WB786 on June 1st, How would I capture the Windows login id that result in the employees only able to review his/her records?

How is this different from my method? Advantages? Disadvantages?

Upon adding additional accounts to the local Access database using Excel VBA, the employee no longer just sees his/her records!

Maybe, I will try another method...

 

[WB786]

Don't jump ship quiet yet!!!

1. Create a module called: USERID and put this in it.

Option Compare Database
Option Explicit

'***************************************************************
' found at

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2410

'***************************************************************

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
'inl as 1

Function GetUser() As String

Dim lpUserID As String
Dim nBuffer As Long
Dim Ret As Long
'lpUserID = String(25, 0)
lpUserID = " "
nBuffer = 25
Ret = GetUserName(lpUserID, nBuffer)

If Ret Then
GetUser$ = lpUserID$
End If

End Function

2. Use your query for that form and sub-form and select the username with Criteria USERID.
3. The UserName Field on your form should be set to default value of USERID. This way when the user creates a new record it will put the userid in that field.

I would filter a field called Assigned for the subform in the query and that way the UserID field doesn't change so the manager can always go back to orginal person who created the record before it was re-assigned.

Let me know if you need any more help.




WB

 

[LevelThought]

Upon revisiting this issue, the following thoughts.

I believe that the way I am creating the subform on the tab titled "Review Accounts" is the problem. Specifically, below are the steps that I took to setup the subform:

Step 1 - Created subform on tab titled "Review Accounts"
Step 2 - drag main form onto subform (Note, the main form also appears when the manager accesses the tab titled "Administrator" and clicks on the command button titled "Assign Accounts. The main form is based on the main table that contains all accounts within the database.
At the top of the main form is a combo box with a label titled "Assigned To." At this point, the Manager can assign the account to one of the six employees.

Question 1 - Is this good practice to just drag the main form onto the subform that is located on the tab titled "Review Accounts?"

Option - One option that I am exploring is to have the Manager accessing the tab titled "Review Accounts" to assign accounts instead of going to the tab titled "Administrator." The Manager should still be able to see all of the accounts within the main table and the employee, upon accessing this same tab, should only be able to see his/her accounts.

Since the subform is based on the following query, I believe that the employees will still be able to only see his/her accounts and the Manager will be able to see all of the accounts (assigned and un-assigned);

SELECT tblAllAccounts.AccountOrig, tblAllAccounts.AccountCurrent, tblAllAccounts.AccountNo, tblAllAccounts.LastName, tblAllAccounts.FirstName, tblAllAccounts.TotChg,
FROM tblAllAccounts
WHERE (((tblAllAccounts.AssignedTo) in (select employee_id from Employee_List) or
(tblAllAccounts.AssignedTo)=0));

Is my logic sound?

Thanks in advance for any additional insight and/or suggestions.

 

[WB786]

1. Your step 2 doesn't make sense to me. The sub-form is always a child form of the main form and not the other way around.

2. How many tables are involved and what are thier names relevant to your form.

3. You can just hide - unhide the admin tab based on the user using the db.

4. I have designed a db with two different forms orginally one for users and one for admins so the user never see any admin functions and the admins always can see the users and their data. Sometime it is better that way and then once you iron out the details then you can combine the two togather.

5. I would still pull data based on computer login.


Without looking at the db it is sometime hard to see the flow.

Hope this helps,



WB