Multiselect listbox - reading amount of values selected and values

  • Thread starter Thread starter Przemek Wrzesiñski
  • Start date Start date
P

Przemek Wrzesiñski

Hi,

I've got a form with multiselect listbox filled with names - it's
query from tblEmployees (EmployeeID and EmployeeName). In the same
form I put a textbox where user will put the numbers. Calculation,
which I want to do, depends on how many names are selected and
simultaneously pass calculated amount with selected names (actually
EmployeeID) to another table, e.g. listbox contains: John, Mark, Sean.
User selected: "John" and "Sean" and the amount given by user is 1000.
So I want to add 2 records "John 500" and "Sean 500". I know how to
add record but I have a problem to tell Access: how many names is
selected , what names was selected and what calculations are needed
with this selection.

TIA

Przemek
 
You will probably need several queries to get to desired
result. The text field that users input amounts must be a
field that you have added to the tblEmployees. Then the
amount will be tied to the correct employee. Then you can
easily create a query based on that table for your
calculation. In order to get the amount of names that
were entered, create a query that is grouped, and use
the "Count" grouping option on the names.
Hope this helps,
Eve
 
Przemek Wrzesiñski said:
Piêknego dnia Tue, 9 Sep 2003 06:41:59 -0700, osobnik ukrywajacy sie
pod pseudonimem "Eve" <[email protected]> w wiadomosci


I figured out different thing.
Dim ctlSource As Control
Dim intCurrentRow As Integer
Dim Choice As String

Set ctlSource = EmployeeList

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
Choice = Choice + ctlSource.Column(0, intCurrentRow)
End If
Next intCurrentRow
Set ctlSource = Nothing

At the end of this I'm receiving string with id's selected employees.
Length of string tells me the number I have to divide given amount by
and the content of string tells me the employee/employees selected.
Each id's combination is unique, so using if/else/end if construction
I can fill another (not tblEmployee) table with records.

Przemek
Click to expand...

Rather than looping through all the items in the list and only acting on
the selected ones, you could simplify your logic by using the list box's
ItemsSelected collection. EmployeeList.ItemsSelected.Count tells you
how many employees were selected, and each item in the collection is the
row number of a selected employee.
 
Rather than looping through all the items in the list and only acting on
the selected ones, you could simplify your logic by using the list box's
ItemsSelected collection. EmployeeList.ItemsSelected.Count tells you
how many employees were selected, and each item in the collection is the
row number of a selected employee.
Click to expand...

Tx very much for help.

Przemek
 
Back
Top