Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    use a series of listboxes as filter (Access97)

    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.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: use a series of listboxes as filter (Access97)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use a series of listboxes as filter (Access97)

    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.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use a series of listboxes as filter (Access97)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use a series of listboxes as filter (Access97)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use a series of listboxes as filter (Access97)

    <P ID="edit" class=small>(Edited by HansV on 27-Nov-02 16:53. added DISTINCT to second SQL string to avoid duplicates.)</P>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.)

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use a series of listboxes as filter (Access97)

    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

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use a series of listboxes as filter (Access97)

    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...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use a series of listboxes as filter (Access97)

    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.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use a series of listboxes as filter (Access97)

    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

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: use a series of listboxes as filter (Access97)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use a series of listboxes as filter (Access97)

    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.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use a series of listboxes as filter (Access97)

    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.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use a series of listboxes as filter (Access97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •