How to prevent reloading a List box multiple times

[shanl]

I'm sure this is basic but I'm new so bear with me.

I have two list boxes of which I will populate the first list box with names and then will select names from the first list box to be moved to the second list box for additional processing.

The first list box I populate is based on criteria entered on an Access form. I tried doing a Load of the particular list box, but that was too early in the process as the fields on my Access form haven't been filled out yet which are used in the criteria to load the list box. If I populate my list box upon Enter or On Focus, it works great until I select the second, third etc person in which it re-executes the logic again to populate the list box and I get multiple sets of the same people in the list list box. What is the best way to load my list box so it will only do it once?

 

[Andrzejek]

If you would post your code of how you do it now - I am sure there will be plenty of people who can show you a better / correct way to do so.

Just guessing here, but a command button comes to mind where you can add sigle person from List1 to List2 by List2.AddItem method.


Have fun.

---- Andy

 

[shanl]

I do have four command buttons set up to move the items from one list box to the other and they are working for the most part (other than my not being able to move more than one column to the second list box, but thats a different problem.)

This is the code that populates the list box. As you can see I have it set up on a GotFocus action. Each time I select a person in this box, it repopuluates the list over and over adding the same subset multiple times I click in the box. Is there a way to set it so it will stop once I've clicked it the first time.

Private Sub Employeelist_GotFocus()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String, strItem As String

strSQL = "SELECT DISTINCT EmployeeDept_qry2.zLName, EmployeeDept_qry2.zFName, EmployeeDept_qry2.zBadgeID FROM EmployeeDept_qry2 WHERE EmployeeDept_qry2.zDeptDesc = " & "'" & [Forms]![Main_frm]![DepartmentList] & "'"
'ORDER BY [EmployeeDept_qry2.zLname]"
'& "And ((EmployeeDept_qry.dtActEnd) >=" & ([Forms]![Main_frm]![StartDate]) & "Or & (((EmployeeDept_qry.zDeptDesc) =" & ([Forms]![Main_frm]![DepartmentList]) & "And ((EmployeeDept_qry.bFullTime) =" & True & "And ((EmployeeDept_qry.dtActEnd) Is" & Null))


Set cn = Application.CurrentProject.Connection

Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

Do Until rs.EOF
strItem = rs.Fields("zLname").Value & ";" & rs.Fields("zfName").Value & ";" & rs.Fields("zBadgeId").Value
Me.Employeelist.AddItem strItem 'Row Source Type must be Value List
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Set cn = Nothing
End Sub

 

[SkipVought]

How about...

if Me.Employeelist.listcount = 0 then
 'now fill the listbox
end if

Skip,

_[red][/red]

 

[PHV]

As you talked about an Access form, why all this ADO stuff ?
Set the RowSourceType to Table/Query instead of Value List, and then you simply have to do:
Me!Employeelist.RowSource = strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shanl]

Skip,
Thanks, you're the best. That did the job.

PHV,
You asked why I put all the ADO stuff in. In one of the examples I got this from, it said it HAD to be a VALUE list. My initial start was using a rowsourceType to a Query and I couldn't get it to move the items when I used the command buttons. The example I was using said it HAD to be a VALUE list so I used it. So far I have everything working except I don't know how to expand my command button to move three fields instead of One to my other list box. Here's the code for moving from one list box to the other list box. Do you see what I'm missing on what I need to change to move three columns instead of one?

Private Sub cmdMoveAlltoList1_Click()
MoveAllItems "List2", "EmployeeList"
End Sub
Private Sub cmdMoveAlltoList2_Click()
MoveAllItems "EmployeeList", "List2"
End Sub
Private Sub cmdMoveToList1_Click()
MoveSingleItem "List2", "EmployeeList"
End Sub
Private Sub cmdMoveToList2_Click()
MoveSingleItem "EmployeeList", "List2"
End Sub
Sub MoveSingleItem(strSourceControl As String, strTargetControl As String)
Debug.Print
Dim strItem As String
Dim intColumnCount As Integer
For intColumnCount = 0 To Me.Controls(strSourceControl).ColumnCount - 1
strItem = strItem & Me.Controls(strSourceControl).Column(intColumnCount) & ";"
Next
strItem = Left(strItem, Len(strItem) - 1)
'Check the length to make sure something is selected

If Len(strItem) > 0 Then
Me.Controls(strTargetControl).AddItem strItem
Me.Controls(strSourceControl).RemoveItem Me.Controls(strSourceControl).ListIndex
Else
MsgBox "Please Select an employee to move."
End If
End Sub
Sub MoveAllItems(strSourceControl As String, strTargetControl As String)
Dim strItem As String
Dim intColumnCount As Integer
Dim lngRowCount As Long
For lngRowCount = 0 To Me.Controls(strSourceControl).ListCount - 1
For intColumnCount = 0 To Me.Controls(strSourceControl).ColumnCount - 1
strItem = strItem & Me.Controls(strSourceControl).Column(intColumnCount, lngRowCount) & ";"
Next
strItem = Left(strItem, Len(strItem) - 1)
Me.Controls(strTargetControl).AddItem strItem
strItem = ""
Next

Me.Controls(strSourceControl).RowSource = ""
End Sub

 

[PHV]

Why not simply a single MultiSelect listbox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shanl]

I am trying to keep the process similar to one the department is used to using. (Granted, using a multiselect shouldn't be tough transition but if I can make it the same that would have been preferred)