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