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.

2 answers

  • answered 2018-02-13 02:26 Brad

    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.

    1. You don't have option explicit set at the top of your module (always have this checked Tools > Options > Editor > Require Variable Declaration)
    2. 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.

  • answered 2018-02-13 02:26 Rafael Amorim

    You should separate the fixed part of the variable part in the where statement by enclosing them with quotation marks and joining with '&', like this: "IsOpen = True And Department =" & DLookup ("[lkp_Department]", "tbl_Users" [id] = "& [TempVars]! [CurrentUser]),,acNormal