How to create a multiselect combo box? 1

Niki_S · Aug 8, 2022

I have a combobox as "cboDes" and there have some designations as below,

ACCOUNT ASSISTANT
ASSISTENT OFFICE
BOILER OPERATORS
CAD ROOM ASSISTANT
CANTEEN HELPER
CARPENTER
CLERK
CO-ORDINATOR
CO-ORDINATOR - CUTTING

Now I want to select more than one designation. After I select them I want to filter data from sql as below,

_Designation = thisform.cboDes.DisplayValue
stra = "SELECT cFact,nEmpNo,cFullName,cDepartment,dJoin,dResign "
stra = stra + "from HRIS.dbo.vHRIS_Deletion where cDesignation = ?_Designation and "
stra = stra + "cFact=?_Fac and dResign>= ?thisform.txtFromDate.Value AND dResign<= ?thisform.txtToDate.Value order by cFact,nEmpNo"
SQLEXEC(hndOps,stra,'Emp_Detl')

How can I create a multiselect combobox and filter data from sql according the the selected values?

Thank you.

Mike Gagnon · Aug 8, 2022

https://github.com/VFPX/cboMultiSel

If you want to get the best response to a question, please check out FAQ184-2483 first.

mjcmkrsr · Aug 8, 2022

Hi Niki S,

You may also use the native MultiSelect = .T. for listboxes - see code below and feel free to adapt it to your needs

Code:

PUBLIC goForm

goForm = CREATEOBJECT("MyForm")
goForm.Show()

READ EVENTS

CLOSE ALL
CLEAR ALL 

**********

DEFINE CLASS MyForm as Form
	cCountry = ""
	cSelection = ""
	
	Width = 480
	Height = 270
	MinWidth = This.Width
	MinHeight = This.Height
	MaxWidth = This.Width
	Caption = "Cars on Stock - Multiselect CTRL + click"
	AutoCenter = .T.
	ShowTips = .T.
	Themes = .F.
	
	ADD OBJECT lblReturnValue as Label WITH ;
		Top = 132, ;
		Left = ThisForm.Width - 126, ;
		Caption = "Listbox return value", ;
		AutoSize = .T., ;
		Anchor = 9
	
	ADD OBJECT opgChoice as OptionGroup WITH ;
		Top = 12, ;
		Left = ThisForm.Width - 126, ;
		ButtonCount = 6, ;
		Value = 6, ;
		AutoSize = .T., ;
		Anchor = 9
		
		PROCEDURE opgChoice.Init()
			LOCAL loButton
			
			FOR i = 1 TO This.ButtonCount
				WITH This.Buttons(i)
					.AutoSize = .T.
					.Caption = ICASE(i = 1, "US cars", i = 2, "FR cars", i = 3,"DE cars", i = 4, "GB cars", i = 5, "IT cars", "All")
				ENDWITH 
			ENDFOR 
		ENDPROC 
		
		PROCEDURE opgChoice.Click()
			DO CASE 
				CASE This.Value = 1
					ThisForm.cCountry = "US"
				
				CASE This.Value = 2
					ThisForm.cCountry = "FR"
				
				CASE This.Value = 3
					ThisForm.cCountry = "DE"
				
				CASE This.Value = 4
					ThisForm.cCountry = "GB"
				
				CASE This.Value = 5
					ThisForm.cCountry = "IT"
				
				CASE This.Value = 6
					ThisForm.cCountry = ""
				
			ENDCASE 
			
			ThisForm.lstList.Requery()
			
		ENDPROC 

	ADD OBJECT opgListValue as OptionGroup WITH ;
		Top = 150, ;
		Left = ThisForm.Width - 126, ;
		ButtonCount = 3, ;
		Value = 1, ;
		AutoSize = .T., ;
		Anchor = 9
		
		PROCEDURE opgListValue.Init()
			LOCAL loButton
			
			FOR i = 1 TO This.ButtonCount
				WITH This.Buttons(i)
					.AutoSize = .T.
					.Caption = ICASE(i = 1, "Code", i = 2, "Item", "Quantity")
				ENDWITH 
			ENDFOR 
		ENDPROC 
		
		PROCEDURE opgListValue.Click()
			ThisForm.lstList.BoundColumn = This.Value
				
		ENDPROC 

	ADD OBJECT lstList as ListBox WITH ;
		Top = 12, ;
		Left = 12, ;
		Width = ThisForm.Width - 144, ;
		Height = ThisForm.Height - 24, ;
		ItemBackColor = RGB(0, 240, 240), ;
		Anchor = 15, ;
		RowSourceType = 6, ;
		RowSource = "cItemCode, cItemName, iQuantity", ;
		ColumnCount = 4, ;
		ColumnWidths = "54, 120, 48, 0", ;
