Listbox based on query and combos??

[KathCobb]

Hello all. I am not sure if this is a forms question or a queries question. Probably a bit of both. I saw this idea in a book but can't take it from a simple example to my more complex example. And I have been at it for a long while. :banghead:

access 2007 is my version, btw, a very beginner at Access and vba

So I have Agents, I have Clients, and the Agents sell Insurance Policies to clients. The agents would like to be able to get a list of their clients--all clients--(addresses and policies they now have) and/or also get this list and narrow it by County the Client lives in.

So I wanted to have two combo boxes. One selects the agent, the other selects the county and this would change a list box that I have based on a query that shows all the needed info.

As I said, I saw this in a book and looked like a great idea but I am in WAAAY over my head on the query and joins especially with the number of "lookup" tables I have and the junction tables to.

I did manage to create a query that when run by itself, it actually displays all the records and all the required fields. Now I just need to figure out how to get this to change based on combo box selections.

I am not sure what question to ask here or where to start. I think my query may actually be wrong for what I need--but right for a stand alone query. I know i need the row source of the list box to change based on an after update event to the combo boxes, but that is where I am lost. I created my query in query design view and have no idea how or what to change in vba.

Any chance someone might know of a simpler way to get where I am trying to go? Or a good tutorial? Or maybe can ask me some questions that I might be able to give enough information to get some help?

Anything would be greatly appreciated.

 

[Pat Hartman]

The listbox that will show the selected customers needs a RowSource query that references the two combos for its criteria:
Select ...
From ...
Where AgentID = Forms!yourform!cboAgentID AND County = Forms!yourform!cboCounty;

Then in the AfterUpdate event of each combo, requery the listbox.
Me.lstCustomers.Requery

You have to do the requery in both places because you don't know which one the user will change last. An alternative is to use a button to refresh the listbox. That way the user pushes the button when he is ready and so you would only have the requery once, in the click event of the button.

 

[KathCobb]

I actually managed to get one combo box to work using this code:


Private Sub cboAgent_AfterUpdate()
Dim strSQL As String
Dim strWhere As String
strWhere = "WHERE fkAgentID=" & Me.cboAgent & " "
strSQL = "SELECT qryClientListBox.ClientLastName, qryClientListBox.ClientFirstName, qryClientListBox.StreetAddress, qryClientListBox.CityName, qryClientListBox.State, qryClientListBox.Zip, qryClientListBox.CountyName, qryClientListBox.AgentLastName, qryClientListBox.AgentFirstName, qryClientListBox.Company, qryClientListBox.PlanType, qryClientListBox.PlanName " & _
"FROM qryClientListBox " & _
strWhere & _
"ORDER BY qryClientListBox.[ClientLastName], qryClientListBox.[CountyName], qryClientListBox.[CityName] "
Me.lstClientList.RowSource = strSQL
Me.lstClientList.Requery
Me.lstClientList = 0
End Sub
---------------------

But I do not know now what to do with the other combo box to make them work together--or if I just change the where statement will that automatically happen? I'll give it a try!

 

[KathCobb]

That worked!!! Wow!! I followed a different sample code and am quite impressed with myself that I figured out how to apply it to what I needed.

Now that I have these two combo boxes, is there a way to get the second one to reset to "blank" if the firs tone--the Agent is changed?

 

[KathCobb]

It didn't work actually. I wasn't paying attention to enough detail I guess. When I select an agent, the list box refreshes to only that agent's clients. Thats perfect. if I then choose a county for that agent and that agent has clients in that county, it reduces the list to that agent and that county.

If the agent has No clients in that county, it shows all clients for that county regardless of agent. Is there away to change that?

Thanks in advance!

kathy

 

[Pat Hartman]

You'll need to post the queries so we can see what you are doing wrong.