use form for selection

[AccessWater]

I have a combo box (cmb1)to allow user to choose rollUp groups, after that, all areas within that group will be shown in a ListBox (Ls2). After user click the area(s), I will run a query to find out all projects in the selection. Then I want to show these projects in another listbox (LS3) for next step.

I have two questions: 1. after I choose area, the select query always pop out to show the results, which I do not want to see. 2. The LS3 does not get updated after LS2 selection. I actually have to run query again to get it updated and shown in LS3.

I attached a pic. to show what I want to do. Any suggestion is appreciated.

 

[RuralGuy]

I would think you would use the area selection to set the RowSource of LS3. Why are you running a query?

 

[RuralGuy]

RuralGuy, Thanks for your quick reply.
LS2 allows mutliple choices. I only know to use a query to trigger the mutiple choices. If there is only one choice, know how to do it. Could you pelase teach me some tricks about mutliple choices? Thank you Very much .

Can you post the SQL for your query(s)? What events are you using in the ListBoxes?

 

[AccessWater]

Hi, RuralGuy,

Thank you for your reminder. Yes, you are absolutely right. I can use the following code in the click event of ls2 and got the projects shown in the ls3. lssource.RowSource=strSQL.

 

[RuralGuy]

Outstanding! Glad you got it sorted.

 

[AccessWater]

Hi, RuralGuy, I am having new problem now. Because there is no temp table to store projects shown in LS3, after I move projects to LS4 (a paired list box of LS3), I kind of delete proejct from the original table which is not what I want. It messes up the original data set. Could you please give me some ideas to conduct transfers between two list boxes without out change the original data set? (as shown in my picture) Thank you.

 

[RuralGuy]

I don't have any good suggestions for what you need. If LS4 were based on a table then using NOT IN (the table for LS4) in the SQL of LS3 would stop the item from showing up in LS3 without deleting it simply by putting the item in the table for LS4.

 

[AccessWater]

RuralGuy, Thank you again for this idea.

I now created a new variable as YES/NO in the original table (tb1). LS4 also use this table. Now it won't delete or add anything to tb1. The ideal situation is when a project is moved to LS4, it should be cleared out from LS3. I used the following code to control the showup of projects in LS3 and LS4. It works fine for LS4 (add and disappear from LS4), but the project is not movd from LS3.

I figured the reason is Me. lbsource.RowSource=theSQL & " NOT InSelectedList" when move to LS4 conflits with lbSource.RowSource = strSQL when I try to show list in LS3 from LS2 (in my previous post). Any idea how to get this work? Thank you very much.


Private Sub Form_Open(Cancel As Integer)
Dim theSQL As String
theSQL = "SELECT projectName, InSelectedList FROM tbFinancial_grouping WHERE "

Me.lbSource.RowSource = theSQL & "NOT InSelectedList"
Me.lbDestination.RowSource = theSQL & "InSelectedList"

End Sub

 

[RuralGuy]

Why not have the RowSource of of LS3 and LS4 the same except have a WHERE clause that is opposite for the Yes/No field. Then all you need do is flip that field in the table and that record moves to the other ListBox?

 

[AccessWater]

RuralGuy, it works now. It may not in the exact way as you suggested, but your suggestion did help me thinking about the way to set up RowSources for LS3 and LS4. Thanks a lot!

 

[RuralGuy]

Glad you got it sorted.

 

[AccessWater]

Hi, RuralGuy, Need your help again. Currently, the way I set up the row source for LS3 and LS4. It works fine after teh first selection for RollUp and Area. I can move projects between LS3 and LS4 back and forth. However, if I make anotehr selection (either Roll up or even Area), it give me an error message: "Invalid Operation." I think the way I set up the row source is still not good. Could you please give me soem thougths again? Thank you.

Here is my code for seeting LS3 row source:


Private Sub lsArea_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria2 As String
Dim strSQL As String
Dim theSQL As String

Set db = CurrentDb()
DoCmd.SetWarnings False

strCriteria2 = "'" & Me!cmbRollUp & "'"

'set up SQL for selection and row source for lbSource
For Each varItem In Me!lsArea.ItemsSelected

strCriteria = strCriteria & ",'" & Me!lsArea.ItemData(varItem) & "'"

Next varItem

strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM tbFinancial_grouping " & _
"WHERE tbFinancial_grouping.Area IN(" & strCriteria & ") AND tbFinancial_grouping.Rollup IN(" & strCriteria2 & ") AND NOT tbFinancial_grouping.InSelectedList;"

'Debug.Print strSQL
lbSource.RowSource = strSQL
Me.lbSource.Requery

'Set up SQL for row source for lbDestination
theSQL = "SELECT projectName, InSelectedList FROM tbFinancial_grouping WHERE "
Me.lbDestination.RowSource = theSQL & "InSelectedList"
Me.lbDestination.Requery


End Sub

 

[RuralGuy]

Any chance you can post your db so I can play with it? Remove any sensitive data but leave enough sample data to demonstrate the issue.

 

[AccessWater]

Hi, RG, Could you please take look at my code and help me with it? Thank you very much.

 

[AccessWater]

Could any body help me with this issue? The deadline is coming . What did I do wrong ? Why cannot I make the second time selection to move projects in LS3 to LS4.

I really appreciate your help.

 

[RuralGuy]

I haven't forgotten about you, I just don't have an answer yet.

 

[RuralGuy]

AccessWater,
Start a new thread and post the db and problem. Maybe someone will come up with a solution for you. I'm still drawing a blank, sorry.