use a series of listboxes as filter (Access97)

Topic

New Reply

on an unbound form I have three listboxes. I used the wizard to create them. Their names and row sources are as follows:

cboCampType SELECT DISTINCTROW [tblCampType].[CampTypeID], [tblCampType].[CampType], [tblCampType].[CampDescription] FROM [tblCampType];

cboLName SELECT DISTINCTROW [tblLegislators].[LegID], [tblLegislators].[LName], [tblLegislators].[FName] FROM [tblLegislators] ORDER BY [LName];

cboCampName SELECT DISTINCTROW [tblCampaign].[CampName] FROM [tblCampaign] ORDER BY [CampName];

tblCampType is linked to tblCampaign as a look up table. tblLegislators is joined to tblCampaign via a junction table called jctblLegCamp. there is a one to many relationship between Legislators and Campaigns.

How do you tell these listboxes that after one of them is updated, to filter the other two? The users will not choose in any particular order, just personal preference so there needs to be three choices from the onset. I think the answer lies in dynamically setting the rowsource of the remaining boxes after a selection is made . This is proving difficult because the code required is beyond my scope. Also i don’t believe that these boxes understand that they are related to each other. Apparently, they can’t look at the relationship window and see the line connecting them so I have to figure out a way to tell them.

If anyone has suggestions on where to get some clarity on the situation, I’d be appreciative. I know i’ve seen a post about this, but can’t find that either. Thanks.

Reply | Quote

Replies

Some questions.
I presume that CampType is a field in tblCampaign? If not how do you relate tblCampType to tblCampaign?

Do you mean that the user can select any of the combo boxes in the order they choose? If yes, then you will have to set the RowSource of each combo box dynamically.

What is the content of jctblLegCamp? Is it LegID and CampName?

Pat

Reply | Quote

1. Why would you want cboCampType to be filtered when the user selects an item in one of the others?

2. Suppose the user selects a campaign in cboCampName. You want cboLName to be filtered, so that it only displays legislators involved in the selected campaign. Next, the user selects a legislator. How do you want to filter cboCampName now? To show *all* campaigns for this legislator?

(3. Minor quibble: why do you use cbo in the names of your list boxes? The cbo prefix usually stands for combo box.)

Anyway, as patt indicated, you will probably have to change the Row Source property of the list boxes in the After Update event of each. You will need to include the junction table in the Row Source in order to select the appropriate legislators/campaigns.

Reply | Quote

LOL, they started out as combo boxes Hans!

OK, in response to both of you: thanks first of all.

yes, cboCampType is a field in tblCampaign. jctblLegCamp consists of an automunber, LegID and CampID (not CampName).

If a user selects an item from CampType, then LName needs to be filtered along with the Camp Name box to show campaigns for that Legislator. There are some instances where a user might want to select the campaign name directly, and in that case it isn’t necessary for the other two to be requeried. Whichever way it is accomplished, I would like to end up in a position to use the campID the user selected in the next form that will open.

I do need to be able to clear the form too so the defaults return… each box now has a list of all their options, no filters applied. I do not want to add an all option to each of the boxes, rather a button that will remove any filters applied or reset the row sources to the original condition as stated in the earlier post.

Anyhow, If you know of how to set the row sources dynamically or where to find info about that specifically, I’d like the direction.

(As an aside, what i’m trying to accomplish is akin to selecting a customer and then placing an order for that customer. In my case a “customer” is a “campaign name” but users rarely know the proper name for a campaign, they may know the name of the legislator or the office for which they are running. In this version of the application, I want to give them several options for choosing a campaign and it is a better means for helping them choose the correct campaign.)

Again thanks for your posts! and any information you may still have to offer.

Reply | Quote

In the OnClick procedure of the “Reset” command button, you can set the row source of the list boxes to the tables instead of a query:

Private Sub cmdReset_Click()
cboCampName.RowSource = “tblCampaign”
cboLName.RowSource = “tblLegislators”
End Sub

You can also set them to the SQL strings you have in design view.

Reply | Quote

(Edited by HansV on 27-Nov-02 16:53. added DISTINCT to second SQL string to avoid duplicates.)

Try something like the following in the AfterUpdate event of cboCampType:

