Form Filter Subform Not Listing Result Set Correctly

So thank you to all, first and foremost, to everyone who has been so kind to help me out with this form filter I have been working on and in the process helping me learn a little VBA.

My issue now is with using a subform for displaying the form header section's results (the form header has multiple search boxes that I can fill out and click Enter or hit a command button to query for the results).

The reason for needing to use a subform is that there are so many fields needed, it stretches horizontally all the way to the maximum 22 inches and won't stretch any farther. Besides that, I need to be able to keep all of the information in view (both the form header and the subform result set)

I built a little subform that gets the correct query result sets, but the problem is that it doesn't display the same way that I had it before where it would list all of the results. Now, with the subform, it lists only the first one it finds and has the Record box in the bottom left corner (example: 1 of 3, etc., etc.) so that I would have to keep clicking the right arrow to go one-by-one through the result set........How tedious and unmanageable???

So at first I thought it was because I wasn't linking enough of the fields together between the main form and subform. So I just linked all of them and that didn't do anything different. I tried just linking the Key ID field but that didn't do anything different.

So I'm not sure what to try to get the subform to list the results the way they should. That is, to list them all in one result set so I can use the vertical scroll bar if I needed to.

Please help me with this as I will try to keep searching for a solution in the meantime.....and thank you for all the hard work.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Wow ... calm down a little. This is the weekend, and many of us spend time with our families and friends. We all VOLUNTEER our time, after all, and are not here to act as your personal employee.


1) Remove the values in Master and Child links for the subform.

2) Change the code at the bottom of cmd_Filter to this:

If lngLen <= 0 Then     'Nah: there was nothing in the string.
        If Me.ActiveControl.Name = "cmdFilter" Then MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'Finally, apply the string as the form's Filter.
        Me.Frm_Provider_Lookup_subfrm.Form.Filter = strWhere
        Me.Frm_Provider_Lookup_subfrm.Form.FilterOn = True
    End If

In general, with Filter forms you should not be dealing with a bound "main" form. You can have a bound subform, and set the recordsource or filter of that subform, of course.
IEHP1Author Commented:
OK I just figured something probably obvious to anyone reading this (duh!!)

I switched the subform to Datasheet View and each of the search boxes show as column headings instead of using Labels I had thought I had to do to show them in a subform.

But what is weird to me is that you know how in the Main form and the Subform, they both have the bottom left corner where it says Record 1 of # (however many show in the result set)?  

Well if I put in San Bernardino into the City textbox and search on that (knowing that there should be 3 results that show up), it says Record 1 of 2 in the subform with the first KeyID it finds and then in the Main form, it shows as Record 1 of 3??

I'm thinking it is the linking of the Master and Child fields again? As of now I am only linking on the KeyID.....but I thought that should be enough??
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would presume it's got to do with the Master/Child links, but without knowing more about the database it's hard to say. Basically, you should link the Master and Child forms based on the Master/Child relationships in the respective tables.

Let's say my Main form is based on the Customers table, and my Child (i.e. Sub) form is based on the Invoices table. The Customer table Primary Key is "CustomerID", so in order to relate my Invoices table to the right Customer, I'm storing the value from Customer.CustomerID in Invoices.CustomerID. If that's the case, then in my Mainform/Childform setup, I'd set the Master/Child links to be CustomerID/CustomerID. This would tell Access to show ALL records (in the child form) for the selected record in the Main form.

I don't use a bound main form when searching. Instead I add textboxes and combos, and then "build up" the recordsource of my subform (or listbox) based on user choices. However, if you could upload your database and let us know how to recreate the issue we'll take a closer look.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

IEHP1Author Commented:
Sure thing......

So this is a very basic sample database but the concepts remain true.

I still don't know how to approach the Record 1 of # issue between the subform and mainform. That is, the main form will show Record 1 of 3 while the subform will show Record 1 of 2 if I input San Bernardino in the City search field (I know there is 3 results and I want them to all show in the subform).

Another issue that is more minor in nature is the whole format\look and feel of when I put the subform in datasheet view in order to get it to display more than just the first record and having to hit the Record 1 of # right arrow to show the next results (that was in continuous form Default View I believe).

I had it before with the same font as the form header search criteria section and I would like to keep that same formatting with the subform if I can find a way to do that (Datasheet View puts it in the format as if seeing the query results directly from the query--not so pretty......especially since the font size increases and I would have to scroll farther to see all of the fields......)

Thank you in advance for helping me out with this LSMConsulting!!
IEHP1Author Commented:
I guess what I'm asking for regarding the subform's formatting is that the column headings be formatted the same as the form header section's labels and the results are formatted the same as the combo and text boxes formatting.

I saw that I can simply go to Home tab and change the font size and font color (getting the same RGB values), but the problem with that is that it changes both the column headings and the results. Is there any way to get them to match as the form header section???
IEHP1Author Commented:
I don't know if I am being too vague about having a subform to show the results or not. I have read a lot of articles about having a subform for a one-to-many relationship in the underlying query, but I don't believe that is the case here (it is a one-to-one relationship).

The subform is to simply show the results of nearly all of the same fields that are in the form header section...... Can I get someone to give me a response to this question please (LSM Consulting gave a response and it seems like noone else wants to give their input, but I really do appreciate everyone's input, really.......)

So to sum it up, this is the issue:

1. The Record 1 of # in the subform is only showing the first person it finds (whether that be 1 or more records) while the main form's Record 1 of # is showing 1 of # (# being more than the subforms number of records)

Please don't worry too much about the Datasheet View issue because if I can get it to work in Continuous Form or another view besides Datasheet View, I can have more flexibility to format the labels and values of the result set.........

Please respond.......Can you please respond LSMConsulting?????
IEHP1Author Commented:
I apologize LSM Consulting (I actually had thought the experts on the forum got paid for their points). You are right about spending time with our families on the weekend so I am sorry if my tone sounded upset or something. Thank you for volunteering to help me and all others that I know you make a world of difference in their learning and understanding of the concepts used.

So I removed the Link Master and Link Child Field properties and I also inserted the Form reference (I guess that's what it would be called??) that you bolded in the VBA onclick event for cmdFilter and then tried filtering "San Bernardino" in the City field and received the following errors:

Form Filter VBA error
vba debug error
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I can filter for "san berandino" in the City ... it sounds as if you don't have the correct value for the Subform name. The syntax should be:


"NameOfYourSubFormControl" is the name of the Subform CONTROL on the main form. It may or may not be named the same as the form you're using as a Subform.

Note that I used your database (the one that you sent to EE) for this, so the names should be the same - but be very sure of punctuation and such ...
IEHP1Author Commented:
This is a quote I took from

"To refer to a subform or a control on a subform, you must remember that Access treats the subform as a control. Essentially, you have a form with a control with a control. To express that arrangement in terms Access can decipher, you need the Form property as follows

In other words, subform is simply a control on the main form"

These are the kinds of things I keep seeing when searching how to correct the syntax (or maybe I am wrong about that?), but even if it were that, I don't think that would fix the error I am getting because isn't the Control we want to reference the cmdFilter_Click??

Just thought I would throw that one out there and see if I am making any sense.....:)  :)?
IEHP1Author Commented:
Oh ok, so I will try it out when I come back from church (just letting you know) and can't thank you enough LSMConsulting for all the hard work!!! You rock!!!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As i said, using your database, with the changes I made, I can search for "san bernadino" and the subform shows me 3 records.

The error you're getting most often is caused by improperly referring to a control.
IEHP1Author Commented:
So please let me understand this correctly:


"NameOfYourSubFormControl" is the name of the Subform CONTROL on the main form. It may or may not be named the same as the form you're using as a Subform.

So the Subform CONTROL on the main form is CmdFilter (the filter command button) and the .Form is suppose to be the name of my subform (Frm_Provider_Lookup_subfrm  - I renamed it to the same name but with underscores)?? Is that right?

Like this:

Me.cmdFilter.Frm_Provider_Lookup_subfrm.Filter = strWhere
        Me.cmdFilter.Frm_Provider_Lookup_subfrm.FilterOn = True       ???

That didn't work so I will try something different??

Sorry, tried it a couple different ways and I am not understanding what you are saying (still getting errors)?

Can you upload the sample database that works for you please?
IEHP1Author Commented:
Well I got it working now LSMConsulting!! Can't thank you enough even though I was pretty bad there bugging you to get an answer.....I got it working with the answer you gave me.

I don't know why, but I had to redownload the one I uploaded to EE and make the changes you said to make and voila.....worked like a charm!!!  

Sorry about not making your magic work sooner.
IEHP1Author Commented:
I have now learned how to reference a subform with VBA and I am happy to have gone through some research to find out about it. LSMConsulting's expert answer was right on!!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.