Results 1 to 3 of 3
  1. #1
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    33

    Use a parameter to return all records if Null


    I have a combobox on a continuous form that filters the detail pane based on what the user selects. I created a “select all” option for the combobox with an id of null through a UNION SELECT on the row source.
    For the criteria of the id, which is on the forms record source, I wanted the combobox to return all results with a matching id EXCEPT when the “select all” option is selected, in which case I wanted all results,to include null values, to be returned. So, I set the criteria as follows:

    Field: ID
    Table: tbl1Type
    Criteria: [Forms]![frm3PartN]![cboFltrType]
    Or: [Forms]![frm3PartN]![cboFltrType] Is Null

    After testing this out, it works! However, When I re open the query, Access rewrites the query by removing the “Or” statement and assigning the combobox it’s own field. The newly assigned field looks like this:

    Field: [Forms]![frm3PartN]![cboFltrType]
    Table: blank
    Criteria: blank
    Or: Is Null

    While I don’t have any problems with it doing that, the problem is that whenever I close the current database and re open it, it asks for the parameter value of [Forms]![frm3PartN]![cboFltrType]. After selecting ok, it next tells me the following

    “the expression on Current you entered as the event property setting produced the following error: Object or class does not support the set of events.”

    I’m guessing the error is a result of Access rewriting the query by assigning [Forms]![frm3PartN]![cboFltrType] its own field as an “expression”. So, I have to change it back to how I originally had it for it work again. Of course, changing it back every time I reopen the file is not a good solution.

    So, is there a way I can disable the query rewrite? Or is theresomething additional I can type into the criteria to circumvent this? Or isthere a better way of going about this? I want to try and keep the solution assimple as possible. I'm hoping one of you gurus may understand what is going on? Thank you for your help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,042
    I tested this and I don't get that prompt nor error. Is this query the source for a form set to open by default? Or is it a subform?

    If you want to provide db for analysis, follow instructions at bottom of my post.

    I don't use dynamic parameterized queries. I use VBA to apply filter to form or report. Review http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    33
    June7,

    Thank you for that reference. I redid my control form using VBA and it operates much better. I have used a lot of VBA in Excel but didn’t know where to start in Access. This was a great starting point.

    I recommend a small addition to Allen Browne’s script. A small problem you will encounter is that he only tests if the checkbox in the details section is on (-1) or off (0). He says that it isn’t necessary to test for the both condition (1) since “we add nothing to the filter string for other cases (1 or Null).”

    Code:
     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
        If Me.cboFilterIsCorporate = -1 Then
            strWhere = strWhere & "([IsCorporate] = True) AND "
        ElseIf Me.cboFilterIsCorporate = 0 Then
            strWhere = strWhere & "([IsCorporate] = False) AND "
        End If
    While this will still work for most cases, we run into a problem if the only thing we want to filter is the “both” condition(1) because no other criteria is entered and therefore, the routine will default to his error message “no criteria”. To avoid this we should still test for the “both” condition (1)

    Code:
      'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
        If Me.cboFilterIsCorporate = -1 Then
            strWhere = strWhere & "([IsCorporate] = True) AND "
        ElseIf Me.cboFilterIsCorporate = 0 Then
            strWhere = strWhere & "([IsCorporate] = False) AND "
        ElseIf Me.cboFilterIsCorporate = 1 Then
            strWhere = strWhere & "([IsCorporate] = True Or [IsCorporate] = False) AND "
        End If
    By adding the third test condition for(1) we now avoid the error message IF no other criteria is entered.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Return records if null
    By Sabosis in forum Queries
    Replies: 2
    Last Post: 12-05-2017, 05:26 PM
  2. Replies: 2
    Last Post: 10-01-2015, 10:10 AM
  3. Replies: 5
    Last Post: 03-02-2015, 03:07 PM
  4. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  5. Return Null as Zero
    By bbshriver in forum Reports
    Replies: 12
    Last Post: 10-19-2010, 01:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums