Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    ListBox criteria not showing up when some fields are null?

    Hi all,


    My listbox on primary form will not add the business if the BusinessEntityID & BusinessAddressID, & BusinessPhoneID are Null?
    Is there a work around for this so that if I add a new business but not an Entity or address or a phone to it that it would still add to this listbox?
    I cant really load up the db any longer as its just to big for this site size. So, below I have added a screen shot, Query, the Row Source, and the search filter info as well as a screen shot of primary form.
    Just checking to see if there is somehting I can do to allow it to put in the business record without all the entity, address and phone info as sometimes I dont have all of that but I still want the business in
    the search listbox.
    Thank you,
    Dave
    Code:
    'QryBusinessSearch
    
    
    SELECT DISTINCT tblBusiness.BusinessID, tblBusiness.BusinessName, tblCategory.CategoryName, tblCategorySub.SubCategoryName," _
    & "tblBusinessEntity.DBA, tblCategory.CategoryID, tblCategorySub.SubCategoryID, tblCategoryEntity.CategoryEntityID," _
    & "tblBusinessEntity.BusinessEntityID, tblBusinessAddress.BusinessAddressID, tblBusinessAddress.AddressTypeID, tblCboData.CboDataValue," _
    & "tblBusinessAddress.Address, tblBusinessAddress.AddressCont, tblBusinessAddress.City, tblBusinessAddress.State," _
    & "tblBusinessAddress.ZipCode, tblBusinessAddress.County, tblBusinessEntity.Reference, Trim([CategoryEntityName] & "/" & [Reference]) AS EntityReference," _
    & "IIf([tblBusiness].[IsActive]=-1,'Yes','No') AS Active, IIf([tblBusiness].[IsPerferred]=-1,'Yes','No') AS Preferred," _
    & "IIf([tblBusiness].[NoSolicit]=-1,'Yes','No') AS Solicit, tblBusiness.IsActive, tblBusiness.IsPerferred, tblBusiness.NoSolicit," _
    & "tblCategoryEntity.CategoryEntityName, tblBusinessPhone.BusinessPhoneID, tblBusinessPhone.BusinessPhoneTypeID, tblBusinessPhone.PhoneNumber" _
    & "FROM ((tblBusiness INNER JOIN tblCategory ON tblBusiness.CategoryID = tblCategory.CategoryID)" _
    & "INNER JOIN tblCategorySub ON tblBusiness.SubCategoryID = tblCategorySub.SubCategoryID) INNER JOIN" _
    & "(((tblBusinessEntity INNER JOIN tblCategoryEntity ON tblBusinessEntity.CategoryEntityID = tblCategoryEntity.CategoryEntityID)" _
    & "INNER JOIN (tblCboData INNER JOIN tblBusinessAddress ON tblCboData.CboDataID = tblBusinessAddress.AddressTypeID)" _
    & "ON tblBusinessEntity.BusinessEntityID = tblBusinessAddress.BusinessEntityID) INNER JOIN tblBusinessPhone" _
    & "ON tblBusinessEntity.BusinessEntityID = tblBusinessPhone.BusinessEntityID) ON tblBusiness.BusinessID = tblBusinessEntity.BusinessID;"
    Code:
    'List Box Rowsource
    "SELECT DISTINCT qryBusinessSearch.BusinessID, qryBusinessSearch.BusinessName, qryBusinessSearch.CategoryName, qryBusinessSearch.SubCategoryName," _
    & "qryBusinessSearch.CategoryID, qryBusinessSearch.SubCategoryID FROM qryBusinessSearch;"
    Code:
    'Reset Button
            Me.LstBusinessSearch.RowSource = "SELECT DISTINCT qryBusinessSearch.BusinessID, qryBusinessSearch.BusinessName," _
         & "qryBusinessSearch.CategoryName, qryBusinessSearch.SubCategoryName," _
    & "qryBusinessSearch.CategoryID," _
         & "qryBusinessSearch.SubCategoryID" _
         & " FROM qryBusinessSearch " _
         & " ORDER BY qryBusinessSearch.[BusinessName];"
    Code:
    'Search Filter 
    
    
    Public Const b As String = "SELECT DISTINCT BusinessID, BusinessName, CategoryName, SubCategoryName" _
    & "FROM (SELECT DISTINCT BusinessID, BusinessName, CategoryName, SubCategoryName, CategoryID, SubCategoryID, DBA," _
    & "CategoryEntityID, BusinessEntityID, BusinessAddressID, AddressTypeID, cboDataValue, Address, City, State, ZipCode," _
    & "County, Reference, EntityReference, Active, Preferred, Solicit, BusinessPhoneID, PhoneNumber From qryBusinessSearch)"
    
    
    Function bListBoxFilter()
       'this is the main procedure for filtering
        Dim strWhere As String
        Dim lngLen As Long
       
       'check FilterBusinessName
        Forms!frmBusiness.FilterBusinessName.SetFocus
        If Len(Forms!frmBusiness.FilterBusinessName.Text) & "" > 0 Then
          strWhere = strWhere & "([BusinessName] LIKE ""*" & Forms!frmBusiness.FilterBusinessName.Text & "*"") And "
        End If
    
    
       'check FilterDBA
        Forms!frmBusiness.FilterDBA.SetFocus
        If Len(Forms!frmBusiness.FilterDBA.Text) & "" > 0 Then
          strWhere = strWhere & "([DBA] LIKE ""*" & Forms!frmBusiness.FilterDBA.Text & "*"") And "
        End If
    
    
        'check cboFilterIndustry
        If Len(Forms!frmBusiness.cboFilterIndustry) > 0 Then
          strWhere = strWhere & "([CategoryID] = cboFilterIndustry) And "
        End If
    
    
        'check cboFilterCategory
        If Len(Forms!frmBusiness.cboFilterCategory) > 0 Then
          strWhere = strWhere & "([SubCategoryID] = cboFilterCategory) And "
        End If
    
    
        'check cboFilterEntity
        If Len(Forms!frmBusiness.cboFilterEntity) > 0 Then
          strWhere = strWhere & "([CategoryEntityID] = cboFilterEntity) And "
        End If
    
    
        'check cboFilterCity
        If Len(Forms!frmBusiness.cboFilterCity) > 0 Then
          strWhere = strWhere & "([City] = cboFilterCity) And "
        End If
    
    
        'check cboFilterState
        If Len(Forms!frmBusiness.cboFilterState) > 0 Then
          strWhere = strWhere & "([State] = cboFilterState) And "
        End If
    
    
        'check cboFilterZip
        If Len(Forms!frmBusiness.cboFilterZip) > 0 Then
          strWhere = strWhere & "([ZipCode] = cboFilterZip) And "
        End If
    
    
        'check cboFilterCounty
        If Len(Forms!frmBusiness.cboFilterCounty) > 0 Then
          strWhere = strWhere & "([County] = cboFilterCounty) And "
        End If
    
    
       'check FilterAddress
        Forms!frmBusiness.FilterAddress.SetFocus
        If Len(Forms!frmBusiness.FilterAddress.Text) & "" > 0 Then
          strWhere = strWhere & "([Address] LIKE ""*" & Forms!frmBusiness.FilterAddress.Text & "*"") And "
        End If
    
    
       'check FilterReference
        Forms!frmBusiness.FilterReference.SetFocus
        If Len(Forms!frmBusiness.FilterReference.Text) & "" > 0 Then
          strWhere = strWhere & "([Reference] LIKE ""*" & Forms!frmBusiness.FilterReference.Text & "*"") And "
        End If
    
    
       'check FilterIsActive
        If Forms!frmBusiness.FilterIsActive = True Then
          strWhere = strWhere & "([Active] = 'Yes') And "
        End If
    
    
       'check FilterBusinessIsPreferred
        If Forms!frmBusiness.FilterIsPreferred = True Then
          strWhere = strWhere & "([Preferred] = 'Yes') And "
        End If
    
    
       'check FilterBusinessNoSolicit
        If Forms!frmBusiness.FilterNoSolicit = True Then
          strWhere = strWhere & "([NoSolicit] = 'Yes') And "
        End If
        
       'check FilterPhoneNumber
        Forms!frmBusiness.FilterPhoneNumber.SetFocus
        If Len(Forms!frmBusiness.FilterPhoneNumber.Text) & "" > 0 Then
          strWhere = strWhere & "([PhoneNumber] LIKE ""*" & Forms!frmBusiness.FilterPhoneNumber.Text & "*"") And "
        End If
        
        'chop off the last 5 character
        lngLen = Len(strWhere) - 5
        If lngLen > 0 Then    '<<-- if  Len of strWHERE is > 0, then there must be a filter to apply
            strWhere = Left(strWhere, lngLen)
            strWhere = " WHERE " & strWhere  ' add "WHERE to the filter string (strWHERE)
                    
            Forms!frmBusiness!LblBusinessAvailable.Caption = "Business's Filtered"
        Else
            Forms!frmBusiness!LblBusinessAvailable.Caption = "Business's Avaliable"
        End If
        
    '    Debug.Print Len(strWhere)
    '    Debug.Print b
    '    Debug.Print b & strWhere
        
        Forms!frmBusiness.LstBusinessSearch.RowSource = b & strWhere
        'Me.LstBusinessSearch.Requery
        
    End Function
    Click image for larger version. 

