F
frauschmid
Is it possible to display more than one column in the lstAvailable combo box?
if so how?
Any help would be appreciated. thanks.
if so how?
Any help would be appreciated. thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Ken Sheridan
Gnädige Frau:
I don't know what the lstAvailable control is but you can include multiple
columns in the drop down list of a combo box or in a list box. If the
RowSource is a query return the columns in question and set the combo box's
ColumnCount property to the number of columns and its ColumnWidths property
to the dimensions you want for each column, e.g. you might have a RowSource
property of:
SELECT CustomerID, Customer, City
FROM Customers INNER JOIN Cities
ON Customer.CityID = Cities.CityID
ORDER BY Customer,City;
If you have two customers of the same name in different cities this will
allow the user to distinguish between them. To hide the CustomerID the first
column's dimension should be set to zero, so just the name would show in the
control after a selection is made. The other properties would be:
BoundColumn: 1
ColumnCount: 3
ColumnWidths 0cm;4cm;3cm
ListWidth: 7cm
Experiment with the non-zero column widths to get the best fit. The
ListWidth should be the sum of the column widths. The BoundColumn determines
which column provides the Value of the control, in this case the CustomerID
column.
With the above example if you wanted to show the city of the selected
customer on the form after the selection is made rather than simply in the
drop down list you would add a text box to the form with a ControlSource
which referenced the third column of the combo box. The Column property is
zero-based, so it would be along these lines:
=cboCustomers.Column(2)
If the RowSource property is a value list you set the other properties in
the same way, and list the values separated by semi-colons, so the value list
for a two column list might be:
Apple;Fruit;Pear;Fruit;Potato;Vegetable
which, with a ColumnCount property of 2, would give a list like this:
Apple Fruit
Pear Fruit
Potato Vegetable
A multi-column list box is set up in the same way, but without a ListWidth
property as there is no drop down list of course.
Ken Sheridan
Stafford, England
I don't know what the lstAvailable control is but you can include multiple
columns in the drop down list of a combo box or in a list box. If the
RowSource is a query return the columns in question and set the combo box's
ColumnCount property to the number of columns and its ColumnWidths property
to the dimensions you want for each column, e.g. you might have a RowSource
property of:
SELECT CustomerID, Customer, City
FROM Customers INNER JOIN Cities
ON Customer.CityID = Cities.CityID
ORDER BY Customer,City;
If you have two customers of the same name in different cities this will
allow the user to distinguish between them. To hide the CustomerID the first
column's dimension should be set to zero, so just the name would show in the
control after a selection is made. The other properties would be:
BoundColumn: 1
ColumnCount: 3
ColumnWidths 0cm;4cm;3cm
ListWidth: 7cm
Experiment with the non-zero column widths to get the best fit. The
ListWidth should be the sum of the column widths. The BoundColumn determines
which column provides the Value of the control, in this case the CustomerID
column.
With the above example if you wanted to show the city of the selected
customer on the form after the selection is made rather than simply in the
drop down list you would add a text box to the form with a ControlSource
which referenced the third column of the combo box. The Column property is
zero-based, so it would be along these lines:
=cboCustomers.Column(2)
If the RowSource property is a value list you set the other properties in
the same way, and list the values separated by semi-colons, so the value list
for a two column list might be:
Apple;Fruit;Pear;Fruit;Potato;Vegetable
which, with a ColumnCount property of 2, would give a list like this:
Apple Fruit
Pear Fruit
Potato Vegetable
A multi-column list box is set up in the same way, but without a ListWidth
property as there is no drop down list of course.
Ken Sheridan
Stafford, England
F
frauschmid
This is what I am working with, unfortunatley I do not understand the
SETARRAY method. Sorry, I should have included it in the original post.
thanks for your response.
Private Sub Form_Open(Cancel As Integer)
' Four steps to using Multipik:
' 1. Instantiate the object
' 2. Set up the RowSourceType for the list boxes.
' If you did this at design time, you needn't
' do it now.
' 3. Register the 8 controls with the object
' 4. Tell it where your data comes from
Set mmp = New MultiPik
mmp.RegisterControls _
lstAvailable, lstSelected, _
cmdAddOne, cmdAddAll, _
cmdDeleteOne, cmdDeleteAll, _
cmdUp, cmdDown
' Modify the following line to
' match your own needs.
' Specify a recordsource (table
' or query name) and a field to display.
mmp.SetData "PERSONNELfiltered", "fullname"
lstAvailable.RowSourceType = "FillLists"
lstSelected.RowSourceType = "FillLists"
' You can also pass in two arrays.
' mmp.SetArrays Array("Item1", "Item2", "Item3", "Item4", "Item5"),
Array("Item6")
' Or two collections, using the SetCollections method.
End Sub
SETARRAY method. Sorry, I should have included it in the original post.
thanks for your response.
Private Sub Form_Open(Cancel As Integer)
' Four steps to using Multipik:
' 1. Instantiate the object
' 2. Set up the RowSourceType for the list boxes.
' If you did this at design time, you needn't
' do it now.
' 3. Register the 8 controls with the object
' 4. Tell it where your data comes from
Set mmp = New MultiPik
mmp.RegisterControls _
lstAvailable, lstSelected, _
cmdAddOne, cmdAddAll, _
cmdDeleteOne, cmdDeleteAll, _
cmdUp, cmdDown
' Modify the following line to
' match your own needs.
' Specify a recordsource (table
' or query name) and a field to display.
mmp.SetData "PERSONNELfiltered", "fullname"
lstAvailable.RowSourceType = "FillLists"
lstSelected.RowSourceType = "FillLists"
' You can also pass in two arrays.
' mmp.SetArrays Array("Item1", "Item2", "Item3", "Item4", "Item5"),
Array("Item6")
' Or two collections, using the SetCollections method.
End Sub
K
Ken Sheridan
From a brief examination of the MultiPik class in the Access Developer's
Handbook it looks to me that as written it can only show one column in the
list. The code for the class is commented as follows with respect to storing
more than one column:
' This is the data type for the main array.
' It also contains the fSelected flag, indicating whether or not
' each row is currently selected.
' If you need to store more than one column
' of data, you'll need to add more members to this
' data structure.
Private Type DataRow
varData As Variant
fSelected As Integer
End Type
However, using the class as written, you could concatenate the values from
more than one column into a single column in a query. This won't show the
values from each column vertically aligned as separate columns but it will
show the values from both as a single string. This might even be
advantageous if the columns are something like FirstName and LastName as
concatenating these would simply show the full name, e.g. Ken Sheridan.
To try it out open the CH07.mdb file from the ADH CD and create the
following query as qryContacts:
SELECT[Contact Name] & ", " & Address AS [Contact Address]
FROM Customers
ORDER BY [Contact Name];
Then amend the Open event procedure of the frmMultiPik form so it uses this
query:
Private Sub Form_Open(Cancel As Integer)
' Four steps to using Multipik:
' 1. Instantiate the object
' 2. Set up the RowSourceType for the list boxes.
' If you did this at design time, you needn't
' do it now.
' 3. Register the 8 controls with the object
' 4. Tell it where your data comes from
Set mmp = New MultiPik
mmp.RegisterControls _
lstAvailable, lstSelected, _
cmdAddOne, cmdAddAll, _
cmdDeleteOne, cmdDeleteAll, _
cmdUp, cmdDown
' Modify the following line to
' match your own needs.
' Specify a recordsource (table
' or query name) and a field to display.
mmp.SetData "qryContacts", "Contact Address" ' this is the amended line
lstAvailable.RowSourceType = "FillLists"
lstSelected.RowSourceType = "FillLists"
' You can also pass in two arrays.
' mmp.SetArrays Array("Item1", "Item2", "Item3", "Item4", "Item5"),
Array("Item6")
' Or two collections, using the SetCollections method.
End Sub
While the MultiPik class is an elegant and fully featured solution you can
select items from one list box and place them into another much more simply.
The following is the code for the module of a simple form with two 2-column
multiselect list boxes, lstFrom and lstTo, the latter's RowSourceType being a
Value List, and two buttons, one for selecting and moving all rows from
lstFrom to lstTo, the other for clearing all the selections and emptying
lstTo. It works quite well with relatively short lists, but its not really
suitable fro very long lists:
Option Compare Database
Option Explicit
Private Sub cmdClearSelections_Click()
Dim n As Integer
For n = 0 To Me.lstFrom.ListCount - 1
Me.lstFrom.Selected(n) = False
Next n
lstFrom_AfterUpdate
End Sub
Private Sub cmdSelectAll_Click()
Dim n As Integer
For n = 0 To Me.lstFrom.ListCount - 1
Me.lstFrom.Selected(n) = True
Next n
lstFrom_AfterUpdate
End Sub
Private Sub lstFrom_AfterUpdate()
Dim varItem As Variant
Dim strSelectedItems As String
Dim ctrl As Control
Set ctrl = Me.lstFrom
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSelectedItems = strSelectedItems & ";" & _
ctrl.Column(0, varItem) & ";" & _
ctrl.Column(1, varItem)
Next varItem
strSelectedItems = Mid(strSelectedItems, 2)
Me.lstTo.RowSource = strSelectedItems
Else
Me.lstTo.RowSource = ""
End If
Me.lstTo.Requery
End Sub
The 'from' list box remains unchanged, i.e. it doesn't move items from one
to another, but simply copies all the currently selected rows from one into
the other. You can remove an item from the 'to' list simply by deselecting
it in the 'from' list. The order of rows in the 'to' list will always match
that in the 'from' list.
I expect that you could find other examples of how to do this sort of thing
with a bit of Googling around.
Ken Sheridan
Stafford, England
Handbook it looks to me that as written it can only show one column in the
list. The code for the class is commented as follows with respect to storing
more than one column:
' This is the data type for the main array.
' It also contains the fSelected flag, indicating whether or not
' each row is currently selected.
' If you need to store more than one column
' of data, you'll need to add more members to this
' data structure.
Private Type DataRow
varData As Variant
fSelected As Integer
End Type
However, using the class as written, you could concatenate the values from
more than one column into a single column in a query. This won't show the
values from each column vertically aligned as separate columns but it will
show the values from both as a single string. This might even be
advantageous if the columns are something like FirstName and LastName as
concatenating these would simply show the full name, e.g. Ken Sheridan.
To try it out open the CH07.mdb file from the ADH CD and create the
following query as qryContacts:
SELECT[Contact Name] & ", " & Address AS [Contact Address]
FROM Customers
ORDER BY [Contact Name];
Then amend the Open event procedure of the frmMultiPik form so it uses this
query:
Private Sub Form_Open(Cancel As Integer)
' Four steps to using Multipik:
' 1. Instantiate the object
' 2. Set up the RowSourceType for the list boxes.
' If you did this at design time, you needn't
' do it now.
' 3. Register the 8 controls with the object
' 4. Tell it where your data comes from
Set mmp = New MultiPik
mmp.RegisterControls _
lstAvailable, lstSelected, _
cmdAddOne, cmdAddAll, _
cmdDeleteOne, cmdDeleteAll, _
cmdUp, cmdDown
' Modify the following line to
' match your own needs.
' Specify a recordsource (table
' or query name) and a field to display.
mmp.SetData "qryContacts", "Contact Address" ' this is the amended line
lstAvailable.RowSourceType = "FillLists"
lstSelected.RowSourceType = "FillLists"
' You can also pass in two arrays.
' mmp.SetArrays Array("Item1", "Item2", "Item3", "Item4", "Item5"),
Array("Item6")
' Or two collections, using the SetCollections method.
End Sub
While the MultiPik class is an elegant and fully featured solution you can
select items from one list box and place them into another much more simply.
The following is the code for the module of a simple form with two 2-column
multiselect list boxes, lstFrom and lstTo, the latter's RowSourceType being a
Value List, and two buttons, one for selecting and moving all rows from
lstFrom to lstTo, the other for clearing all the selections and emptying
lstTo. It works quite well with relatively short lists, but its not really
suitable fro very long lists:
Option Compare Database
Option Explicit
Private Sub cmdClearSelections_Click()
Dim n As Integer
For n = 0 To Me.lstFrom.ListCount - 1
Me.lstFrom.Selected(n) = False
Next n
lstFrom_AfterUpdate
End Sub
Private Sub cmdSelectAll_Click()
Dim n As Integer
For n = 0 To Me.lstFrom.ListCount - 1
Me.lstFrom.Selected(n) = True
Next n
lstFrom_AfterUpdate
End Sub
Private Sub lstFrom_AfterUpdate()
Dim varItem As Variant
Dim strSelectedItems As String
Dim ctrl As Control
Set ctrl = Me.lstFrom
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSelectedItems = strSelectedItems & ";" & _
ctrl.Column(0, varItem) & ";" & _
ctrl.Column(1, varItem)
Next varItem
strSelectedItems = Mid(strSelectedItems, 2)
Me.lstTo.RowSource = strSelectedItems
Else
Me.lstTo.RowSource = ""
End If
Me.lstTo.Requery
End Sub
The 'from' list box remains unchanged, i.e. it doesn't move items from one
to another, but simply copies all the currently selected rows from one into
the other. You can remove an item from the 'to' list simply by deselecting
it in the 'from' list. The order of rows in the 'to' list will always match
that in the 'from' list.
I expect that you could find other examples of how to do this sort of thing
with a bit of Googling around.
Ken Sheridan
Stafford, England