[highlight #4E9A06]		Multiselect = .T., ;
[/highlight]		IncrementalSearch = .T.
		
		PROCEDURE lstList.Click()
			ThisForm.cSelection = ThisForm.cSelection + ALLTRIM(This.Value) + " - "
			
		ENDPROC 
		
		PROCEDURE lstList.Requery()
			Select cItemCode, cItemName, iQuantity ;
				FROM csrOne ;
				WHERE cCountry = ThisForm.cCountry ;
				ORDER BY 1 ;
				INTO CURSOR csrCars
				
		ENDPROC 
		
		PROCEDURE lstList.Init()
			This.Requery()
			
		ENDPROC
		
	ADD OBJECT cmdShow as CommandButton WITH ;
		Top = 216, ;
		Left = ThisForm.Width - 126, ;
		Height = 36, ;
		BackColor = RGB(0, 240, 240), ;
		Caption = "Show Selection"
		
		PROCEDURE cmdShow.Click()
			WAIT WINDOW + "You choose " + Thisform.cSelection TIMEOUT 5

			ThisForm.cSelection = ""
			ThisForm.lstList.Requery
			
		ENDPROC 
		
	PROCEDURE Load()
		CREATE CURSOR csrOne (cItemCode C(5), cItemName C(15), iQuantity I , cCountry C(2))
		
		INSERT INTO csrOne VALUES ("AAAAA", "GMG X1", 5, "US")
		INSERT INTO csrOne VALUES ("AAAAB", "GMG Y2", 4, "US")
		INSERT INTO csrOne VALUES ("AAAAC", "Renault Zoe", 12, "FR")
		INSERT INTO csrOne VALUES ("AAAAD", "Renault Twizzy", 3, "FR")
		INSERT INTO csrOne VALUES ("AAAAE", "Renault Twingo", 17, "FR")
		INSERT INTO csrOne VALUES ("AAAAF", "BMW 3", 15, "DE")
		INSERT INTO csrOne VALUES ("AAAAG", "BMW 5", 25, "DE")
		INSERT INTO csrOne VALUES ("AAAAH", "VW Golf", 51, "DE")
		INSERT INTO csrOne VALUES ("AAAAI", "Audi A7", 14, "DE")
		INSERT INTO csrOne VALUES ("AAAAJ", "Austin", 11, "GB")
		INSERT INTO csrOne VALUES ("AAAAK", "Triumph", 9, "GB")
		INSERT INTO csrOne VALUES ("AAAAL", "Morgan", 9, "GB")
		INSERT INTO csrOne VALUES ("AAAAM", "Alfa Romeo", 6, "IT")
		INSERT INTO csrOne VALUES ("AAAAN", "Fiat", 5, "IT")
		INSERT INTO csrOne VALUES ("AAAAO", "Maserati", 2, "IT")
		INSERT INTO csrOne VALUES ("AAAAP", "Peugeot 403", 2, "FR")
			
	PROCEDURE Destroy()
		ThisForm.Release
		CLEAR Events
		
	ENDPROC
ENDDEFINE

hth

MarK

mjcmkrsr · Aug 9, 2022

Hi Niki S

The updated code to avoid "multi - multi - selections"

Code:

PUBLIC goForm

goForm = CREATEOBJECT("MyForm")
goForm.Show()

READ EVENTS

CLOSE ALL
CLEAR ALL 

**********

DEFINE CLASS MyForm as Form
	cCountry = ""
	DIMENSION aSelection[1]
	
	Width = 480
	Height = 270
	MinWidth = This.Width
	MinHeight = This.Height
	MaxWidth = This.Width
	Caption = "Cars on Stock - Multiselect CTRL + click"
	AutoCenter = .T.
	ShowTips = .T.
	Themes = .F.
	
	ADD OBJECT lblReturnValue as Label WITH ;
		Top = 132, ;
		Left = ThisForm.Width - 126, ;
		Caption = "Listbox return value", ;
		AutoSize = .T., ;
		Anchor = 9
	
	ADD OBJECT opgChoice as OptionGroup WITH ;
		Top = 12, ;
		Left = ThisForm.Width - 126, ;
		ButtonCount = 6, ;
		Value = 6, ;
		AutoSize = .T., ;
		Anchor = 9
		
		PROCEDURE opgChoice.Init()
			LOCAL loButton
			
			FOR i = 1 TO This.ButtonCount
				WITH This.Buttons(i)
					.AutoSize = .T.
					.Caption = ICASE(i = 1, "US cars", i = 2, "FR cars", i = 3,"DE cars", i = 4, "GB cars", i = 5, "IT cars", "All")
				ENDWITH 
			ENDFOR 
		ENDPROC 
		
		PROCEDURE opgChoice.Click()
			DO CASE 
				CASE This.Value = 1
					ThisForm.cCountry = "US"
				
				CASE This.Value = 2
					ThisForm.cCountry = "FR"
				
				CASE This.Value = 3
					ThisForm.cCountry = "DE"
				
				CASE This.Value = 4
					ThisForm.cCountry = "GB"
				
				CASE This.Value = 5
					ThisForm.cCountry = "IT"
				
				CASE This.Value = 6
					ThisForm.cCountry = ""
				
			ENDCASE 
			
			ThisForm.ResetArray()
			
		ENDPROC 

	ADD OBJECT opgListValue as OptionGroup WITH ;
		Top = 150, ;
		Left = ThisForm.Width - 126, ;
		ButtonCount = 3, ;
		Value = 1, ;
		AutoSize = .T., ;
		Anchor = 9
		
		PROCEDURE opgListValue.Init()
			LOCAL loButton
			
			FOR i = 1 TO This.ButtonCount
				WITH This.Buttons(i)
					.AutoSize = .T.
					.Caption = ICASE(i = 1, "Code", i = 2, "Item", "Quantity")
				ENDWITH 
			ENDFOR 
		ENDPROC 
		
		PROCEDURE opgListValue.Click()
			ThisForm.lstList.BoundColumn = This.Value
			ThisForm.ResetArray()
				
		ENDPROC 

	ADD OBJECT lstList as ListBox WITH ;
		Top = 12, ;
		Left = 12, ;
		Width = ThisForm.Width - 144, ;
		Height = ThisForm.Height - 24, ;
		ItemBackColor = RGB(0, 240, 240), ;
		Anchor = 15, ;
		RowSourceType = 6, ;
		RowSource = "cItemCode, cItemName, iQuantity", ;
		ColumnCount = 4, ;
		ColumnWidths = "54, 120, 48, 0", ;
		Multiselect = .T., ;
		IncrementalSearch = .T.
		
		PROCEDURE lstList.Click()
			IF ASCAN(Thisform.aSelection, ALLTRIM(This.Value)) = 0
				ThisForm.aSelection[ALEN(ThisForm.aSelection)] = ALLTRIM(This.Value)
				DIMENSION ThisForm.aSelection[ALEN(ThisForm.aSelection) + 1]

			ENDIF
		ENDPROC 
		
		PROCEDURE lstList.Requery()
			Select cItemCode, cItemName, iQuantity ;
				FROM csrOne ;
				WHERE cCountry = ThisForm.cCountry ;
				ORDER BY 1 ;
				INTO CURSOR csrCars
				
		ENDPROC 
		
		PROCEDURE lstList.Init()
			This.Requery()
			
		ENDPROC
		
	ADD OBJECT cmdShow as CommandButton WITH ;
		Top = 216, ;
		Left = ThisForm.Width - 126, ;
		Height = 36, ;
		BackColor = RGB(0, 240, 240), ;
		Caption = "Show Selection"
		
		PROCEDURE cmdShow.Click()
		
			FOR i = 1 TO ALEN(ThisForm.aSelection)
				IF VARTYPE(ThisForm.aSelection[i]) = "C"
					WAIT WINDOW + ThisForm.aSelection[i] TIMEOUT 3

				ENDIF 
			ENDFOR
			
			Thisform.ResetArray() 

			
		ENDPROC
		
	PROCEDURE ResetArray() 
		DIMENSION ThisForm.aSelection[1]
		ThisForm.aSelection[1] = .F.
		ThisForm.lstList.Requery

	ENDPROC 

	PROCEDURE Load()
		CREATE CURSOR csrOne (cItemCode C(5), cItemName C(15), iQuantity I , cCountry C(2))
		
		INSERT INTO csrOne VALUES ("AAAAA", "GMG X1", 5, "US")
		INSERT INTO csrOne VALUES ("AAAAB", "GMG Y2", 4, "US")
		INSERT INTO csrOne VALUES ("AAAAC", "Renault Zoe", 12, "FR")
		INSERT INTO csrOne VALUES ("AAAAD", "Renault Twizzy", 3, "FR")
		INSERT INTO csrOne VALUES ("AAAAE", "Renault Twingo", 17, "FR")
		INSERT INTO csrOne VALUES ("AAAAF", "BMW 3", 15, "DE")
		INSERT INTO csrOne VALUES ("AAAAG", "BMW 5", 25, "DE")
		INSERT INTO csrOne VALUES ("AAAAH", "VW Golf", 51, "DE")
		INSERT INTO csrOne VALUES ("AAAAI", "Audi A7", 14, "DE")
		INSERT INTO csrOne VALUES ("AAAAJ", "Austin", 11, "GB")
		INSERT INTO csrOne VALUES ("AAAAK", "Triumph", 9, "GB")
		INSERT INTO csrOne VALUES ("AAAAL", "Morgan", 9, "GB")
		INSERT INTO csrOne VALUES ("AAAAM", "Alfa Romeo", 6, "IT")
		INSERT INTO csrOne VALUES ("AAAAN", "Fiat", 5, "IT")
		INSERT INTO csrOne VALUES ("AAAAO", "Maserati", 2, "IT")
		INSERT INTO csrOne VALUES ("AAAAP", "Peugeot 403", 2, "FR")
			
	PROCEDURE Destroy()
		ThisForm.Release
		CLEAR Events
		
	ENDPROC
ENDDEFINE

hth

MarK

Niki_S · Aug 10, 2022

When I'm going to get my data into the listbox I did this code in my 'cboFactory' click event.

_Fact = thisform.cboFactory.value

stra = "SELECT cDesignation as Designation,CAST(0 as bit) as cSelect FROM HRIS.dbo.vHRIS_Deletion WHERE cFact=?_Fact AND cDesignation <> 'NULL' GROUP BY cDesignation ORDER BY cDesignation "
SQLEXEC(hndOps,stra,'_Des')

thisform.cboFactory.value = _Des.Designation

But I can't get records into my listbox.
How can I get my data?

Thank you

Chris Miller · Aug 10, 2022

Why are you setting cboFactory.value to a designation? The value of a combobox has to be what the controlsource of it could be, and those are factories, not designation, aren't they? Judging from you taking the cboFactory.value as _Fact and comparing it to the cFact field of an MSSQL table.

If The whole result _Des contains the items you want to display in a listbox, then a) there is no need to create a field cSelect (that was for usage of the cursor in a grid to have a field for an additional checkbox column, you don't need and don't have that in a combobox nor in a listbox) and you need to set the Rowsource of a listbox, not the value, to set its items.