Name:	PrimaryForm.jpg 
Views:	19 
Size:	245.2 KB 
ID:	48780

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Create a 'dummy' entry to assign to, then amend when you have all the details?

    And judging by the amount of times you post, best set up to create a mini DB for upload, fo the times when just seeing code is not going to work.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    ProfileTest.zip

    Hi Welshgasman,
    Thanks and I was able to get rid of some tables to upload db!
    Didnt think it would open correctly without some of the folders in the db folder but seems to open fine!
    All info is just test so no worries!
    Dave

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    And just how am I meant to replicate this?

    Remember I have no idea as to how all this works, or is meant to work?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    This is what I get to see, your colours on my laptop make the text almost invisible?

    Just pressed the Add button, put in a Business Name and then tried the Save button.

    I get an error 2164 (you cannot disable a control while it has the focus) because you are trying to disable the Save button in it's Click event?

    Do you test your code? How is that meant to work?

    Code:
    Private Sub cmdSaveBusiness_Click()
        DoCmd.RunCommand acCmdSaveRecord
        If Not Me.NewRecord Then
            Modified = Now()
        End If
        Call Form_Current
        
            Me.AllowAdditions = True
            Me.AllowEdits = True
            Me.cmdSaveBusiness.Enabled = False ' ERRORS HERE !!
            Me.cmdUnDoBusiness.Enabled = False
    End Sub
    I *think* you will need to amend your joins for the listbox, but when I try I get ambiguous outer joins.
    Attached Thumbnails Attached Thumbnails bad coolurs.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You appear to have coded this for someone who knows all the steps required and in which is the correct order?

    Almost everything I click on, produces an error?
    In the top right subform, I can make all records checkbox as Primary?. When I unticked Active, it produced another record.
    Attached Thumbnails Attached Thumbnails Primary.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Welshgasman,
    Thanks for looking at it! I dont get any errors on my origional but with removing some stuff to make it small enough to upload, i guess something wont work or it will produce errors.
    I guess I will just have to add unknow files when i dont know an address or phone for now anyway. I am not getting any errors on my end?
    Thanks

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well my best guess, and I have not seen how to even do this, is assign them to dummy values, then amend when you know the correct data.
    Easy enough to produce a report that shows anything still assigned to dummy, when they should not be?

    However no matter what you do with the DB, you cannot disable a control that has the focus, so that needs to be fixed, regardless. I just commented it out to get the save to work.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Consider using this updated SQL as your qryBusinessSearch query (I replaced your equi joins with outer joins):
    Code:
    SELECT DISTINCT tblBusiness.BusinessID, tblBusiness.BusinessName, tblCategory.CategoryName, tblCategorySub.SubCategoryName, tblBusinessEntity.DBA, tblCategory.CategoryID, tblCategorySub.SubCategoryID, tblCategoryEntity.CategoryEntityID, tblBusinessEntity.BusinessEntityID, tblBusinessAddress.BusinessAddressID, tblBusinessAddress.AddressTypeID, tblCboData.CboDataValue, tblBusinessAddress.Address, tblBusinessAddress.AddressCont, tblBusinessAddress.City, tblBusinessAddress.State, tblBusinessAddress.ZipCode, tblBusinessAddress.County, tblBusinessEntity.Reference, Trim([CategoryEntityName] & "/" & [Reference]) AS EntityReference, IIf([tblBusiness].[IsActive]=-1,'Yes','No') AS Active, IIf([tblBusiness].[IsPerferred]=-1,'Yes','No') AS Preferred, IIf([tblBusiness].[NoSolicit]=-1,'Yes','No') AS Solicit, tblBusiness.IsActive, tblBusiness.IsPerferred, tblBusiness.NoSolicit, tblCategoryEntity.CategoryEntityName, tblBusinessPhone.BusinessPhoneID, tblBusinessPhone.BusinessPhoneTypeID, tblBusinessPhone.PhoneNumberFROM ((tblBusiness LEFT JOIN tblCategory ON tblBusiness.CategoryID = tblCategory.CategoryID) LEFT JOIN tblCategorySub ON tblBusiness.SubCategoryID = tblCategorySub.SubCategoryID) LEFT JOIN (((tblBusinessEntity LEFT JOIN tblCategoryEntity ON tblBusinessEntity.CategoryEntityID = tblCategoryEntity.CategoryEntityID) LEFT JOIN (tblCboData RIGHT JOIN tblBusinessAddress ON tblCboData.CboDataID = tblBusinessAddress.AddressTypeID) ON tblBusinessEntity.BusinessEntityID = tblBusinessAddress.BusinessEntityID) LEFT JOIN tblBusinessPhone ON tblBusinessEntity.BusinessEntityID = tblBusinessPhone.BusinessEntityID) ON tblBusiness.BusinessID = tblBusinessEntity.BusinessID;
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Gicu,
    I ran this in a new query but received the following?

    Thanks
    Dave

    Click image for larger version. 

