Go Premium for a chance to win a PS4. Enter to Win


Form Filter Subform Not Listing Result Set Correctly

Posted on 2013-01-25
Medium Priority
Last Modified: 2013-01-27
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.
Question by:IEHP1
  • 10
  • 4

Author Comment

ID: 38821595
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??
LVL 85
ID: 38821897
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.

Author Comment

ID: 38822494
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!!
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 38822530
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???

Author Comment

ID: 38823367
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?????
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38823757
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.

Author Comment

ID: 38824376
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
LVL 85
ID: 38824502
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 ...

Author Comment

ID: 38824524
This is a quote I took from http://www.techrepublic.com/blog/msoffice/how-to-reference-an-access-subform/617

"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.....:)  :)?

Author Comment

ID: 38824529
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!!!
LVL 85
ID: 38825122
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.

Author Comment

ID: 38825243
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?

Author Comment

ID: 38825571
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.

Author Closing Comment

ID: 38825573
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!!!!

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question