Form Will Not Display All Records (1 Viewer)

JAB

Registered User.
Local time
Today, 00:57
Joined
Oct 14, 2004
Messages
21
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.

Thanks in advance.
John
 

conception_native_0123

Well-known member
Local time
Today, 02:57
Joined
Mar 13, 2021
Messages
1,826
John,

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?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:57
Joined
Feb 28, 2001
Messages
26,996
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.
 

plog

Banishment Pending
Local time
Today, 02:57
Joined
May 11, 2011
Messages
11,611
My guess is that not all your "blanks" are equal.

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?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:57
Joined
Jul 9, 2003
Messages
16,243
A computer process no data (NULL) differently than an empty string ("").

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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:57
Joined
May 7, 2009
Messages
19,169
what is the Criteria of your Query:

FieldName: Artist
Criteria: IIF(IsNull([Forms]![YourFormName]![ComboName]), [Artist], [Forms]![YourFormName]![ComboName])
 

JAB

Registered User.
Local time
Today, 00:57
Joined
Oct 14, 2004
Messages
21
Thank you all for your responses.

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.
 

Attachments

  • Database2.accdb
    664 KB · Views: 218

plog

Banishment Pending
Local time
Today, 02:57
Joined
May 11, 2011
Messages
11,611
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.

The solution is to set your form's Record Source to just the table itself (tblMedia) and then use Form.Filter (https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property)) to implement the filtering of it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:57
Joined
May 7, 2009
Messages
19,169
an alternative approach
 

Attachments

  • Database2.accdb
    672 KB · Views: 503

JAB

Registered User.
Local time
Today, 00:57
Joined
Oct 14, 2004
Messages
21
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.

Sorry for the long post, just wanted to share!

Mike

Moderator update:- Code Tags Added
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:57
Joined
Jul 9, 2003
Messages
16,243
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
 

Attachments

  • Form Will Not Display All Records_ATH_1a.zip
    61.9 KB · Views: 326

Users who are viewing this thread

Top Bottom