Name:	syntax error.jpg 
Views:	12 
Size:	72.4 KB 
ID:	48791

  11. #11
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Sorry Gici,
    I found it, was space in front of from
    Fixed it, will see how it works

  12. #12
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi and thank you Gicu!
    That worked like a charm... Oh Thank you!!!!!!
    Now I have to figure out what you did and do it to my BusinessContacts too as that has same issue
    Thanks again....
    Dave

  13. #13
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Sorry, the qry worked but still didnt come up in listbox! I though you did it and in some respect you did!
    Thanks for looking at it and taking time on it.
    dave

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Which record in the sample you uploaded are you talking about?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    ProfileNewTest.zip
    Hi Guci,
    The sample db I uploaded. If you add a new record to the frmBusiness, it wont go in list box until you enter all info such as Entity Type, Address Type, and Phone Type. It wants it all to go into list box.
    It does same thing in the tab Team Members. I cant just add a contact without filling in all the phone, email, and Title. The ones that are in the sample if I didnt have a phone such i just put in 000-000-0000 or Unknown to get it into
    list box.

    In both cases it saves to table but wont come up in query so that it shows up in my list box? Beats me for sure
    If you look, it has 5 records in list box but yet it has 6 records in table...

    Welshgasman had issues with it opening up? I just added another fake business to it with no errors but i now have 5 business in listbox and 7 businesses in tbl

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 01-05-2018, 06:31 PM
  2. Replies: 3
    Last Post: 11-20-2014, 12:18 PM
  3. Replies: 3
    Last Post: 12-13-2012, 04:40 AM
  4. Replies: 1
    Last Post: 02-23-2012, 02:27 PM
  5. Replies: 1
    Last Post: 02-25-2011, 06:11 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