G
Guest
I have three levels of data: Client, Plan, and Project. Client to plan is a
one-to-many relationship, Plan and Project are a many-to-many.
Users can search at any level using unbound text fields, and the results
populate a listbox. Ideally, clients should then be able to click a result
in the listbox to populate the other two listboxes. This works fine going
from client to plan, but when I click on a listbox with Plan or Project
results, I have to populate two other listboxes. The criteria for the other
two listboxes are in two different columns.
What I've tried doing is setting the bound column and requerying one
listbox, then changing the bound column and requerying the other one.
However, whichever one I do first gets cleared out by the second requery (the
second listbox fills perfectly). If I take out the second requery, the first
one works fine. I've tried adding a loop between the bound column change and
the requery (to give the computer a moment to process the bound column
change), but this had no effect. What am I missing?
Below are the lines of code I'm using. In listbox [lstPlan], [ClientID] is
column 0, and [PlanID] is column 1. I appreciate any help anyone can offer.
sSelect = "SELECT [ClientID], [ClientName], [ClientSegment],
[ClientParticipants] "
sFrom = "FROM tblClientInfo "
sOrder = "ORDER BY [ClientName]"
lstPlan.BoundColumn = "0"
sWhere = "WHERE [ClientID]=""" & [lstPlan] & """"
lstClient.RowSource = sSelect & sFrom & sWhere & sOrder
DoCmd.Requery "lstClient"
tSelect = "SELECT [ClientID], [PlanID], [ProjectID], [ProjectType],
[ProjectStatus], [DateValuation], [ProjectParticipants], [ProjectAssets] "
tFrom = "FROM qryPlanAndProject "
tOrder = "ORDER BY [ProjectID]"
lstPlan.BoundColumn = "1"
tWhere = "WHERE [PlanID]=""" & [lstPlan] & """"
lstProject.RowSource = tSelect & tFrom & tWhere & tOrder
DoCmd.Requery "lstProject"
one-to-many relationship, Plan and Project are a many-to-many.
Users can search at any level using unbound text fields, and the results
populate a listbox. Ideally, clients should then be able to click a result
in the listbox to populate the other two listboxes. This works fine going
from client to plan, but when I click on a listbox with Plan or Project
results, I have to populate two other listboxes. The criteria for the other
two listboxes are in two different columns.
What I've tried doing is setting the bound column and requerying one
listbox, then changing the bound column and requerying the other one.
However, whichever one I do first gets cleared out by the second requery (the
second listbox fills perfectly). If I take out the second requery, the first
one works fine. I've tried adding a loop between the bound column change and
the requery (to give the computer a moment to process the bound column
change), but this had no effect. What am I missing?
Below are the lines of code I'm using. In listbox [lstPlan], [ClientID] is
column 0, and [PlanID] is column 1. I appreciate any help anyone can offer.
sSelect = "SELECT [ClientID], [ClientName], [ClientSegment],
[ClientParticipants] "
sFrom = "FROM tblClientInfo "
sOrder = "ORDER BY [ClientName]"
lstPlan.BoundColumn = "0"
sWhere = "WHERE [ClientID]=""" & [lstPlan] & """"
lstClient.RowSource = sSelect & sFrom & sWhere & sOrder
DoCmd.Requery "lstClient"
tSelect = "SELECT [ClientID], [PlanID], [ProjectID], [ProjectType],
[ProjectStatus], [DateValuation], [ProjectParticipants], [ProjectAssets] "
tFrom = "FROM qryPlanAndProject "
tOrder = "ORDER BY [ProjectID]"
lstPlan.BoundColumn = "1"
tWhere = "WHERE [PlanID]=""" & [lstPlan] & """"
lstProject.RowSource = tSelect & tFrom & tWhere & tOrder
DoCmd.Requery "lstProject"