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.
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.
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.
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.
ASKER
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!!
Form-Building-Using-FiltersRev-3.accdb
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!!
Form-Building-Using-FiltersRev-3.accdb
ASKER
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???
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???
ASKER
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?????
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?????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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:
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:
Me.NameOfYourSubformContro
"NameOfYourSubFormControl"
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 ...
Me.NameOfYourSubformContro
"NameOfYourSubFormControl"
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 ...
ASKER
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
Forms!mainform!subform.For
Me!subform.Form.controlons
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.....:) :)?
"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
Forms!mainform!subform.For
Me!subform.Form.controlons
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.....:) :)?
ASKER
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!!!
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.
The error you're getting most often is caused by improperly referring to a control.
ASKER
So please let me understand this correctly:
Me.NameOfYourSubformContro
"NameOfYourSubFormControl"
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_subfr
Like this:
Me.cmdFilter.Frm_Provider_
Me.cmdFilter.Frm_Provider_
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?
Me.NameOfYourSubformContro
"NameOfYourSubFormControl"
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_subfr
Like this:
Me.cmdFilter.Frm_Provider_
Me.cmdFilter.Frm_Provider_
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?
ASKER
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.
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.
ASKER
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!!!!
ASKER
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??