[AccessD] Changing Combo Box Data Source

Karen,
I have an A97 app where I had to allow for (eventually) 9 different filters
in a list box. It started out with less choices and they kept growing so
building it this way made it very flexible.

I needed to keep the app small so I did it using SQL statements (This was
back when floppies were still the removable media of choice). The UI is a
frame with 9 buttons. The default recordset and each button calls a
subroutine that sets the recordset using a case statement. I've condensed it
below:

Private Sub ChooseList()

Dim strListSelectSQL As String
Dim strListWhereSQL As String
Dim strListOrderSQL As String
Dim strListFullSQL As String

DoCmd.Hourglass True
strListSelectSQL = "SELECT [sql select statement] "

Select Case fraListChoices
	Case 1:
		strListWhereSQL = "WHERE [sql order statement] "
		strListOrderSQL = " ORDER BY [sql order statement];"
	Case 2:
		strListWhereSQL = " "WHERE [sql order statement] "
		strListOrderSQL = " ORDER BY [sql order statement];"
	Case Else:
		strListWhereSQL = " "WHERE [sql order statement] "
		strListOrderSQL = " ORDER BY [sql order statement];"

End Select

strListFullSQL = strListSelectSQL & strListWhereSQL & strListOrderSQL
Me.lstList.RowSource = strListFullSQL   
Call SetListMess 'if no record tell user
End Sub

You could do the same thing with queries set with different filters if the
resulting bloat didn't matter.

HTH
John B.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nicholson, Karen
Sent: Friday, July 15, 2005 12:32 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Changing Combo Box Data Source

I know there is a way to do this.  Where are the Friday Jokes?  My form
opens.  The combo box on the form is filtering for only "Open" items.
However, PIA user wants the option to see all projects, open and closed.
What code do I throw on a button to change the record source of the combo
box to take off the filter?  Is it setting the recordset?  Thanks.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com