list box data based on logged in users department

[cbrxxrider]

I have a list box with a row source query as follows...

SELECT qryPendingCompletions2.ID, qryPendingCompletions2.Department, qryPendingCompletions2.[Employee Name], qryPendingCompletions2.CompletionDate AS [Completion Date], qryPendingCompletions2.[Entered By], qryPendingCompletions2.Goal, qryPendingCompletions2.Comments, qryPendingCompletions2.[Verified By]
FROM qryPendingCompletions2
WHERE (((qryPendingCompletions2.Department)=[cbocurrentemployee].[column](3)) AND ((qryPendingCompletions2.[Verified By]) Is Null))
ORDER BY qryPendingCompletions2.Department;

I also have a combo box at the top of the form showing what user is logged in where the row source query is the following...

SELECT Employees.UserID, [First Name] & " " & [Last Name] AS EFullname, Employees.AccessLevelID, Employees.Department
FROM Employees
WHERE (((Employees.AccessLevelID)=1 Or (Employees.AccessLevelID)=2 Or (Employees.AccessLevelID)=3 Or (Employees.AccessLevelID)=4));

I'm trying to figure out how to only list the data in the list box when the department is the same as the logged in user.

So I'd like to add the criteria to the first query something like criteria = [cbocurrentemployee].[column](3) but this doesn't seem to work as criteria in queries.

Thanks.

 

[JHB]

You can only get value from the column a combobox is bound to, so you need a little trick.
You can place a text control on your form and set the control source to:

=[cbocurrentemployee].[column](3)

Then you can refer to it in the query.

Or you can use the [cbocurrentemployee] Tag property, and place the value from [cbocurrentemployee].[column](3) in it, run some in the after update event for the [cbocurrentemployee].
Then you can refer to it in the query:

WHERE ....[cbocurrentemployee].[Tag] ...

 

[cbrxxrider]

You can only get value from the column a combobox is bound to, so you need a little trick.
You can place a text control on your form and set the control source to:

=[cbocurrentemployee].[column](3)

Then you can refer to it in the query.

Or you can use the [cbocurrentemployee] Tag property, and place the value from [cbocurrentemployee].[column](3) in it, run some in the after update event for the [cbocurrentemployee].
Then you can refer to it in the query:

WHERE ....[cbocurrentemployee].[Tag] ...

Sounds like it should work and much easier than having to figure out a bunch of coding. I'll give it a shot, thanks.

 

[cbrxxrider]

You can only get value from the column a combobox is bound to, so you need a little trick.
You can place a text control on your form and set the control source to:

=[cbocurrentemployee].[column](3)

Then you can refer to it in the query.

Or you can use the [cbocurrentemployee] Tag property, and place the value from [cbocurrentemployee].[column](3) in it, run some in the after update event for the [cbocurrentemployee].
Then you can refer to it in the query:

WHERE ....[cbocurrentemployee].[Tag] ...

The txt control trick worked great. Do you know how I could slightly alter this to allow either 1 person or 1 department to see all the data.

for example, I could set up yet another txt control to show whether they are a developer or supervisor and I'd like the developer level to be able to see unfiltered data.

I appreciate the previous advice either way.