help with using a List Box and Option Group in combination.

[michaelk]

I have a List Box (name ListBox) that updates with information from queries according to which option in an option group (named ListSelect) is chosen. The column properties of the ListBox must change slightly according to the queries.

The problem I am having is that certain queries require data to be entered by the users. When a new option is selected it seems that the query is run again for each property change. Therefore in Case 2 you must enter the Category 3 times before the list is populated.

How can I avoid this. Listing the properties in If Then fashion is the second thing I have tried. Originally I listed the properties under each Case and received the same results. How can I execute the property changes so that the queries are only run once? I have also tried moving the query statement (strRowSource) below the If Then lines. The only difference is that the query is run 3 times when you select it rather than when you select a new option.

Thanks in Advance for any help you can offer.

Private Sub ListSelect_AfterUpdate()

Dim strRowSource As String
Dim strPropertiesType As String

Select Case Me!ListSelect
Case 1
strPropertiesType = "A"
strRowSource = "SELECT [Personal Data].[First Name], [Personal Data].[Last Name], [Personal Data].Organization, [Personal Data].ID FROM [Personal Data] ORDER BY [Personal Data].[Last Name], [Personal Data].[First Name];"
Case 2
strPropertiesType = "A"
strRowSource = "SELECT [Personal Data].[First Name], [Personal Data].[Last Name], [Personal Data].Organization, [Personal Data].ID FROM [Category Types] INNER JOIN ([Personal Data] INNER JOIN [Category Link] ON [Personal Data].ID = [Category Link].[Member ID]) ON ([Category Types].[Category ID] = [Category Link].[Category ID]) AND ([Category Types].[Category ID] = [Category Link].[Category ID]) WHERE ((([Category Types].[Category Description]) = [Enter Category])) ORDER BY [Personal Data].[Last Name], [Personal Data].[First Name];"
Case 3
strPropertiesType = "A"
strRowSource = "SELECT [Personal Data].[First Name], [Personal Data].[Last Name], [Personal Data].Organization, [Personal Data].ID FROM [Personal Data] INNER JOIN (Events INNER JOIN Attendance ON (Events.[Event ID] = Attendance.[Event ID]) AND (Events.[Event ID] = Attendance.[Event ID]) AND (Events.[Event ID] = Attendance.[Event ID])) ON [Personal Data].ID = Attendance.[Member ID] WHERE (((Events.[Event Name]) = [Enter Event])) ORDER BY [Personal Data].[Last Name], [Personal Data].[First Name];"
Case 4
strPropertiesType = "A"
strRowSource = "SELECT [Personal Data].[First Name], [Personal Data].[Last Name], [Personal Data].Organization, [Personal Data].ID FROM [Personal Data] INNER JOIN (Events INNER JOIN Attendance ON (Events.[Event ID] = Attendance.[Event ID]) AND (Events.[Event ID] = Attendance.[Event ID]) AND (Events.[Event ID] = Attendance.[Event ID])) ON [Personal Data].ID = Attendance.[Member ID] WHERE (((Events.[Event Interest]) = [Enter Interest])) ORDER BY [Personal Data].[Last Name], [Personal Data].[First Name];"
Case 5
strPropertiesType = "B"
strRowSource = "SELECT [Personal Data].[Chapter Number], [Office & Chair Link].[Office/Chair], [Personal Data].[First Name], [Personal Data].[Last Name], [Personal Data].ID FROM [Office and Chair Lookup] INNER JOIN ([Personal Data] INNER JOIN [Office & Chair Link] ON [Personal Data].ID = [Office & Chair Link].ID) ON [Office and Chair Lookup].[Office/Chair] = [Office & Chair Link].[Office/Chair] WHERE ((([Personal Data].[Chapter Officer/Chair]) = Yes) And (([Office & Chair Link].Term) = [Enter Year])) ORDER BY [Personal Data].[Last Name], [Personal Data].[First Name];"
Case 6
strPropertiesType = "C"
strRowSource = "SELECT [All Representatives].Salutation AS Office, [All Representatives].[First Name], [All Representatives].[Last Name], [All Representatives].ID FROM [All Representatives] ORDER BY [All Representatives].[Last Name], [All Representatives].[First Name];"
End Select

Me!ListBox.RowSource = strRowSource

If strPropertiesType = "A" Then
Me!ListBox.ColumnCount = 4
Me!ListBox.BoundColumn = 4
Me!ListBox.ColumnWidths = "1080;1080;2880;0"
Else
If strPropertiesType = "B" Then
Me!ListBox.ColumnCount = 5
Me!ListBox.BoundColumn = 5
Me!ListBox.ColumnWidths = "1080;1080;1080;2880;0"
Else
If strPropertiesType = "C" Then
Me!ListBox.ColumnCount = 4
Me!ListBox.BoundColumn = 4
Me!ListBox.ColumnWidths = "1080;1080;1080;0"
End If
End If
End If

End Sub