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.

Any thoughts?

Thanks,
Piper

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.

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.

HTH
Matt

Okay,

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 "
Next
If Len(txtTemp) > 0 Then
txtTemp = Left(txtTemp, Len(txtTemp) - 4)
End If

Me.Capture_Year_Criteria = txtTemp
End Sub

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

Exit_Nest_Failure_Results_Click:
Exit Sub

Err_Nest_Failure_Results_Click:
MsgBox Err.Description
Resume Exit_Nest_Failure_Results_Click

End Sub

However, the "frm_Yr_INFO_nests_failed" is actually comprised of two subforms:

"fsub_Yr_Num_Nests_Failed_Incomplete_by_Cause"

and

"fsub_Yr_Num_Nests_Failed_Complete_by_Cause"

But neither of these subforms is updated when the "frm_yr_INFO_nests_failed" opens up.

Any suggestions?

Thans again!
Piper

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.

HTH
Matt

Thanks Matt,

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!
Piper

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.

Great! Thanks so much!

np

Matt,

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...)
Piper

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

HTH
Matt