how to convert rowsource type of listbox from table/query to valuelist 1

[yummy7]

Actually i have two listboxes and four cmdbuttons to select and deslect values from List1 to List 2.I can do this if listbox have valuelists but now i have values in listbox through query.And i want it to select and deselect through command buttons to Listbox2.
I know it is very common feature in forms but i m begginner.
Thanx in Advance.

 

[kjv1611]

Can you expand a little on what you currently have, and what you want it to do? Maybe provide examples?

 

[jebry]

Hi Yummy7!

The easiest thing to do is to add a field to your table. I would be a yes/no field and it will indicate if the record should be in list one. Default the value to yes so that all of the records start in list 1. The queries will be:

For list1

Select Yourfields From YourTable Where fldList1 = True

For list2

Select Yourfields From YourTable Where fldList1 = False

Then in the button click to move a record to list two you put

Dim varRow As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

For Each varRow in Me.List1.SelectedItems
rst.FindFirst "YourID = " & Me.List1.Columns(0, varRow)
rst.fldList1 = False
Next varRow

Me.List1.Requery
Me.List2.Requery

Set rst = Nothing

Of course to move them back you just do the same thing except you use List.SelectedItems and you will set fldList1 = True. You will still requery both list boxes.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[yummy7]

Thanx u both to help me.Jebry i think this is not the case,u told me.i explain it to u clearly.
I have a combobox,two list boxes and four cmdbuttons.
there r queries labels in combobox when i click over on any query.It retrieves result in one colum in ListBox1.e.g.I click on "all stores in ON" value of combo so it retrieves all store numbers in ListBox1.
Now I want to select specific stores in ListBox2.How I can do this as ListBox1&ListBox2 have Row Source Type "Table/Query".
Thanx in advance.

 

[MajP]

Not sure what is the difference is with a value list versus a query. The process should be the same. Here is an example of two list boxes. The first is populated with a query. Then I can move things back and forth between the two using my cmd buttons.

Private Sub cmdSelect_Click()
 On Error GoTo err_list
 Dim intCounter As Integer
 If lstUnselected.ListIndex = -1 Then
       MsgBox "Must select a sort field"
       GoTo Exit_Sub
 End If
 For intCounter = 0 To lstSelected.ListCount - 1
     If lstUnselected.Value = lstSelected.Column(1, intCounter) Then
       MsgBox "Field Already Selected"
       GoTo Exit_Sub
     End If
   Next intCounter
   lstSelected.additem ("")
   intListCount = lstSelected.ListCount
   lstSelected.Column(1, intListCount - 1) = lstUnselected.Value
   lstSelected.Column(0, intListCount - 1) = lstUnselected.Column(0, lstUnselected.ListIndex)
Exit_Sub:
  Exit Sub
err_list:
   MsgBox Err.Description
   Resume Exit_Sub
 Exit Sub
End Sub
Private Sub cmdUnselect_Click()
On Error GoTo err_list
  If lstSelected.ListIndex = -1 Then
     MsgBox "Select a field to remove."
     Exit Sub
  End If
  lstSelected.RemoveItem (lstSelected.ListIndex)
Exit_Sub:
  Exit Sub
err_list:
   MsgBox Err.Description
   Resume Exit_Sub
 Exit Sub
End Sub

 

[MajP]

Oops not a great example. In this case I only move from list 1 to list 2, but do not take away from list 1 when I move to list 2. However, a little extra code should allow you to take out of 1. Just fire the remove item method, again.

 

[MajP]

Oops again. I went back and read my code, and now I understand what you meant. Here is the conversion from query to value list. Yes, the remove and add item requires a value list. This is how I did that.

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("qryPersonnel")
  Do While Not rs.EOF
    Me.lstUnselected.AddItem (rs.Fields("strLastName"))
    rs.MoveNext
 Loop
End Sub

You would do something similar on list 2, and maybe fire the sub again on the "on current'

 

[jebry]

Hi!

Well, the method I outlined I use regularly and it works without a hitch. The main point is that you need a way to say in the query that a specific record belongs in list box one or two. To me, the easiest way is to add the yes/no field and manipulate the field in the code and requery the list boxes. Alternatively you could build a Where clause on the fly using the selected items from the list box:

