How to use dlookup to filter a query instead of a table join

[annah]

I have a form where users are only allowed to see work assignments that belong to them. This is currently accomplished using a join between my employee table and my work assignment table, and a custom defined function that returns the logon id that is currently using the workstation to use as a criteria in the query. The employee table contains the employee's name and their logon id, and the work assignment table contains the employee's name.



This creates a recordset that is not updateable and there are a few fields that I want the employees to be able to edit.

I've used dlookup before to return values but not to use as a filter. I would like to be able to remove the join and just use dlookup as a criteria on the employee's name from the work assignment table but I'm struggling with syntax and I can't find an example on google.

Thanks for your help!

[aytee111]

Use a filter on the form instead. The form's record source can be the work assignment table and OnOpen or OnLoad set the form's filter:

Me.Filter="EmployeeName='" & DLookup("EmployeeName","EmployeeTable","LogonID='" & functionname & "'") & "'"
Me.FilterOn=True

[annah]

Yeah that doesn't work because the user can just remove filter the form and then they will see everything else.

I did figure out what to do though in case anyone has a similar question. Instead of trying to put the dlookup as a criteria under the employee's name, I set up a custom column in the query called "login" which returned the value of the dlookup. I then set the criteria for that column to be the custom function which returns the current user login id.

[aytee111]

Forms have a property - AllowFilters - which you can set to No.

[annah]

True, but then my users wouldn't be able to apply their own filters, which they need to be able to do.

Forms have a property - AllowFilters - which you can set to No.

[John_G]

This creates a recordset that is not updateable and there are a few fields that I want the employees to be able to edit.

Try putting an index on the employee name field in both tables.