listbox row source to multiple queries

[JPR]

Hello.
Sorry if posting this thread in the wrong section.
I have a form on which I have a combo (cbostudy) and a listbox (lstStudy).

I would like the list box to dynamically change the record source to different queries, depending on the selection in the combo.

I have already created two queries and in each one I have added the criteria that will return the selected records.
I have found some code and changed it but the list box does not requery and therefore I don't get any result. This is the code in the AfterUpdate event of my combobox:

If Me![cboStudy] = "Rep Payee" Then
Forms![frmMenu]!lstStudy.RowSourceType = "Query/table"
Me![lstStudy].RowSource = "qryRepPayee"
Me!lstStudy.Requery
Else
If Me![cboStudy] = "US Citizens" Then
Forms![frmMenu]!lstStudy.RowSourceType = "Query/table"
Me![lstStudy].RowSource = "qryuscit"
Me!lstStudy.Requery
End If
End If
End Sub

The combo has one column and is bound to 1.
Queries have already the criteria (ex. in query rep payee is "1")

Thank you for any help.

 

[Minty]

Is this code all being run from frmMenu?

Also this line in not needed - Forms![frmMenu]!lstStudy.RowSourceType = "Query/table"
Once set in design mode this is fixed for ever unless you have a reason to change it. Try this;

    If Me.cboStudy = "Rep Payee" Then
        
        Me.lstStudy.RowSource = "qryRepPayee"
      
    ElseIf Me.cboStudy = "US Citizens" Then
           
        Me.lstStudy.RowSource = "qryuscit"
           
    End If
    Me.lstStudy.Requery
    Refresh

If there are just the two options you can simplify it further.

 

[Gasman]

How can the combo only have one column which would contain "Rep Payee" or "US Citizens" and then have a value of 1 in the query.?

 

[JPR]

Thank you all for your help. It works great.
I will not add more queries. About the "1", that's just the criteria I need to sort the records in my query. Thanks

 

[Gasman]

So it was just the Refresh that was needed?

 

[JPR]

Yes. Thank you

 

[Minty]

I hope you changed your Me! to Me. as well and removed all the [ ] , it tends to indicate a field property rather than a control.