I have a continuous form connected to tblMedia, containing right now vinyl LPs, DVDs, and Blue-Rays. When the form was created there were 280 records for vinyl LPs and the form could be filtered using a combo box for Artist. The combo box can also display blank and all records will show. I have now added 56 records for DVDs and 8 for Blue-Rays, neither of which have a value in the Artist field (but there is a value in Format and Title). However the form still shows only the 280 LPs, even when I select blank in the filtering combo box. I added an artist to one of the DVDs and it now displays along with the LPs. Further, I have added a combo box to filter on format (Vinyl, DVD, etc., blank for all). Nothing happens no matter what value I select. Both queries have the Event procedure set on After Update with the code DoCmd.Requery. I am using MS 365.
It sounds like "blank" is not serving the purpose that you want it to serve. can you post the sql you are using for the query that sources the form's records and the sql for the box? or even better, which others would probably say too, can you post the entire file?
If adding a value to a field suddenly enables the display of that field, it sounds like you have a filter that is somehow incorrectly concerned with nulls. As @conception_native_0123 suggests, we would need to see more than you told us in that first post.
If you have a combo box, it has a property called .RowSource, probably an SQL statement we would need to see. But what else makes me wonder is that you are describing at least two filters apparently intended to affect a single form. So how are you actually implementing the filtration step? I think that will be a legit area of concern as well.
First, "blank" is an ambiguous term and doesn't mean anything to a computer. A computer process no data (NULL) differently than an empty string (""). To human eyes and even minds the are the same. Not to computers.
My guess is your filter is set up to allow Nulls or empty strings, but not both. And those 56 new records are different than the first 280. Can you post the filter criteria and tell me the name of your table and the field we are talking about?
The other thing to watch out for is invisible characters like linefeed.
This can happen if you import data from a spreadsheet. You might see a field which says "LP" but actually the field contains "LP" "Vinyl" ... But you won't see vinyl because it is on a row below in the field/textbox where you can't see it.
To check for this, make a temporary form and increase the size of the text boxes to allow you to see any extra lines.
First, for the combo box that filters on Format, I had set up the event procedure but forgot to put the criteria statement in the forms underlying query. Doh! This is now fixed and functioning properly.
The Artist filter is still an issue. This field and all the other text fields in the table are set to Allow Zero Length = Yes. As I mentioned, I populate the combo box (cboArtist) with a union query combining “ “ with the list of artists from tblMedia. The “ “ or blank row displays at the top of the list and when selected, all records should be included in the output of the form. Interesting (to me), this list now includes two blank rows at the top and this makes sense since in the table there are records with no value entered for the artist.
The comment about line feed is a good one but shouldn’t be an issue. While I stated Vinyl LPs the actual description brought into the table was just “Vinyl”.
The filter criteria in the forms underlying query for both the Format and Artist are:
Like ([Forms]![frmMedia]![cboFormat]) & "*"
Like ([Forms]![frmMedia]![cboArtist]) & "*"
I did make one change in the SQL and that was to make the filter criteria and OR instead of AND. With this all 344 records display when the form is first opened but did not resolve the filtering issue so I have changed it back to AND.
When I use this form I want it to initially display all records. From here I may edit or add a new record. Sometimes I may want to view only a certain format like the DVDs, other times I may want to view only a given artist, regardless of format, or I may wish to use both filters at once. I have other databases with where I filter on forms in a similar fashion, some with 3 or 4 filters and these all work fine. The difference is they are working on Office 2010 Access v 14.0. I mention this because it seems I have been having more issues under MS 365 than I did under Office. Could part of my problem be I don’t have the right database tools/references selected? This database and others have all been migrated from older .mdb to .accdb files.
I copied the table and form into the attached database.
The culprit is NULL. Your query criteria is always going to exclude Nulls. Like I said before NULL is not an empty string its the complete lack of data, you cannot compare it to anything except when you explicitly compare it to NULL. But your query criteria doesn't do that.
Thank you both plog and arnegp for your solutions. I ended up using the reference provided by plog to set up my filters. I was still having an issue when filtering with multiple combo boxes but found an elegant solution on another forum, one the author had posted out there to share with others.
From a Michael Kenneth on 9/24/13:
Every so often my team observes someone whose face is familiar, but they can not recall who the person is. In this case they need to search the database by the person's description. I provided a form to search for people, but was stumped for the longest time on how to provide a quick multiple-column search to filter the form.
The solution involved placing several comboboxes (gender, hair, eyes, etc) on the form's Header, and setting each rowsource to contain the searchable values. In the AfterUpdate event, I manipulate the comboboxes' "TAG" property so that when the box is updated, the TAG value is set to the search statement like this:
Code:
Private Sub gender_AfterUpdate()
On Error Resume Next
If Not IsNull(Me.gender) Then
Me.gender.TAG = "[Gender]='" & Me.gender & "'"
Else
Me.gender.TAG = ""
End If
Call FilterTest
End Sub
Each time a combobox is changed it also calls FilterTest, which looks through each control to find the search criteria and then does this:
Code:
Private Sub FilterTest()
On Error Resume Next
Dim ctl As Control
Dim strFilter as string
strFilter = ""
For Each ctl In Me.FormHeader.Controls
If ctl.ControlType = acComboBox Then
If Len(ctl.TAG) > 1 Then
If Len(strFilter) < 1 Then
strFilter = ctl.TAG
Else
strFilter = strFilter & " And " & ctl.TAG
End If
End If
End If
Next ctl
If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub
So whether my users select only one combobox or all comboboxes, the form is filtered correctly. It also updates on the fly after any single search value is changed.
This solution will build the filter for any number of combo boxes automatically. The combo box label should contain the name of the field that the combobox is searching. In the event that the combo-box label caption contains something other than the field it searches, then change the code so that it looks in the combo box label Tag Property. Put the name of the field to search in the combo_box label Tag.
Code:
Option Compare Database
Option Explicit
'############## ---------------- Control Events ---------------------- ########
Private Sub cboArtist_AfterUpdate()
Me.Filter = ""
Me.Filter = fCreateFilter
Me.FilterOn = True
End Sub
Private Sub cboFormat_AfterUpdate()
Me.Filter = ""
Me.Filter = fCreateFilter
Me.FilterOn = True
End Sub
'############## --------------- Private Subs/Functions --------------- ########
Private Function fCreateFilter() As String
Dim conAppName As String
conAppName = "(Replace this Local Constant with a Global One) "
Dim strSubName As String
Dim strModuleName As String
strSubName = "fCreateFilter"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"
On Error GoTo Error_Handler
Dim strFilter As String
Dim Ctrl As Control
For Each Ctrl In Me.FormHeader.Controls
Select Case Ctrl.ControlType
Case acComboBox
Dim strCritera As String
If Ctrl.Value = "" Or IsNull(Ctrl) Then
strCritera = ""
Else
strCritera = fGetLabel(Me, Ctrl) & "=" & Chr(39) & Ctrl.Value & Chr(39) & " AND "
End If
If strFilter = "" Then
strFilter = strCritera
Else
strFilter = strFilter & strCritera
End If
End Select
Next Ctrl
'Needed for NONE SELECTED
If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Else
strFilter = ""
End If
fCreateFilter = strFilter
Exit_ErrorHandler:
Exit Function
Error_Handler: 'Version - 1a
Dim strErrFrom As String
Dim strErrInfo As String
strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
Select Case Err.Number
Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
Case Else
MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
End Select
Resume Exit_ErrorHandler
End Function 'fCreateFilter
'############## ---------------- Helper Routines --------------------- ########
Private Function fHasLabel(oFormPassed As Form, strCtrlName As String) As Boolean
'This function Returns True if the control "Name" entered has an associated label.
'Used in "fGetLabel"
Dim Ctrl As Control
For Each Ctrl In oFormPassed
If Ctrl.ControlType = acLabel Then
If Ctrl.Parent.Name = strCtrlName Then fHasLabel = True
End If
Next
End Function 'fHasLabel
Private Function fGetLabel(oFormPassed As Form, oCtrl As Control) As String
'Extract the caption from the Associate Controls label...
If fHasLabel(oFormPassed, oCtrl.Name) Then
fGetLabel = oCtrl.Controls(0).Caption
'fGetLabel = oCtrl.Controls(0).Tag
Else
fGetLabel = ""
End If
End Function 'fGetLabel