And that would either just be Rowsource="_Del" when using RowSourceType=5 (alias). And when you use RowSourceType=6, RowSource="_Del.Designation" does the job. Both ways will display Designations as listbox items, but one thing is sure, you don't and can't set this list by value. A value is always a single value, the currently selected one. It's a read-out property, not one you actually set. There are use cases for setting it, but never ever to set a list.

Any multiple items/rows/records displaying control like combobox, listbox or grid has a Rowsource or Recordsource property to specify the origin of the list of records or items to show and then a RowsourceType or RecordsourceType that specifies what this list is exactly. Because it may not come from a workarea, but something else, like an array. The items of a combobox or listbox also can be added one by one with their Additem method. In which case the RowsourceType should be set to none, you're then using the controls internal listitem array.

Chriss

Niki_S · Aug 10, 2022

I changed my one as below.

_Fact = thisform.cboFactory.value

stra = "SELECT cDesignation as Designation FROM HRIS.dbo.vHRIS_Deletion WHERE cFact=?_Fact AND cDesignation <> 'NULL' GROUP BY cDesignation ORDER BY cDesignation "
SQLEXEC(hndOps,stra,'_Des')

thisform.List1.RowSourceType = 2
thisform.List1.RowSource =('_Des')

Now the listbox is ok.
Now I want to insert selected items into a cursor. I tried to understand the above code but I couldn't. Is there have any other way to insert selected items into a cursor?

