Display Username instead of Employee ID in form and queries 3

[BvCF]

Problem: Employer ID number is displayed in AssignedTo field within form and also in main Access table.

What is the quickest method to modify the database so that the Access database forms display Username in the AssignedTo

field instead of the Employee ID number? Also, anytime I create a query and build a listbox on the query, the employee

id is displayed instead of the username.


Currently, I am using a global variable throughout the Access database, a Employee table
and several key queries and/or mudules to restrict access to the data within the Access
database. In other words, the end-user should only be able to view the accounts assigned to them.


Employee table

Employee_ID------Username-----Password----AccessLevel
1----------------Ann----------****--------E
2----------------Jill---------******------M
3----------------Joe----------******------E
4----------------Jack---------*****-------E
5----------------Sue----------***---------M
6----------------Biol---------****--------E

Access_List

SELECT L_Employees.Employee_ID
FROM L_Employees
WHERE (((L_Employees.Employee_ID)=get_global('employee_id'))) OR (((get_global('access_level'))="M"));


Module

Option Compare Database
Global GBL_Access_Level As String
Global GBL_Employee_ID As Long ' initially formatted as String
Option Explicit
Public Function get_global(G_name As String)
Select Case G_name
Case "access_level"
get_global = GBL_Access_Level
Case "Employee_ID"
get_global = GBL_Employee_ID
End Select
End Function

Sub set_globals()
GBL_Access_Level = "X"
End Sub


qryPopulateListBox (Used to enable the end user to only see his/her accounts in the listbox)

SELECT tblVar.AcctOrig, tblVar.AcctCurrent, tblVar.AcctNo, tblVar.Balance,tblVar.ExpPymt
FROM tblVar
WHERE (((tblVar.AssignedTo) In (select employee_id from Access_List)));

 

[PHV]

display Username in the AssignedTo field instead of the Employee ID number?
Use a combobox.

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

 

[AlexCuse]

Set the control source to this:

SELECT L_Employees.Username
FROM L_Employees
WHERE (((L_Employees.Employee_ID)=get_global('employee_id'))) OR (((get_global('access_level'))="M"));

Rather than this:

SELECT L_Employees.Employee_ID
FROM L_Employees
WHERE (((L_Employees.Employee_ID)=get_global('employee_id'))) OR (((get_global('access_level'))="M"));

?

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[BvCF]

AlexCuse,

Typed in the code that you provided. The Employee_ID is still displayed! Interesting observation is that the control source for the textbox reverts back to "Assigned To."

Also, considering that the AssignedTo field has a data type of number, how will the manager be able to assign accounts. In other words, a standard update query is in the following format;

Update tblVar
Set AssignedTo = "Ann"
where AssignedTo = ""
and AcctID = "X02"

Would it be easier to delete "Employee_ID" from the employee table and maybe use username as the primary key?

The only option that is being contemplated is to use the windows login name to restrict users' access to the data.

Note, I am populating my listbox on the multi-tabbed form using the query below. Therefore, this implies that the data type for the "AssignedTo" and the "Employee_ID" field are the same. In this instance, they are of the number data type.

qryPopulateListBox (Used to enable the end user to only see his/her accounts in the listbox)

SELECT tblVar.AcctOrig, tblVar.AcctCurrent, tblVar.AcctNo, tblVar.Balance,tblVar.ExpPymt
FROM tblVar
WHERE (((tblVar.AssignedTo) In (select employee_id from Access_List)));

 

[JoeAtWork]

I'm under the assumption that:

Access_List is a listbox that allows you to pick a user, which will subsequently allow you to fill a second listbox with tblVar records assigned to that user.

The standard method in such a case is to have two columns in the Access_List listbox, the first being the ID, the second being the name. Usually you would hide ID column, but set it as the bound field.

So, the Row Source for Access_List would be something like:

SELECT L_Employees.Employee_ID, L_Employees.Username
FROM L_Employees
WHERE (((L_Employees.Employee_ID)=get_global('employee_id'))) OR (((L_Employees.AccessLevel=get_global('access_level'))="M"));

Set other properties as follows:
BoundColumn=0
ColumnCount=2
ColumnWidths=0;0.1 (second value doesn't matter, as long as it's bigger than 0)

Your code to populate the second listbox would be something like:

Dim strSQL as String

strSQL = "SELECT tblVar.AcctOrig, tblVar.AcctCurrent, tblVar.AcctNo, tblVar.Balance,tblVar.ExpPymt
FROM tblVar WHERE tblVar.AssignedTo = " & nz(Access_List,0)

lstDetails.RowSource = strSQL
lstDetails.Requery

Likely place to put the above might be the Access_List's click event.

 

[BvCF]

Additional context; I have two queries that form the basis of security for the Access database. The first query displayed immediately below is the query record source for the main form, frmMain;

Select tblVar.AcctID, tblVar.AcctNo, tblVar.DateOpened from
tblVar where (tblVar.Employee_ID) in (select employee_id from Access_List) OR ((tblVar.employee_id)=0)));

The combination of the query above and the query titled "Access_List" limits access to account data.