Selecting based on current user

[shabbaranks]

Hi guys,



Im trying to build up a select code which based on the logged in user, and thier selection selects from a table - but Im getting stuck. Currently I have a table which holds what user is a member of what group - how do I get the logged in users group reference from the users table?

Would I use a dlookup to query the logged in user and the group membership of that user? You'll have to bear with me on this as Im just starting off.

My current code which I have written ( I am missing how I am going to achieve the pulling the logged in user and group membership) is below

Private Sub SelectDeptbtn_Click()
Dim sUser As String
Dim sGroup As String
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim strSQL As String

Select Case Me.DepartmentIDCombo
Case "Admin"
A = 1
Case "Mech"
A = 2
Case "Software"
A = 3
Case "Elect"
A = 4
Case "Managers"
A = 5
End Select
Select Case sGroup
Case 2
B = 2
Case 3
B = 3
Case 4
B = 4
End Select
C = A + B
Select Case C
Case 3
strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description, TimesheetTable.sCostCode" & _
"FROM TimesheetTable " & _
"WHERE (((TimesheetTable.sCostCode) Like 'AD-LAB'));"
End Select
End Sub

Then Ive got the main form which pulls the username in

http://imgur.com/gTOlL

The table which holds the group membership based on the username

http://imgur.com/0R2Wi

Any advice is appreciated - thanks

[alansidman]

It looks like you are trying to do something along these lines.

http://www.mrexcel.com/forum/showthread.php?t=248191

Look at this tutorial and see if it helps you.

Alan

[shabbaranks]

Looks good Alan thanks. Im trying to setup a dlookup function to look at a column in a table for a value. The value could be 2,3 or 4 how would I achive this within the dlookup? Ive courrently got it set up like below:

Private Sub SelectDeptbtn_Click()
Dim sUser As String
Dim sGroup As String
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim strSQL As String
sGroup = DLookup("DepGroupID", "UserNames_tbl")
Select Case Me.DepartmentIDCombo
Case "Admin"
A = 1
Case "Mech"
A = 2
Case "Software"
A = 3
Case "Elect"
A = 4
Case "Managers"
A = 5
End Select
Select Case sGroup
Case 2
B = 2
Case 3
B = 3
Case 4
B = 4
End Select
C = A + B
Select Case C
Case 3
strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description, TimesheetTable.sCostCode" & _
"FROM TimesheetTable " & _
"WHERE (((TimesheetTable.sCostCode) Like 'AD-LAB'));"
End Select
End Sub

But the SelectDeptbtn_click does nothing, when I say nothing it doesnt even error.

Thanks

[alansidman]

Try putting your calculations after your case selects. You have them in the code before the variables have been populated. Did you try stepping through the code by setting break points and then advancing the code with F8? Debug. Look here and see how he does the debug.