[AccessD] Question about Listbox Finders on a subform

John Ruff papparuff at comcast.net
Sun May 16 21:04:18 CDT 2004


Arthur,
 
Here's code that I use for setting the RecordSource for a Subform based on
what has been selected in a listbox.
 
Private Sub lstAttributes_Click()
 
    Dim strSQL As String
    Dim varItm As Variant
    Dim strValues As String
    
    ' Loop through the items selected in the listbox
    ' and concatenate the strValues string with
    ' each AttributeID selected in the listbox, followed
    ' by a comma (the delimiter)
    For Each varItm In lstAttributes.ItemsSelected
        strValues = strValues & _
                lstAttributes.Column(0, varItm) & ","
    Next varItm

    ' Insure the user selects at least one value    
    If strValues = "" Then
        MsgBox "You must select at least one item from the listbox"
        lstAttributes.SetFocus
        Exit Sub
    End If
    
    ' Remove the last comma from the txtSelectedItems textbox
    strValues = Left(strValues, Len(strValues) - 1)
    
    ' Build the SQL string using the IN operator and the values in the
    ' txtSelectedItems textbox
    strSQL = "SELECT AttributeID, AttributeName, " & _
                "AttributeInstructions " & _
                "FROM tbl_SpecialAttributes " & _
                "WHERE AttributeID In (" & strValues & ");"
    
    ' Set the subform's RecordSource with the SQL string
    frm_SpecialAttributes_Sub.Form.RecordSource = strSQL
    
End Sub

John V. Ruff - The Eternal Optimist :-) 
Always Looking For Contract Opportunities 

Home: 253.588.2139 
Cell: 253.307/2947 
9306 Farwest Dr SW 
Lakewood, WA 98498 

"Commit to the Lord whatever you do, 
                and your plans will succeed." Proverbs 16:3 





-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, May 16, 2004 5:29 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Question about Listbox Finders on a subform


Yes.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Sunday, May 16, 2004 7:54 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Question about Listbox Finders on a subform


Tabbed form? Do you mean a tab control?

Susan H.

I have a form with a listbox "finder" on it, generated by the wizard. As a
standalone form it works perfectly: I select a row in the listbox and it
navigates to said row. When I drop this form on a tabbed form, it ceases to
work. I can't figure out why. It may be something obvious, but it's been a
while since I've done Access development and if I ever knew the answer I've
forgotten.


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list