Querying different table\form from current one

[shabbaranks]

Hi

Im not too sure if this is the best way to go about it so I am definitely open to suggestions. Im trying to reference a column in a table which holds which group a person is in. I am then using select case to tally up if they can run a query. The problem is I cant get VBA to recognize the field within the table which holds the values. Is it not "best practice" to reference fields in tables should I be referencing forms? If so how do I get a value to a form based on this table structure?

My current code is below:

Option Compare Database

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 = [UserNames_tbl]![DepGroupID].[column3]

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

Thanks as always!

 

[pbaldy]

You can't refer to a table value that way. Try a DLookup().

 

[shabbaranks]

Is it possible to get dlookup to look within a column as opposed to a specific value within a column? As based on my code above sGroup could equal different values depending on what group the user is in??

Thanks

 

[pbaldy]

I'm sorry, I don't really understand what you're trying to do. With criteria, a DLookup() can pull a specific record's information. You could certainly change the field in the first argument.