Thank you

Chris Miller · Aug 10, 2022

And just to mention something that strikes me: You easily confuse yourself when using names with underscore prefix both for single values variables and aliases of workareas.

Notice exactly what your code does: _Fact is set to the combobox value, which is one field of the combobox itms list. As we don't know the rowsource, boundto, boundcolumn etc. properties of it, I would assume it's the factory name which also is displayed by that cboFactory. So _Fact is a single value variable. _Des is a workarea, and while _Des.Description isn't the whole workarea but just one field - Designation - likely also a string, that doesn't make it compatible to be used as cboFactory.value at all. How should it?

In natural language, I'd describe what your code does up to the SQLExec as translating a factory to a designation or looking up a designation that corresponds to a factory name. Not knowing the meaning of the HRIS.dbo.vHRIS_Deletion table at all, I have no clue what that actually means, but some things are clear independent of that: a) _Des is not a single value, even if there only is one record in _Des, it's the name of a workarea. _Des.Designation is only one designation the first one, in the list of result records. Could be the only one. But it doesn't seem likely to me that both the cFact and cDesignation fields of dbo.vHRIS_Deletion have the same meaning of a factory name like a factory and a designated factory.

Only in that case, and pardon me, if that is the case and my earlier interpretation is wrong, that could be used to switch the cboFactory from the picked factory to instead pick the designation related to it. But would you actually do that? Use the control the user has to pick the input of the lookup and set the result back to the input? Wouldn't that cause endless recursion of now looking up the next designation by using the output as input again?

