Sep 20 2007, 08:34 AM
Why won't my subforms be updated with my choice(s) from my multiselect listbox?
I have two queries that I have made into subforms. The code I got from here opens the main form that the subforms are on, but then all my census years data appears, instead of the one or two years that I selected in my multiselect listbox.
Sep 20 2007, 08:37 AM
When the subform opens, no values have been selected in the list box have they? If that is so, then of course the subform will not know which years you want to see, so it will display all of them. One way to handle this is to have an empty subform on the main form, then set its form's Record Source property to a query that uses the list box selections as its criteria values, then requery the subform.
Sep 20 2007, 08:38 AM
Just a wild guess, but I bet your code isn't right
From the information you have posted, there is really no way to tell for sure what's wrong. Please post the SQL of the two queries and the code you are using.
Sep 20 2007, 09:02 AM
I have a main form with the multiselect listbox and several command buttons. The user chooses a year or several years that they want the information displayed. On the main form there are also several command buttons.
This is the code for the listbox to store the year(s) that I want to see:
Option Compare Database
Private Sub Selected_Years_AfterUpdate()
Dim varItem As Variant
Dim txtTemp As String
Dim txtCriteria As String
txtCriteria = "[Year] = "
For Each varItem In Me.Selected_Years.ItemsSelected
txtTemp = txtTemp & txtCriteria & "'" & Me.Selected_Years.ItemData(varItem) & "'" & " Or "
If Len(txtTemp) > 0 Then
txtTemp = Left(txtTemp, Len(txtTemp) - 4)
Me.Capture_Year_Criteria = txtTemp
This is the code that runs when I click the command button tilted "Nest Failure Results"
Private Sub Nest_Failure_Results_Click()
On Error GoTo Err_Nest_Failure_Results_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm_Yr_INFO_nests_failed"
stLinkCriteria = Me![Capture_Year_Criteria]
DoCmd.OpenForm stDocName, , , stLinkCriteria
However, the "frm_Yr_INFO_nests_failed" is actually comprised of two subforms:
But neither of these subforms is updated when the "frm_yr_INFO_nests_failed" opens up.
Sep 20 2007, 09:10 AM
You are applying the criteria to "frm_Yr_INFO_nests_failed", while the data you want filtered is on the sub-forms. How are the two sub-forms related to "frm_Yr_INFO_nests_failed"?
Also, "Year" is a reserved word in Access - I suggest you change it to something else.
Sep 20 2007, 09:20 AM
I forgot about the "Year" being a reserved word - I shouldn't have - I had a problem with this before.
Anyway, I think you hit on the problem:
"How are the two sub-forms related to "frm_Yr_INFO_nests_failed"?"
Actually, they're not. The "frm_Yr_INFO_nests_failed" is a blank form that holds the two subforms. It's a background with no data of its own. It's more of a placemat to put the two subforms that are somewhat related on the same screen when they are opened. I couldn't figure out a way to have the results from the queries be tied together into a new query (which would have been so much easier because I could have just based a form on that instead).
So I think that's where my problem is. But how do I fix it? Any ideas?
Thanks so much!
Sep 20 2007, 09:23 AM
Instead of concatenating a SQL Where clause, use the list box to build a filter string, and then apply the filter to the sub-forms.
Sep 20 2007, 09:28 AM
Great! Thanks so much!
Sep 20 2007, 09:40 AM
Sep 20 2007, 09:58 AM
I hope you are still there :-)
To be honest, (and I've said this a million times), I know just enough to get myself into trouble.
I've tried searching on the code archives, and the MS access support site, but I still haven't figured out to:
"...build a filter string, and then apply the filter to the sub-forms."
What you said makes perfect sense to me, I just don't know where to get started.
If you can, please enlighten me!
(waiting patiently for illumination...)
Sep 20 2007, 10:03 AM
After looking at it a second time, you should be able to use the same string you are already building:
To set the filter, you would use:
Forms!MainForm!Subform1.Form.Filter = Me.Capture_Year_Criteria
Forms!MainForm!Subform1.Form.FilterOn = True
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here