Results 1 to 6 of 6
  1. #1
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    May 2014
    Posts
    81

    Display all records if the referenced combo box is null in vba

    The short version: How do I get access to display all records if the referenced combo box is null in vba?

    The longer version:

    I’m comfortable making minor edits to VBA code but I still don’t fully understand it. The problem seems simple: I have four combo boxes that I want to use to filter a subform. I’ve used the criteria in the source query method, but over time I’ve run into issues with it jumbling data, hence why I turned to VBA.

    I posted in the forums just over a week ago (https://www.accessforums.net/showthread.php?t=78504) but I couldn’t get the code to work pbaldy suggested.
    Most recently I’ve followed the steps in this video and got it to work…mostly: https://youtu.be/M1J1rHXR4JY

    So the issue I have now is I get errors when one of the combo boxes doesn’t contain a selection. How do I tell access to display all records when the combobox value is null?

    Private Sub cboCostCenter_AfterUpdate()
    Dim SQL As String

    SQL = "SELECT tblEntry.EntryID, tblEntry.Entry_ProjectCodeIDfk, tblEntry.Entry_CostCenterIDfk, tblEntry.Entry_Description, " _
    & "tblEntry.Entry_ProductSKU, tblEntry.Entry_UnitPrice, tblEntry.Entry_QuantityofUnits, tblEntry.Entry_VendorIDfk, " _
    & "tblEntry.Entry_PurchaseOrderIDfk, tblEntry.Entry_EventYear, tblEntry.Entry_ReceiptDate, tblEntry.Entry_ReceiptNumber, " _
    & "tblEntry.Entry_BAFIDfk, qryEntry.Entry_TotalCost FROM qryEntry " _
    & "WHERE tblEntry.Entry_ProjectCodeIDfk=" & Me.[cboProjectCode] & "" _


    & "AND tblEntry.Entry_CostCenterIDfk=" & Me.[cboCostCenter] & "" _
    & "AND tblEntry.Entry_EventYear='" & Me.[cboYear] & "'"


    Me.frmEntry_sub.Form.RecordSource = SQL
    Me.frmEntry_sub.Form.Requery
    End Sub

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    In VBA, build your SQL string a bit at a time, testing each combobox. If a combobox is null, leave it out of the SQL string.

  3. #3
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    May 2014
    Posts
    81
    The string before the WHERE clause works fine. In fact, the whole string works fine if I fill the other combo boxes first before I fill in the combo box that activates the code. I guess that's my question is how do I tell access to ignore the blank combo boxes? Is it possible to just do something like an Nz funtions? ie: WHERE tblEntry.Entry_ProjectCodeIDfk=" & Nz(Me.[cboProjectCode],DISPLAY ALL) & ""

    Or do I have to do a separate If then filter type code?



  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Don't make iif's in the sql. Make the if's in VBA to build the SQL string. Tricky part is the AND connector if a cbo is null:

    Code:
    Private Sub cboCostCenter_AfterUpdate()
        Dim SQL As String
        Dim and1 as Boolean
        Dim and2 as Boolean
        and1 = false
        and2 = false
        SQL = "SELECT tblEntry.EntryID, tblEntry.Entry_ProjectCodeIDfk, tblEntry.Entry_CostCenterIDfk, tblEntry.Entry_Description, " _
        & "tblEntry.Entry_ProductSKU, tblEntry.Entry_UnitPrice, tblEntry.Entry_QuantityofUnits, tblEntry.Entry_VendorIDfk, " _
        & "tblEntry.Entry_PurchaseOrderIDfk, tblEntry.Entry_EventYear, tblEntry.Entry_ReceiptDate, tblEntry.Entry_ReceiptNumber, " _
        & "tblEntry.Entry_BAFIDfk, qryEntry.Entry_TotalCost FROM qryEntry " _
        & "WHERE "
        If not isnull(cboProjectCode) then
           SQL=SQL & "tblEntry.Entry_ProjectCodeIDfk=" & Me.[cboProjectCode] & " "
           and1 = true
       endif 
       If Not IsNull(cboCostCenter) then
           If and1 = true then
               SQL=SQL & "AND tblEntry.Entry_CostCenterIDfk=" & Me.[cboCostCenter] & " " 
          else
               SQL=SQL & "tblEntry.Entry_CostCenterIDfk=" & Me.[cboCostCenter] & " " 
          endif
          and2 = true
       endif 
       if not isnull(cboYear) then
          if and1 = true or and2 = true then
              SQL=SQL & "AND tblEntry.Entry_EventYear='" & Me.[cboYear] & "'"
         else 
              SQL=SQL & "tblEntry.Entry_EventYear='" & Me.[cboYear] & "'"
         endif 
       endif 
    
       Me.frmEntry_sub.Form.RecordSource = SQL
       Me.frmEntry_sub.Form.Requery
       End Sub 

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I would build the where clause using only those combos with a value.

    something like...

    Code:
    dim strWhere as string
    
    if nz(Me.[cboProjectCode],"")<>"" then
    strWhere=strWhere &  "tblEntry.Entry_ProjectCodeIDfk=" & Me.[cboProjectCode] & " And "
    end if
    
    if nz(Me.[cboCostCenter],"")<>"" then
    strWhere=strWhere &  " tblEntry.Entry_CostCenterIDfk=" &  Me.[cboCostCenter] & " And "
    end if
    
    if nz(Me.[cboYear],"")<>"" then
    strWhere=strWhere &  "tblEntry.Entry_EventYear=" &  Me.[cboYear] & " And "
    end if
    
    if nz(strWhere,"")<>"" then
    strWhere = " Where " & left(strWhere,len(strWhere)-4)
    end if
    
    
    SQL = "SELECT tblEntry.EntryID, tblEntry.Entry_ProjectCodeIDfk, tblEntry.Entry_CostCenterIDfk, tblEntry.Entry_Description, " _
    & "tblEntry.Entry_ProductSKU, tblEntry.Entry_UnitPrice, tblEntry.Entry_QuantityofUnits, tblEntry.Entry_VendorIDfk, " _
    & "tblEntry.Entry_PurchaseOrderIDfk, tblEntry.Entry_EventYear, tblEntry.Entry_ReceiptDate, tblEntry.Entry_ReceiptNumber, " _
    & "tblEntry.Entry_BAFIDfk, qryEntry.Entry_TotalCost FROM qryEntry " _
    &  strWhere
    
    

  6. #6
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    May 2014
    Posts
    81
    Thanks all. Moke123 I used the set-up as you suggested as it seems like a cleaner option if nothing else. I copied and pasted the same code to the AfterUpdate property of each combo-box. It works like a charm.

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

Similar Threads

  1. Message Box Display if Field is Null
    By dhicks in forum Forms
    Replies: 19
    Last Post: 07-14-2017, 01:54 PM
  2. Replies: 5
    Last Post: 08-31-2016, 06:04 PM
  3. Display NOT NULL COLUMN ONLY in query
    By ezybusy in forum Access
    Replies: 2
    Last Post: 12-10-2015, 09:32 AM
  4. Display zero instead of null? Access 2013
    By Lynn Cohen in forum Access
    Replies: 1
    Last Post: 07-20-2015, 09:50 AM
  5. Query to display null fields.
    By dazibit in forum Queries
    Replies: 1
    Last Post: 01-20-2014, 04:00 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