It even more so spaces me why you act on cboFactory after the SQLEXEC, when your goal is to display the _DES result in a listbox. cboFactory by it's name and the naming conventions the VFP help suggests is a combobox, not a listbox.

Chriss

Chris Miller · Aug 10, 2022

Code:

thisform.List1.RowSourceType = 2
thisform.List1.RowSource =('_Des')

Good, we're one step further. You don't need the brackets here, the name is _Des, the Rowsource is a string type property, so it can be set to '_Des' or "_Des".

Next, why not give the listbox an equally good name as you gave to the cboFactory (or your predecessor)? For example name it lstDesginations. My further sample code will assume that's the case already.

To enable multiple selections the lstDesignations.Multiselect property has to be .T., you don't need a Select field, nor can you make the listbox use a logical flag field to set it to .t. for selected items. The listbox has a Selected array property. You read out the multiple selections of a listbox, once the user is done with it, by going through that listbox Selected array. Listbox.Selected(1) is .t. if the first listitem is selected, listbox.Selected(2) is true, if the second item is picked, etc.

Multiple elements of that array can be .t. in multiselect mode of the listbox.

So to read out all selected items you do a loop:

Code:

Local i
Create cursor selecteditems (cItem C(254))
For i = 1 to alen(thisform.lstDesignations.Selected)
   If thisform.lstDesignations.Selected(i)
      insert into selecteditems values (thisform.lstDesignations.listitem(i))
   Endif
Endfor

There are other ways to do this, but this is the straight forward way to use the Selected array feature of the listbox.

Chriss

Niki_S · Aug 10, 2022

When I using this ,

Local i
Create cursor selecteditems (cItem C(254))
For i = 1 to alen(thisform.lstDesignations.Selected)
If thisform.lstDesignations.Selected(i)
insert into selecteditems value (thisform.lstDesignations.listitem(i))
Endif
Endfor

it says that 'SELECTED is not an array'. I did this in my lstDesignations click event.
What should I do for the next?

Thank you

Chris Miller · Aug 10, 2022

My gosh, it actually is almost the same, but what this means is you can't use ALEN(), well, then use the reccount of _Del or better the listbox.listcount:

Code:

Local i
Create cursor selecteditems (cItem C(254))
For i = 1 to thisform.lstDesignations.ListCount
   If thisform.lstDesignations.Selected(i)
      insert into selecteditems values (thisform.lstDesignations.listitem(i))
   Endif
Endfor

Chriss

Niki_S · Aug 10, 2022

Thank You so much Chriss.....
Now it's working. [bigsmile]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to create a multiselect combo box? 1

Niki_S

Programmer

Mike Gagnon

Programmer

mjcmkrsr

Technical User

mjcmkrsr

Technical User

Niki_S

Programmer

Chris Miller

Programmer

Niki_S

Programmer

Chris Miller

Programmer

Chris Miller

Programmer

Niki_S

Programmer

Chris Miller

Programmer

Niki_S

Programmer

Similar threads

Part and Inventory Search

Sponsor