Private Sub cboCampType_AfterUpdate()
cboCampName.RowSource = “SELECT * FROM tblCampaign WHERE CampType = ” & cboCampType
cboLName.RowSource = “SELECT DISTINCT tblLegislators.* FROM tblLegislators ” & _
“INNER JOIN (tblCampaign INNER JOIN jctblLegCamp ON tblCampaign.CampID = jctblLegCamp.CampID) ” & _
“ON tblLegislators.LegID = jctblLegCamp.LegID WHERE tblCampaign.CampType= ” & cboCampType
End Sub

(I made these SQL strings by designing queries that did the same, switching to SQL view, and copying the SQL statement.)

Reply | Quote

I’ve gotten this statement to work so far. how do you add the ORDER BY part? I’ve tried and access thinks it’s part of the parameter of cboCampType…

cboCampName.RowSource = “SELECT DISTINCTROW [tblCampaign].[CampName] FROM [tblCampaign] WHERE [tblCampaign].[CampTypeID] = ” & cboCampType

Reply | Quote

Try

cboCampName.RowSource = “SELECT DISTINCTROW [tblCampaign].[CampName] FROM [tblCampaign] ” & _
“WHERE [tblCampaign].[CampTypeID] = ” & cboCampType & ” ORDER BY [tblCampaign].[CampName]”

This way, the current value of cboCampType is used, because it’s outside the quotes. The ampersands (&) are used to concatenate the three parts into one string. Note that the last part starts with a space within the quotes; this is to separate ORDER BY from the value of cboCampType.

Reply | Quote

Ahhh! it was the missing space after the open quote….

I’ve gotten both boxes to requery properly after a change to the CampType box. Thanks for your help Hans… I will work on the other ones. I’ll post back with any questions as they arise. But for today, my work is done… til after Turkey Day…

Reply | Quote

Campaign Type works great! However, when I tried to adapt the code to work on the LName listbox, I am clearly generating an error, but don’t know why. Can anyone look at the sql statement below and see the error?

Private Sub cboLName_AfterUpdate()
cboCampName.RowSource = “SELECT DISTINCTROW [tblCampaign].[CampName] FROM [tblCampaign] (INNER JOIN jctblLegCamp ON tblLegislators.LegID = jctblLegCamp.LegID) INNER JOIN tblCampaign ON jctblLegCamp.CampID = tblCampaign.CampID” & _
“WHERE jctblLegCamp.CampID = ” & cboCampName & ” ORDER BY [tblCampaign].[CampName]”

End Sub

Note*** there can be more than one campaign name per selected last name….

Thanks a bunch

Reply | Quote

Do you need a space before the “WHERE ?
You have got tblCampaign defined twice in this query.
Are your joins correct?
As Hans has suggested, I would build this query as a query first then go into SQL view and copy that to the code.
Pat

Reply | Quote

As Pat pointed out, the SQL statement is not valid. The best way to create it is to create a parameter query in query design view, then switch to SQL view, delete the returns (line feeds) from the SQL statement, then copy the SQL statement to the clipboard and paste it into your code. You will have to do a bit of cleanup, but still, this is much easier than trying to design the SQL statement from scratch.

I guess you need something like this:

cboCampName.RowSource = “SELECT tblCampaign.CampName FROM tblCampaign” & _
” INNER JOIN jctblLegCamp ON tblCampaign.CampID = jctblLegCamp.CampID” & _
” WHERE jctblLegCamp.LegID=” & [cboLName] & _
” ORDER BY tblCampaign.CampName”

Note that there are spaces immediately after the opening quotes in the continuation lines.

Reply | Quote

Right again you two. Thanks…

Private Sub cboLName_AfterUpdate()
cboCampName.RowSource = “SELECT tblCampaign.CampName FROM tblLegislators” & _
” INNER JOIN (jctblLegCamp INNER JOIN tblCampaign ON jctblLegCamp.CampID =” & _
” tblCampaign.CampID) ON tblLegislators.LegID = jctblLegCamp.LegID” & _
” WHERE jctblLegCamp.LegID=” & [cboLName] & ” ORDER BY tblCampaign.CampName”
End Sub

This is the correct one…. SQL view is very helpful for people like myself who aren’t well versed in writing SQL statements.

Darn all those joins! Thanks again.

Reply | Quote

Jenn,

Both the design view and SQL view of a query are very useful – period. Even for SQL statements that can *only* be viewed in SQL view (union queries for instance, or queries with non-standard joins), it is advisable to start in design view an build as much of the query as is possible there, and only then switch to SQL view to do the final tinkering.

Reply | Quote