List box and subform (1 Viewer)

[123James]

Hi,

I have an unbound form with a list box on it and a subform

How can I filter the data displayed in the subform by the value selected from the list box??

If there are no records in the subform with a matching value from the list, is it possible to have the list value selected appear in the matching field in the subform??

Thanks

 

[doco]

You need to base the subform on a query. The query would then receive criteria from the list box. EG in the criteria section of a field in your query put =[Forms]![myForm].[myListBox]. Then me.requery in the listbox.change event

HTH

 

[123James]

Nice one!

Thanks for the help doco

 

[boblarson]

Just an FYI - If you have an unbound main form and only a single subform, then you wouldn't need to use a subform. Just put the unbound controls on the subform.

 

[feathers212]

Sorry to steal this thread, but this is exactly what I have been looking to do.

I have a Main Form with a list box called CategoryList and a subform called UsageSummarysubform. The list box is setup for simple Multi Select, and its values are based on a query called Categories. The subform is based on a query called UsageSummaryQuery. UsageSummaryQuery contains a field called Category.

After reading this post, I set the Category criteria of the UsageSummaryQuery = [Forms]![Main Form]![CategoryList].

I also put the following Event Procedure code together

Private Sub CategoryList_AfterUpdate()

Me.UsageSummarysubform.Requery

End Sub

Proble


Looked great, but then when I tried it out, it doesn't work. Even after selecting a single category from the list box, nothing shows up on the UsageSummarysubform.

Any thoughts/suggestions? Please help!!

Oh, and I am using Access 97.

~Heather

 

[boblarson]

When a listbox is set to multiselect you can't reference the list box the same as when you have it as single. And, you can't really use it the same way in a query. You would need to use code to iterate through the selected items and build your query's WHERE clause dynamically.

 

[feathers212]

I see that it works no problem with a single select. That is what I figured was the problem. Before I found this post, I did build a for next loop to capture the criteria:

Private Sub CategoryList_AfterUpdate()

    Dim ctlSource As Control
    Dim strCriteria As String
    Dim intCurrentRow As Integer
    Set ctlSource = Me!CategoryList
    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            If strCriteria = "" Then
                strCriteria = ctlSource.Column(0, intCurrentRow)
            Else
                strCriteria = strCriteria & " Or " & ctlSource.Column(0, intCurrentRow)
            End If
        Else: End If
    Next intCurrentRow
    
    
End Sub

However, I got stuck with how to build this into the query. I haven't had any luck in figuring out how to get the strCriteria string variable into the query.

 

[doco]

I think it easier to build the query and then refer to the controls from the criteria section of each field of interest. However, there are a couple different ways to build the query on-the-fly.

***   UNTESED   ***

Dim SQL as String

    SQL = "SELECT * FROM MyTable " & _
             "WHERE(((firstField)='" & MyListSelection & "') AND " & _
             "(secondField)='" & MyNextSelection & "'));"

Continue for each criteria...
Either put the string in a RowSource attribute and requery or a QueryDef, ???.

This may be a bit rough but do not visualize what you want to do or what your schema is. But hope this gives you some ideas...

 

[feathers212]

I don't want to build the query on the fly. The query is already built. I just want to update the criteria for one field of the query.

I need to keep this as simple as possible. I have additional list boxes and subforms on the Main Form that I want to end up doing the same thing with.

 

[doco]

Well, then. It is best to make reference to your form controls from the criteria section of your query then requery the form with each control event (_Change, _Click or ???)

EG:

    LIKE [Forms]![myForm].[myListBox] & "*"

 

[doco]

There is a couple good sample databases on this forum that demos what you're trying to do. I think this is one http://www.access-programmers.co.uk/forums/showthread.php?t=121973

At any rate, go to 'Sample Databases' section of this forum and peek at a couple - reviewng others' work is a really good way to learn.