Q:

Access 2013 Open Form Filter issue

I have an issue using VBA in Access 2013 to open a form with filters applied. I'm trying to filter the data for open records and the department the current user belongs to.
For the user I'm testing with, their department is 6, and the data table has 3 records whose department is 6 and IsOpen = Yes.
The command I'm using is below - the form opens, but displays no data when the Dlookup is included (if I just run it with the IsOpen query, it returns all open records).
If I put the Dlookup into a field on the form it returns the department number of the current user (in this case 6).
If I create a simple query to return the data where IsOpen = Yes, and the Department = 6, I get the expected 3 records returned.
Is there something wrong in my code? Or a better way of achieving what I need?
DoCmd.OpenForm "frm_ServiceRequestRegister", acNormal, "", IsOpen = True And Department = DLookup("[lkp_Department]", "tbl_Users", "[id] =" & [TempVars]![CurrentUser]), , acNormal

Any help would be appreciated.
Regards,
Garry.

A:

The key is that the Where parameter needs to be in quotes. It is a string that is passed to the OpenForm function. If you exclude the quotes the "where clause" is evaluated before anything is passed to OpenForm and either true or false is passed instead of what you want.
Dim userDept as long 'Maybe this is a string? I don't know your table
userDept = DLookup("[lkp_Department]", "tbl_Users", "[id] =" & [TempVars]![CurrentUser])

DoCmd.OpenForm "frm_ServiceRequestRegister", acNormal, "", "IsOpen = True And Department = " & userDept, , acNormal

The fact that this didn't generate an error says one or more of a few things. 

You don't have option explicit set at the top of your module (always have this checked Tools > Options > Editor > Require Variable Declaration)
You have some controls/fields in your data source that coincidentally allowed IsOpen = True And Department = DLookup("[lkp_Department]", "tbl_Users", "[id] =" & [TempVars]![CurrentUser]) to be evaluated as is.

Either way the way to debug this in the future is to step through the code and execute the parameters in the immediate pane. If they are not what you'd expect then something is the matter.