Where fldID Is Not In ("First selection", "Second ...)

And then build the query on the fly and change the rowsource of the list boxes. But that seems considerably more cumbersome!

If you need to see this in action, follow the link below and search using my name and you can get my email address. If you show me the form, tables and queries in question, I should be able to show you how to implement my suggestion.

If you are interested.

http://www.itap.purdue.edu/directory/

Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[yummy7]

MajP i used ur procedure but it is giving error msgbox saying that this method is appropriate only for row source type valuelist.so wht should i do now....here in my case The row source type of List1 is "ON"(the name of query )my whole day is spent on it
I think we should use recordset but i dont know how to use it...i m begginner in MSAccess.

 

[yummy7]

Ya Majp...u r right that why i need to convert rowsource type....because i was successfull to apply these functionalities with valuelist...but when i want to apply it on query it doesnt work so i requested to change query type into value so that i can apply these functionalities of selecting and deselecting.
Now plz help me out that how can I apply the same functinalities on rowsource type 'query'.
I shall b grateful to u all.

 

[MajP]

yummy7,
You must have missed my repost, because I gave you bad information. Here is a cleaned up version if you are using 2 listboxes with one column each. If you are using multiple columns, I would do what Jebry suggests. It can be done as I said but you will have to do more work.

What I am suggesting is that you read a query to populate your value lists. Look at the form load event. If that does not work for you do what jebry said.

Private Sub cmdSelect_Click()
  Call moveBetweenLists("lstUnSelected", "lstSelected")
End Sub

Private Sub cmdUnselect_Click()
  Call moveBetweenLists("lstSelected", "lstUnSelected")
End Sub

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("qryOne")
  Do While Not rs.EOF
    Me.lstUnselected.AddItem (rs.Fields("strLastName"))
    rs.MoveNext
 Loop
 Set rs = CurrentDb.OpenRecordset("qryTwo")
  Do While Not rs.EOF
    Me.lstSelected.AddItem (rs.Fields("strLastName"))
    rs.MoveNext
 Loop
End Sub

Public Sub moveBetweenLists(strFromList As String, strToList As String)
 On Error GoTo err_list
 Dim frmLstBox As Access.ListBox
 Dim toLstBox As Access.ListBox
 Set frmLstBox = Me.Controls(strFromList)
 Set toLstBox = Me.Controls(strToList)
 Dim listValue As String
 If frmLstBox.ListIndex = -1 Then
       MsgBox "Must select a field"
       GoTo Exit_Sub
 End If
 listValue = frmLstBox.Value
 frmLstBox.RemoveItem (frmLstBox.ListIndex)
 toLstBox.AddItem (listValue)
Exit_Sub:
  Exit Sub
err_list:
   MsgBox Err.Description
   Resume Exit_Sub
 Exit Sub
End Sub

 

[MajP]

Here is another way to convert the query to a value list.

Private Sub Form_Load()
Dim rs As DAO.Recordset
Dim strRowSource As String
Set rs = CurrentDb.OpenRecordset("QryTwo")
Do While Not rs.EOF
strRowSource = strRowSource & (rs.Fields("strLastName")) & ";"
rs.MoveNext
Loop
If Len(strRowSource) > 0 Then
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
End If
Me.lstSelected.RowSource = strRowSource
End Sub

 

[yummy7]

As u r forcing to convert query into valuelist means that to do these functions(selecting,deselecting).we need to make row source type=valuelist.isnt it?
first of all clear that now we have only one possibily to accomplish it with value list not with query.

 

[MajP]

No. If you go with jebry's solution, you can work just with a query and not convert to a value list. I did a convert.

 

[yummy7]

hi jebry,
i mailed u.here in ur example i cant understand clearly.i want to use 4 command buttons,which can selct,deselect,select All,deselct All.And data in List1 is coming from a query.
so plz help me.and remain in touch.

 

[MajP]

Yummy,
I played with the idea a little more. Converting a query to a value list seems like an easy way to go. Because you can that take advantage of the additem and removeitem methods. I changed the procedure that allows you to move to and from a list. This now allows a multi select list box and a select all capability. Basically my control mimics something you see on the Form Wizard where you can move fields back and forth.

Private Sub cmdSelectAll_Click()
  Dim counter As Integer
  For counter = 0 To lstUnselected.ListCount - 1
      Me.lstUnselected.Selected(counter) = True
  Next counter
  Call moveBetweenLists(Me.lstUnselected, Me.lstSelected)
End Sub


Private Sub cmdUnselectAll_Click()
  Dim counter As Integer
  For counter = 0 To lstSelected.ListCount - 1
      Me.lstSelected.Selected(counter) = True
  Next counter
  Call moveBetweenLists(Me.lstSelected, Me.lstUnselected)
End Sub

Public Sub moveBetweenLists(lstBoxFrom As Access.ListBox, lstBoxTo As Access.ListBox)
 On Error GoTo err_list
 Dim counter As Integer
 Dim varListItem As Variant
 Dim indexArray() As Variant
 Dim listValue As Variant
 Dim intCountSelected As Integer
 ReDim indexArray(0 To lstBoxFrom.ListCount)
 For Each varListItem In lstBoxFrom.ItemsSelected
   listValue = lstBoxFrom.ItemData(varListItem)
   lstBoxTo.AddItem (listValue)
   indexArray(counter) = varListItem
   counter = counter + 1
 Next varListItem
 intCountSelected = lstBoxFrom.ItemsSelected.Count
 For counter = 0 To intCountSelected - 1
   lstBoxFrom.RemoveItem (indexArray(counter) - counter)
 Next counter
Exit_Sub:
  Exit Sub
err_list:
   MsgBox Err.Description
   Resume Exit_Sub
 Exit Sub
End Sub

 

[yummy7]

MajP can u plz illustrate ur example that in which part what u have done so that i can understand it more.Because i already told u that i m new,may b i cannot understand whts going on in this code of line and got confuse.
Anyhow thanx for being in touch although u replied late

 

[yummy7]

MajP ,
I m also getting msgbox "invalid use of null value".
i dont know why i m getting this.

 

[yummy7]

Dear I tried alot.Getting msgs "Invalid use of null" or "The rowType property should set to 'valuelist '"to use this method.
Currently,i have copied and past the whole code(changed the value of listboxes lstSelected and lstunSelected nothing else).Both have row source type"table query".
i have shown u all the situtation.
I dont know why i m getting error msgs.
Anyhow thanx.if u can do anything in this regard or understand the situation .do tell me.