Limit the contents of a list box based on what's selected in a combo box

[Morpheus_UK]

What I want to do is select an option from a combo box, which changes the record source of a list box also on the same form, to an appropriate query. For instance:

I have a combo box with 3 options to select from
Record Source: "All";"Non Members";"Members"

I have made 3 separate queries for finding certain records:
Qry1 finds All records
Qry2 finds Non Members records
Qry3 finds Members records.

So let’s say you Select “Members” from the combo box, on change it changes the list box record source to Qry3. The Default record source for the list will be Qry1, so I'm guessing I would state the Default for combo would be All to do this. I’ve done a lot of searching but can’t understand how to do this

I searched and found this http://www.mvps.org/access/forms/frm0028.htm but didnt understand the last part. Never mind if it works, and does what I'm after. If you know how help, I be greatful. Cheers

I looked on these boards but couldn't find an exact way to do it, with my know how with Access. So I've commited the act of asking for help

 

[Morpheus_UK]

Ok simple way I found for anyone at the same level as me.

Private Sub ComboName_AfterUpdate()

Select Case ComboName.Value

Case "All"
Me!ListName.RowSourceType = "Table/Query"
Me!ListName.RowSource = "QryAll"

Case "Non Member"
Me!ListName.RowSourceType = "Table/Query"
Me!ListName.RowSource = "QryNonMember"

Case "Student Member"
Me!ListName.RowSourceType = "Table/Query"
Me!ListName.RowSource = "QryStudentMember"

Case "Leader"
Me!ListName.RowSourceType = "Table/Query"
Me!ListName.RowSource = "QryLeader"
End Select

End Sub

 

[pono1]

A sample DB that does this using a textbox can be found right here...

Regards,
Tim

 

[Morpheus_UK]

That right there is v. kool, thanx for sharing Tim.

 

[Mile-O]

Private Sub ComboName_AfterUpdate()

Select Case ComboName.Value

Case "All"
    Me![I]ListName[/I].RowSourceType = "Table/Query"
    Me![I]ListName[/I].RowSource = "QryAll"

Case "Non Member"
    Me![I]ListName[/I].RowSourceType = "Table/Query"
    Me![I]ListName[/I].RowSource = "QryNonMember"
    
Case "Student Member"
    Me![I]ListName[/I].RowSourceType = "Table/Query"
    Me![I]ListName[/I].RowSource = "QryStudentMember"

Case "Leader"
    Me![I]ListName[/I].RowSourceType = "Table/Query"
    Me![I]ListName[/I].RowSource = "QryLeader"
End Select

End Sub

Me.[I]ListName[/I].RowSourceType = "Table/Query"
Select Case ComboName.Value
    Case Is = "All"
        Me.[I]ListName[/I].RowSource = "QryAll"
     Case Is = "Non Member"
        Me.[I]ListName[/I].RowSource = "QryNonMember"  
     Case Is = "Student Member"
        Me.[I]ListName[/I].RowSource = "QryStudentMember"
     Case Is = "Leader"
        Me.[I]ListName[/I].RowSource = "QryLeader"
    Case Else
        Me.[I]ListName[/I].RowSource = vbNullString
End Select

 

[Laia]

Problem making the record source of a combo box depend on another combo box

Hi!

I've tried to follow SJ's proposal to make the record source (a query) of a combo box ("Lloc_rel") depend on the option selected in a previous combo box ("Abast_rel"). I don't get any error message, but the depending combo box show no options.

Here is the event procedure:

Private Sub Abast_rel_AfterUpdate()

Me.Lloc_rel.RowSourceType = "Table/Query"
Select Case Abast_rel.Value
Case Is = "Municipal"
Me.Lloc_rel.RowSource = "ConsultaMunicipi"
Case Is = "Comarcal"
Me.Lloc_rel.RowSource = "ConsultaComarca"
Case Is = "Autonòmic"
Me.Lloc_rel.RowSource = "ConsultaAutonomia"
Case Is = "Estatal"
Me.Lloc_rel.RowSource = "ConsultaPais"
Case Is = "Mundial"
Me.Lloc_rel.RowSource = "ConsultaMon"
Case Is = "Altres"
Me.Lloc_rel.RowSource = "ConsultaAltres"
Case Else
Me.Lloc_rel.RowSource = vbNullString
End Select

End Sub

What am I doing wrong? Must I set the properties of Lloc_rel in a special way?

Thank you very much in advance for any aid.

Laia

 

[CEH]

Take a look at "combos.zip" Only one query .......
http://www.access-programmers.co.uk/forums/showthread.php?t=117112