Limit users to their Department

[Zachrareth]

Hello,

This may get a little long but I want to explain what my goal is and what I have tried so far.

GOAL.
I am trying to set it up so users can only see records that belong to their department (Tax can only see TAX records) when they are performing a search. I thought an easy way to do this was by creating a login and a password type setup in my search form. Where the department selection would be the User Name and the UserID would be the password. This works as long as I only have a person tied to one department. The problem comes in when I set a up a person that should have access to more than one departments records (TAX and Accounting).

CODE.
I am using

UserID = VBA.Environ("username")

to obtain the users name in the text box "txtUserId"

This is the code I am using to make sure the UserId and UserDepartment match.

 Dim MyEmpID As Variant
'Check value of UserName in tblEmployees to see if this matches value chosen in combo box
If Me.txtUserId.Value = DLookup("UserName", "tblEmployees", "[EmpID]=" & Me.cmbUdpart.Value) Then
    MyEmpID = Me.cmbUdpart.Value
    'Close logon form and open splash screen
    DoCmd.OpenForm "MainFrm"
Else
    MsgBox "Wrong Department", vbCritical + vbOKOnly, "Invalid Entry!"
    Me.txtUserId.SetFocus

TABLE.
Here is tblEmployees

My thinking is that each UserDeparment would have its own EmpID so Accounting = 1, RIM = 2, and TAX = 3 and so on. On my form the UserId text box is auto filled with the Department selection being a drop down box of the UserDepartment and EmpID rows in this table.

THE ERROR MSG.
If I say the User ID is fvwinch and select Accounting for the department, I have no problems and when I debug I see MyEmpID = 1 and Me.cmbUdpart.Value = 1. Now if I select RIM with fvwinch get the msg "wrong department' and in debuging I see MyEmpID = Empty and Me.cmbUdpart.Value = 1.



This driving me crazy so any help here is very much appreciated.

[June7]

Need to include department as another criteria in the DLookup.

[Zachrareth]

Would I write that like this

If Me.txtUserId.Value = DLookup("UserName" and "UserDepartment", "tblEmployees", "[EmpID]=" & Me.cmbUdpart.Value)

[June7]

No. Lookup can reference only one field to return value from. Need to use department in the expression of the WHERE CONDITION argument.

Is UserDepartment field text and has actual department name? Department is a column of the combobox? Combobox index starts with 0.

"[EmpID]=" & Me.cmbUdpart & " AND UserDepartment='" & Me.cmbUdpart.Column(x) & "'")

[Zachrareth]

So since my UserDepartment shows up in the second column in my dropbox do I put 2 where you have the X.

"[EmpID]=" & Me.cmbUdpart & " AND UserDepartment='" & Me.cmbUdpart.Column(2) & "'")

[June7]

No, column index begins with 0 so use 1.

[Zachrareth]

Okay I put in the code and it is still not working. I forgot to mention that my drop down is set to show unique records, this way there is only one RIM to select and not 10 of them. I think the problem is with using the EmpID row to tie the Department with the UserName. Is there a way to code this so it only needs the UserDepartment and UserName. If needed I can post the form and table for you to look at.

[Zachrareth]

I have found the solution to my problem, thank you for your help. This is the video that solved my problem.
http://www.youtube.com/watch?v=_Kdr_L_DFOI