×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multiple Combobox Behaviors

Multiple Combobox Behaviors

Multiple Combobox Behaviors

(OP)
Hi,

Using a combobox control with a rowsourcetype = 3 and a sql statement into cursor holds the list values to be selected. With the controlsource representing the destination table.field, I need for it to:

1. accept a new value from the user and if value is in the list, position it to the point that matches. IE. The underlying table has a value of "ABC Autos" and the user types "ABC" they are jumped to the opened list with "ABC" Autos" highlighted. So, it must be type=0 (combo and not list)

2. If there is no match, then position the selection to the next higher value as displaying nothing doesn't give the user a clue and doesn't help the user find the correct one. IE. The list maybe "A B C Autos" Positioning the selector within the list near where the entered text should be located, whether found or not, is important as it helps the user find the correct one faster.

3. I'm creating the cursor in the dropdown code. What would that statement look like that will land the user on the next item in the list if the entered text was not found?

4. Do I have to code and test against both the combo's built-in dropdown arrow and built-in textbox? I think yes... Two ways, one by not opening the listbox portion and another staying in the textbox portion only? Appears as too much juggling to do here as a user clicks in textbox and enters text and presses enter or clicks the downarrow, where and when to validate, and how to esc which reverts back to the "before selected value"

So far, I have not yet achieved the expected results and have not had a chance to tryout some theories as discussed in Tamar's excellent article "Combos and Lists-The Forgotten Controls" and has helped in the understanding.

So for now, I have no "what isn't working" question for you experts to answer. Instead, I'm asking for ideas on how to achieve the above goals. Most articles say it cannot be done natively and need a full custom control. What methods should be used? I found Tamar's article while searching for the firing order of the combobox and really never found a conclusive list, however Tamara partially addressed it. I don't remember the details now and will have to revisit.

How is everyone doing this or ideas on how to do this?

Thanks, Stanley

RE: Multiple Combobox Behaviors

Hi,

I favor a simple solution. In the combobox you offer an additional choice "Record not Found". If the user selects it he/she may add a new record. Sketch of code below.

CODE -->

PUBLIC oForm1

oform1=NEWOBJECT("form1")
oForm1.Visible = .T.
oform1.Show

READ Events

CLOSE ALL
CLEAR ALL 

RETURN

**********

DEFINE CLASS form1 AS form
Height = 240
Width = 360
Autocenter = .T.
Borderstyle = 2
MinButton = .F.
MaxButton = .F.
Themes = .F.
ShowTips = .T.

DIMENSION laGuests[1]
	
	ADD OBJECT lblLName as Label WITH ;
		Top = 24, ;
		Left = 24, ;
		Caption = "Lastname"
	
	ADD OBJECT lblFName as Label WITH ;
		Top = 24, ;
		Left = 24 + 90 + 12, ;
		Caption = "Firstname"
	
	ADD OBJECT lblGName as Label WITH ;
		Top = 24, ;
		Left = 126 + 90 + 12, ;
		Caption = "Guest"
	
	ADD OBJECT lblRNumber as Label WITH ;
		Top = 24, ;
		Left = 228 + 60 + 12, ;
		Caption = "Room"
	

	ADD OBJECT txtLName as TextBox WITH ;
		Top = 48, ;
		Left = 24, ;
		Width = 90, ;
		ControlSource = "Guests.LastName", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
		
	ADD OBJECT txtFName as TextBox WITH ;
		Top = 48, ;
		Left = 24 + 90 + 12, ;
		Width = 90, ;
		ControlSource = "Guests.FirstName", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)

	ADD OBJECT txtGNumber as TextBox WITH ;
		Top = 48, ;
		Left = 126 + 90 + 12, ;
		Width = 48, ;
		ControlSource = "Guests.G_No", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
			
	ADD OBJECT txtRNumber as TextBox WITH ;
		Top = 48, ;
		Left = 126 + 90 + 12 + 48 + 12 , ;
		Width = 48, ;
		ControlSource = "Guests.RoomNumber", ;
		Alignment = 1, ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
		

	ADD OBJECT cboChooseGuest as ComboBox WITH ;
		Style = 2, ;
		Top = 90, ;
		Left = 24, ;
		Width = 312, ;
		RowSourceType = 5, ;
		RowSource = "ThisForm.laGuests", ;
		ColumnCount = 2, ;
		ColumnWidths = "270, 0", ;
		IncrementalSearch = .T.
		
		PROCEDURE cboChooseGuest.GotFocus()
		
*!*			You don't want to have too many items in the COMBOBOX hence you might want to filter with WHERE 
		
			Select TRANSFORM(G_No) +" - "+ ALLTRIM(LastName) +" "+ ALLTRIM(FirstName) ;
				FROM Guests ;
				WHERE .T. ;
				INTO ARRAY ThisForm.laGuests
				
			DIMENSION ThisForm.laGuests(ALEN[ThisForm.laGuests, 1] + 1)
			AINS(ThisForm.laGuests, 1)
			ThisForm.laGuests[1] = "0 - Record not found"
			
			This.Requery()

		ENDPROC 
		
		PROCEDURE cboChooseGuest.Click()
			LOCAL liGuestNumber as Integer
			
			liGuestNumber = VAL(SUBSTR(This.List[This.Listindex, 1], AT("-", This.List[This.Listindex, 1], 1) - 2))
			

			IF liGuestNumber = 0
				IF MESSAGEBOX("Do you want to add a record?", 4 + 32, "Add Record") = 6
					WAIT WINDOW + "Record not found. Your code to add a new record" TIMEOUT 3

				ENDIF 
			ELSE 
*!*			You may SEEK or INDEXSEEK in a large and indexed table - in a small table LOCATE is fine
	
				LOCATE FOR Guests.G_No = liGuestNumber 
			
				Thisform.Refresh()
			ENDIF 
		ENDPROC 
		

	PROCEDURE Load
		CREATE CURSOR Guests (LastName C(42), FirstName C(42), G_No I, RoomNumber C(5))
		
		INSERT INTO Guests values("Meyer", "Corinne", 1, "101")
		INSERT INTO Guests values("Bogart", "Humphrey", 2, "143")
		INSERT INTO Guests values("Hepburn", "Katherine", 3, "97")
		INSERT INTO Guests values("Clooney", "George", 4, "433")
		INSERT INTO Guests values("Miller", "Chris", 5, "132")
		INSERT INTO Guests values("Lewis", "Mike", 6, "177")
		INSERT INTO Guests values("Smith", "Jenny", 7, "187")
		
		LOCATE 

	ENDPROC 

	PROCEDURE Destroy
		ThisForm.Release()
		CLEAR EVENTS
		

	ENDPROC 

ENDDEFINE

********** 

hth

MarK

RE: Multiple Combobox Behaviors

The simpler solution here is to use the autocomplete feature of a textbox and not a combobox.

Make your own experience. I can only warn you users don't care much, you will end up with new values, even though existing ones are meant, as users make typos resulting in a new value, without looking and verifying.

As typos are very frequently happening, it only makes sense to work in combo mode to use the textbox value for SEEKing with SET NEAR ON (well, at least one way to do it) and never take it as a new record, unless the user uses ENTER/RETURN key to actually enter it. That should likely trigger a form to pop up to complete the new record, as it also needs a new first name and likely even more person data.

I'll let you ponder this before I start coding.

Chriss

RE: Multiple Combobox Behaviors

(OP)
Hi Chris,

Quote (Chris)


I can only warn you users don't care much, you will end up with new values, even though existing ones are meant, as users make typos resulting in a new value, without looking and verifying.
Their previous solution offers this behavior and has never created any issues for them. The behavior is purely lookup and not for creating new records. The list needs to contain only valid records from the lookup table and shown in the combo list. Here is what they do now,

In a textbox, user types the value to look for and press F6 (lookup key) which brings up a list of a specified field values with the cursor sitting or as close as possibe to the search value. User selects one and the textbox will be filled out completely. If the value is not found the cursor will be placed on the next higher or lower value (depends on what was defined in the browse lookup. The benefit is user can see what is above and below it, if not found, or land on it if a match is found.

Quote (Chris)


As typos are very frequently happening, it only makes sense to work in combo mode to use the textbox value for SEEKing with SET NEAR ON, and never take it as a new record, unless the user uses ENTER/RETURN key to actually enter it. That should likely trigger a form to pop up to complete the new record, as it also needs a new first name and likely even more person data.
I agree...

Thanks,
Stanley

RE: Multiple Combobox Behaviors

(OP)
Hi mjcmkrsr,

I just ran your code and I see where it can be useful. For the project at hand, I need a more compact way with a behavior to incrementaly search for field values in a table and place the selector as near as possible to the SearchFor value as typed into the combo.

Thanks,
Stanley

RE: Multiple Combobox Behaviors

(OP)
Chris,

The autocomplete feature is not what I'm after for all the reasons you mention as it contains everything the user types in, valid or not.

Thanks,
Stanley

RE: Multiple Combobox Behaviors

Quote:

The autocomplete feature is not what I'm after for all the reasons you mention as it contains everything the user types in, valid or not.

That's true. But don't write it off too quickly. By setting the AutoComplete property to 4, you can get a lot of control over the order in which the entries are displayed, which means that you can arrange for invalid entries to be so low down the list that they don't actually appear. Admittedly, you need to go to the trouble of updating the AutoComplete table each time the user enters some text, and it's not always obvious how to do that.

The other point is that users are now very much accustomed to this type of auto complete. They see it in search engines, in the address bar in most browsers, and many other places. And these generally include invalid entries. There is something to be said for giving your own users the same behaviour.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Multiple Combobox Behaviors

Hi Stanley

Quote:


with a behavior to incrementaly search

Well, INCREMENTAL search IS possible. Just key in 2 or 6 and you see that the corresponding record is highlighted. If you want to search by alphabetic order just adapt the code to your needs.

But since I'm a nice guy smile and since it's Easter WE, I adapted the code - it allows to do INCREMENTAL search by alphabetic order

CODE -->

LOCAL loForm

loForm = NEWOBJECT("form1")
loForm.Visible = .T.
loform.Show

READ Events

CLOSE ALL
CLEAR ALL 

RETURN

**********

DEFINE CLASS form1 AS form
Height = 240
Width = 360
Autocenter = .T.
Borderstyle = 2
MinButton = .F.
MaxButton = .F.
Themes = .F.
ShowTips = .T.
Caption = "Famous Guests"

DIMENSION laGuests[1, 1]
	
	ADD OBJECT lblLName as Label WITH ;
		Top = 24, ;
		Left = 24, ;
		Caption = "Lastname"
	
	ADD OBJECT lblFName as Label WITH ;
		Top = 24, ;
		Left = 24 + 90 + 12, ;
		Caption = "Firstname"
	
	ADD OBJECT lblGName as Label WITH ;
		Top = 24, ;
		Left = 126 + 90 + 12, ;
		Caption = "Guest"
	
	ADD OBJECT lblRNumber as Label WITH ;
		Top = 24, ;
		Left = 228 + 60 + 12, ;
		Caption = "Room"
	
	ADD OBJECT txtLName as TextBox WITH ;
		Top = 48, ;
		Left = 24, ;
		Width = 90, ;
		ControlSource = "Guests.LastName", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
		
	ADD OBJECT txtFName as TextBox WITH ;
		Top = 48, ;
		Left = 24 + 90 + 12, ;
		Width = 90, ;
		ControlSource = "Guests.FirstName", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)

	ADD OBJECT txtGNumber as TextBox WITH ;
		Top = 48, ;
		Left = 126 + 90 + 12, ;
		Width = 48, ;
		ControlSource = "Guests.G_No", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
			
	ADD OBJECT txtRNumber as TextBox WITH ;
		Top = 48, ;
		Left = 126 + 90 + 12 + 48 + 12 , ;
		Width = 48, ;
		ControlSource = "Guests.RoomNumber", ;
		Alignment = 1, ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
		
	ADD OBJECT cboChooseGuest as ComboBox WITH ;
		Style = 2, ;
		Top = 90, ;
		Left = 24, ;
		Width = 312, ;
		RowSourceType = 5, ;
		RowSource = "ThisForm.laGuests", ;
		ColumnCount = 2, ;
		ColumnWidths = "270, 0", ;
		IncrementalSearch = .T.
		
		PROCEDURE cboChooseGuest.GotFocus()
		
*!*			You don't want to have too many items in the COMBOBOX hence you might want to filter with WHERE 
		
			Select ALLTRIM(LastName) +" "+ ALLTRIM(FirstName), G_No ;
				FROM Guests ;
				WHERE .T. ;
				ORDER BY 1 ;
				INTO ARRAY ThisForm.laGuests
				
			DIMENSION ThisForm.laGuests[ALEN(ThisForm.laGuests, 1) + 1, 2]

			WITH ThisForm
				.laGuests[ALEN(ThisForm.laGuests, 1), 1] = "*** Record not found ***"
				.laGuests[ALEN(ThisForm.laGuests, 1), 2] = 99
			ENDWITH 

			This.Requery()

		ENDPROC 
		
		PROCEDURE cboChooseGuest.Click()
			LOCAL liGuestNumber as Integer
			
			liGuestNumber = VAL(This.List[This.Listindex, 2])

			IF liGuestNumber = 99
				IF MESSAGEBOX("Do you want to add a record?", 4 + 32, "Add Record") = 6
					WAIT WINDOW + "Record not found. Your code to add a new record" TIMEOUT 3

				ENDIF 
			ELSE 
			
*!*			You may SEEK or INDEXSEEK in a large and indexed table - in a small table LOCATE is fine
	
				LOCATE FOR Guests.G_No = liGuestNumber 
			
				Thisform.Refresh()
				
			ENDIF 
		ENDPROC 
		
	PROCEDURE Load
		CREATE CURSOR Guests (LastName C(42), FirstName C(42), G_No I, RoomNumber C(5))
		
		INSERT INTO Guests values("Meyer", "Corinne", 1, "101")
		INSERT INTO Guests values("Bogart", "Humphrey", 2, "143")
		INSERT INTO Guests values("Hepburn", "Katherine", 3, "97")
		INSERT INTO Guests values("Clooney", "George", 4, "433")
		INSERT INTO Guests values("Miller", "Chris", 5, "132")
		INSERT INTO Guests values("Lewis", "Mike", 6, "177")
		INSERT INTO Guests values("Smith", "Jenny", 7, "187")
		
		LOCATE 

	ENDPROC 

	PROCEDURE Destroy
		ThisForm.Release()
		CLEAR EVENTS

	ENDPROC 
ENDDEFINE

********** 

hth

MarK

RE: Multiple Combobox Behaviors

Stanlyn,

seems you overlook everything else I said, too. But as Mike said you can make autocomplete work in several ways. You could avoid wrong or half entries to be stored in Valid, I think, by verifying the textbox value is either a valid entry and if not only accept it if the last key was ENTER (the user stating "I want this as new entry") and not TAB (the user stating: I didn't find what I looked for and want to tab away only"). It's all in what I said already.

In fact, a textbox with autocomplete = 1 (alphabetical) and this Valid event works nicely:

CODE

If InList(Lastkey(),9,27)
   This.Value = ''
EndIf 

Now you can TAB away which empties the box and doesn't enter the value or use ESC to empty the textbox box and stay to try again. And, just by the way, if you enter a first letter and don't find anything, you can use DEL and then get all entries listed. I think it's still not what you want as typing say "B" in a list without any name starting with B will not display the next name in alphabetical order, say with D but I think that's not helpful anyway. Indeed that nothing is displayed as nothing starts with B indicates to the user that nobody with B exists and if really someone with B was meant, he has to enter the name fully, which is nicer than offering any other name. If the "B" was a typo the user can just simply DEL or ESC and start over. It all works very nicely and as Mike said it's a feature people got used to by the internet anyway.

Chriss

RE: Multiple Combobox Behaviors

...

and a little demo how AutoComplete textboxes work

CODE -->

*!*	If you don't specify where to save the AutoComp.* files, you find them in the HOME(7) folder
*!*	The command to open it is
*!*	USE HOME(7) + "autocomp" ORDER data
*!*	***********

LOCAL goForm

goForm = CREATEOBJECT("frmForm")
goForm.Visible = .T.
goForm.Show()

READ EVENTS

CLOSE ALL
CLEAR ALL 

RETURN

**********

DEFINE CLASS frmForm as Form

Caption = "Autocomplete TextBoxes"
AutoCenter = .T.
BorderStyle = 3
Width = 570
MinWidth = 570
Height = 300
MinHeight = 300
MinButton = .F.
MaxButton = .F.
Themes = .F.

	ADD OBJECT lblName as Label WITH Left = 12, Top = 12, Caption = "Name"
	ADD OBJECT lblCity as Label WITH Left = 108, Top = 12, Caption = "City"

	ADD OBJECT txtName as Textbox WITH Top = 36, Left = 12, Width = 90, AutoComplete = 1, AutoCompSource = "cAllNames"
		
		PROCEDURE txtName.Valid
			IF LEN(ALLTRIM(This.Value)) < 5
				WAIT WINDOW + ALLTRIM(This.Value) + " is too short" TIMEOUT 2
				RETURN 0
			ENDIF 

		ENDPROC 
		
	ADD OBJECT txtCity as Textbox WITH Top = 36, Left = 108, Width = 90, AutoComplete = 1, AutoCompSource = "cAllCities"

		PROCEDURE txtCity.Valid
			IF LEN(ALLTRIM(This.Value)) < 5
				WAIT WINDOW + ALLTRIM(This.Value) + " is too short" TIMEOUT 2
				RETURN 0
			ENDIF 

		ENDPROC 
	
	ADD OBJECT cmdSave as Commandbutton WITH Top = 12, Left = 204, Height = 48, Caption = "Save", BackColor = RGB(0, 250, 250)
	
		PROCEDURE cmdSave.Click()
			IF NOT (EMPTY(ThisForm.txtName.Value) OR EMPTY(ThisForm.txtCity.Value))
				INSERT INTO csrBirthdays VALUES (ThisForm.txtName.Value, ThisForm.txtCity.Value, DATETIME())
	
				ThisForm.Refresh()
			ELSE
				= MESSAGEBOX("No record added!", 64, "Adding Record", 2000)

			ENDIF 
		ENDPROC 
			
	ADD OBJECT grdNamesCities AS Grid WITH ;
		RecordSource = "csrBirthdays", ;
		ColumnCount = -1 , ;
		Left = 12, ;
		Top = 72, ;
		Width = ThisForm.Width - 24, ;
		Height = ThisForm.Height - 84, ;
		DeleteMark = .F. , ;
		Enabled = .T. , ;
		Anchor = 15
 
		PROCEDURE grdNamesCities.Init
			 WITH This.Column1
			  .Width = 60			
			  .Header1.Caption = "Name"
			 ENDWITH

			 WITH This.Column2
			  .Width = 90			
			  .Header1.Caption = "City"
			 ENDWITH

			 WITH This.Column3
			  .Width = 150			
			  .Header1.Caption = "Date - Time"
			 ENDWITH
		 ENDPROC 
		 
	PROCEDURE Load()
		CREATE CURSOR csrBirthdays (cName C(24), cCity C(24), tDate T)
		
	ENDPROC 
      
	PROCEDURE Destroy()
		CLOSE ALL  
		ThisForm.Release()
		CLEAR EVENTS
	
	ENDPROC 
ENDDEFINE 
********** 

hth

MarK

RE: Multiple Combobox Behaviors

Thanks, MarK!

Now, Stanley, you can combine Mark's valid event code with mine, and adjust or refine it. Prepare data like a list of names and cities or a dictionary table to have just the normal autocomplete of text. You can weigh things on the number of usages, and you can introduce a better search feature by using something like Soundex, but in the end, almost all words will be within selection if you just enter two or three letters and indeed the just usual combobox incremental search is almost as efficient without any programming, as it's really not a good service to go to the net best fit if it's not at all what the user searches, no matter if what he typed is going off because it's a typo or if it's not a typo and no name starts that way.

I mean, give me an example of a list of values you have and a user entry that's not getting a match of all typed-in letters and still leads you to the final goal. With a "near" search you're allowing a typo, but still, only go to the nearest entry from that typo value. If I consider a dictionary as the autocomplete table typing in "Mile" by missing the second L of Miller I just end up there, at the word mile. And there are still miles away from a miller. So, the best I can do as a user is to DL back to "Mil" and correct my typo to get to Millenium and some other words near Miller.

It's still that one of the few who did the groundwork of autocomplete, Charles Babbage, can be glad to never experience his name being autocorrected to Cabbage all the time.

In short: searching nearest value is no big advance from sticking to a completely unintelligent incremental search. I bet you that's why the Fox team never really went into enhancing it with autocorrection features like seeking with NEAR ON. They still implemented Soundex and SET NEAR, true, but in controls, you're still almost always better off with a dumbed-down incremental search.

Again, convince me. Show me a case with a helpful near matching. The crux is not only that a typo does not make you approach the target far better than the start of the word/name before the typo, a typo is a letter on a keyboard one key away, but usually far off in the alphabetical order. i.e. typos of V are not U or W, they are C,F, or B, so taking them for the near search doesn't bring you near where you actually want to be, no matter if you do an exact search and find nothing or near search and either stick to the best match you already had or only go a bit off of it, but nowhere near the actual target. From the typo onwards, even a near search doesn't get you to the goal. It's only a bit better in that it doesn't bring you to the end of the list. I would even suggest that an exact search going to EOF is something you can use to give the user a feedback like a beep to indicate there's nothing starting with what's typed in, so the user can DEL (or backspace) and continue searching or complete his entry and finally ENTER it.

Chriss

RE: Multiple Combobox Behaviors

Let's do it the other way around. I take your notice that it's unhelpful of incremental search to go to EOF if nothing is found and take it as a task to let a typo or mismatch stick to the best match you had so far instead of ending at EOF. That can be done by memorizing the best match as last match and if getting to EOF you go back to the last match.

That's indeed a bit simpler with a combobox than with autocomplete, let me look into it and come back with code examples.

Chriss

RE: Multiple Combobox Behaviors

First try to let autocomplete stick to the best match you already had fails. In interactivechange I don't detect the autocomp.dbf VFP uses (by default) to be open. I assume it is used by VFP in a "secret" datasession id 0, and not available to you. So I don't immediately see a way to make automcomplete stick to the best match you had without reverting the input value to the one before, I can't even detect what autocomplete list and offers for picking in a dropdown, there's no access to it.

So let me start experimenting with a combobox in actual combo mode to see what can be done with either doing an own incremental search or improving the automatic incremental search within the interactivechange event, for example, to not go to EOF and instead at least stick to what your best match was from previous incremental search states. Don't count with another post today already.

Chriss

RE: Multiple Combobox Behaviors

(OP)

Quote (Mike)


But don't write it off too quickly. By setting the AutoComplete property to 4,
I was unaware that autocomplete has options. I'll work on this later today as there is a lot testing needs done. I'll use mjcmkrsr code for the test bed and try to incorporate what Chris said earlier.

Chris, what would your controlsource look like? And what would the recordsource look like using type 3, particularly the part implementing "set near"? It is my understanding a sql select statement into cursor does not respect "set near" issued beforehand. Is this correct?

Thanks,
Stanley

RE: Multiple Combobox Behaviors

Stanley,

I don't know where your last question stems from. SET NEAR has an effect on SEEK command, SEEK() and INDEXSEEK() functions, but not on SQL. You would want the incremental search to be fast, so using an index on the combobox items and a SEEK, won't you? And you waon't use an SQL as your rowsource. Why are you finding this type of rowsource so useful? You want your combobox to have a static set of items to pick from and the search mechanism to move closest as possible to the target item, but not want to requerry items while you type. That's may be what you think you want, but it's really not practical. You navigate in the items, you don't filter the items or requery with each interactivechange.

Chriss

RE: Multiple Combobox Behaviors

(OP)
Lets start over as we are in two different worlds. The first thing I noticed with mjcmkrsr solution was no combobox and a lot of fields to gather the data for the test.

This thread was started after mentioning it in another thread where I am needing this functionality in a combobox that is in a grid cell. Since I can only show one control in the cell, I'm stuck with the combobox which should be sufficient, if I can ever get it coded right. With the combobox, I have controlsource, rowsource, rowsourcetype, value and DisplayValue. I also don't want to bring up yet another window or form to facilitate this as it is not needed, hopefully.

First, can we agree that the combobox control is needed if shown in a grid cell?

I've prepared a short video showing the user experience that I'm trying to duplicate with a combobox in a grid cell. There is no grid in the video, but video has a textbox for entering a value (combobox has an integrated textbox control that can be used to enter value). To do a lookup in the video, user presses F6 while in the textbox. In VFP, user will click the control's down arrow button which opens the list (is same behavior as F6) The url is: https:\\www.stanlyn.com\public\combobox.mp4
The link doesn't seem to work from here, but does when pasted in browser.

I am also aware this is a text-based vs gui-based UI, however the experience can be the same, whereas the user inputs text into the textbox component of the combobox and clicking the combobox's down arrow can equal the F6 key as shown in the video. The list that is presented is of the actual data in the table. The functionality shown in the video is useful in many other senerios.

Where is the best place to trap for ESC being pressed when the combobox list is shown? By default it selects the highlighted row, regardless of navigation mode. In my case I need to test for ESC and if found, restore original value.

Until now, I have been able to do everything needed with a rowsourcetype of 1, 2, and 3. I've never used any of the others. Also, I have never used a builder to build a combobox, until today. School is in session...

So Chris, how do you implement a non-sql or non-alias way that uses the seek as I have no ideal on how to begin in its setup and using or not using the 5-data related values (controlsource, rowsource, rowsourcetype, value and DisplayValue)

I'm creating a test form based on Northwind data that can be shared by saving it as a class...

Thanks,
Stanley

RE: Multiple Combobox Behaviors

Quote (stanlyn)

First, can we agree that the combobox control is needed if shown in a grid cell?
No, an autocomplete textbox is an alternative, but I'll try with a combobox in combo mode.

Quote (stanlyn)

Where is the best place to trap for ESC
The events Keypress and/or Valid, likely both. What's your problem with figuring that out, Stanley? It's a standard to check Lastkey() in valid and alloww leavnig the control when that's ESC (Lastkey()=27), take a look into hackers guide, for example.

Quote:

So Chris, how do you implement a non-sql or non-alias
I never said you you'd not use an alias. You put restrictions in my mouth I never told you about. I just said I never would use a SQL query as rowsourcetype. Because you don't want to repeatedly do SQL potentially in every Interactivechange that does a requery of the cobobox items. What you should aim for is letting the interactivechange navigate the list of items you got once initially. Your video also shows that the list coming up is full and just positioned on the best match. And as far as I understand your demand that item list is a full table, so alias as rowsourcetype is the case here. Same with an autocomplete textbox, you'd specify an autocomplete table with fully populated data column. Let's just put this aside a bit for now. I find it already is a solution, but you haven't even looked into it yet.

Chriss

RE: Multiple Combobox Behaviors

You're avoiding to answer questions. Questions you will have to address whether you want or not.

In your video you show the data comes from a multiple field table. If you make the Firstname column of a combobox to pick from and allow entering a new firstname, well, what will the rest of the data empty. What about that. If actually a new Firstname is entered that isn't in the list to pick from, then you have other fields, most of them will be mandatory and not optional to know, won't they? So what about that, Stanlyn? Even if you have no field rules and a mostly empty record is allowed you're introducing sparse data that later needs to be cared for, if you allow new records to be entered by the combobox. If the grid list is not that table but the person column is just about maintaining a foreign key of a person, then you also don't have the other fields of the person record in the grid to enter that. And if you even would have the person list in the grid, then you would have the firstname column in it without a combobox. The navigation to a person then is best done with textboxes under or over the grid.

That question makes it questionable to have such a combobox for locating a person and adding a new one, which indeed correctly led MarK to offer what he did.

Chriss

RE: Multiple Combobox Behaviors

Quote (myself)

Show me a case with a helpful near matching.

you indirectly answered how you'd make use of it with your video https://www.stanlyn.com/public/combobox.mp4 (The internet uses slashes, not backslashes).
There's a case you enter Rickeyzzzz at about 2 minutes and the result of that is that the navigation is at Rickie directly under Rickey. The only real advantage about this near search is that you're not nowhere or at EOF, but surely not that you're at one record below the last match.

Maybe I should expand, that I expected you to want that at each time the user enters a letter you want to navigate and already have the list displayed. Not needing that turns up another need, a key the user uses to indicate he now wants to see the list positioned near what he entered so far into the textbox of the combobox. That's F6 in your demo. We could make use of that, fine.

But overall I stick to what I said last, the feature is mainly only helpful in that it doesn't bring you to EOF and the better fit in case you enter Rickeyzzzzz is still Rickey, not Rickie. Rickeyzzzz starts with Rickey, that's 6 matching letters, while Rickie only has the first 4 letters matching. And that's generally the case, if you think about it.

Chriss

RE: Multiple Combobox Behaviors

It turns out in expermineting with toe combo mode that the textbox portion is not just free from influence of the combobox items list.
When you set a controlsource the displaytext of the textbox will be the first column of the items list, that is the first column of the rowsource of the combobox.
So you're not starting empty in that textbox portion and can use it for input of the partial name you want to incrementally search for in the items list. That makes the combobox not usable for our idea. No matter how much we differ about details. The textbox is not independent from the items list. And that dependency is strong within a grid, as a gridcolumn always has a controlsource the combobox also inherits. Even with BoundTo = .F. you don't escape that dependency and influence, you don't have the textbox portion for your own usage only, anyway.

So I get back to the autocomplete textbox. There's an idea worth following up, using our own specific automcplete table you have the best ways to make use of the input for navigation within the records of thee autocomplete table.

Chriss

RE: Multiple Combobox Behaviors

One question, Stanlyn,

What is in the grid column that's having the combobox? Is it a foreign key field you eventually want to maintain by the combobox in that grid column, or is it a char field, which you want to set to the final displayvalue of the combobox?

Chriss

RE: Multiple Combobox Behaviors

(OP)
Chris,

Quote (Chris)


is it a char field, which you want to set to the final displayvalue of the combobox?

Yes, as the grid column has 2 objects, one is the header and the other is a combobox where its displayvalue is the underlying table. I removed the default textbox for that cell.

The grid contains customer records from the customer table and the combobox is bound to the zipcode field from the customer table. The lookup table is "zipcode"

Hope that explains it better.

Thanks,
Stanley

RE: Multiple Combobox Behaviors

(OP)

Quote (Chris)


(The internet uses slashes, not backslashes).

Yes, and I knew that... Unix also uses slashes, and at times I mix them up as well when working between them. What is it when you are looking straight at the problem and don't see it.

Actually, the url works with the backslashes in Edge, Chrome and Firefox after they or IIS auto converts them.

Good catch though...

Stanley

RE: Multiple Combobox Behaviors

(OP)

Quote (Chris)


I never said you you'd not use an alias. You put restrictions in my mouth I never told you about.
Thats the problem, you went against SQL query and never said what you would use, leaving me to only speculate.

Quote (Chris)


I just said I never would use a SQL query as rowsourcetype. Because you don't want to repeatedly do SQL potentially in every Interactivechange that does a requery of the cobobox items. What you should aim for is letting the interactivechange navigate the list of items you got once initially.
I agree.

Quote (Chris)


Your video also shows that the list coming up is full and just positioned on the best match. And as far as I understand your demand that item list is a full table, so alias as rowsourcetype is the case here.
Yes, it is a full list of all rows in the database.

Quote (Chris)


Same with an autocomplete textbox, you'd specify an autocomplete table with fully populated data column. Let's just put this aside a bit for now. I find it already is a solution, but you haven't even looked into it yet.
I'll do a test on this autocomplete table with the textbox, but I see an immediate issue with anything other than a combo. Within the grid cell, what do you plan to use for the data entry part and for the other list part?

Can we use a textbox as another control within the cell along side the combo and trigger the combobox? Doing so would mean that we would lose the visual downarrow component of a combobox and not the preferred solution. Doing so would involve toggling the column's selected control and may not be an issue.

If this was on a form, I would create a textbox and combobox and use them together, like setting the textbox.value to the value of the combo.DisplayValue after trapping for ESC. The textbox.controlsource property is not set programmatically. I do not think this form idea will work in the grid. Your thoughts?

Stanley



RE: Multiple Combobox Behaviors

(OP)

Quote (Chris)


the textbox portion is not just free from influence of the combobox items list.
When you set a controlsource the displaytext of the textbox will be the first column of the items list, that is the first column of the rowsource of the combobox.
So you're not starting empty in that textbox portion and can use it for input of the partial name you want to incrementally search for in the items list. That makes the combobox not usable for our idea. No matter how much we differ about details. The textbox is not independent from the items list. And that dependency is strong within a grid, as a gridcolumn always has a controlsource the combobox also inherits. Even with BoundTo = .F. you don't escape that dependency and influence, you don't have the textbox portion for your own usage only, anyway.
I saw the same behavior. I may be getting closer by creating a container that contains a textbox and a combobox reduced to the width of the downarrow, side-by-side. Then using the container within the cell. The combo has no controlsource, only rowsource and type. The textbox component has the controlsource.

Stanley

RE: Multiple Combobox Behaviors

(OP)

Quote (Chris)


In your video you show the data comes from a multiple field table. If you make the Firstname column of a combobox to pick from and allow entering a new firstname, well, what will the rest of the data empty. What about that.
Yes, the table has multiple fields and I can define what I want to show the user when they do a F6 lookup. The rest of the data could be empty, it doesn't matter as it is in the lookup table and shown if it exists. It helps the user determine if its possibly the same record and act accordingly.

Quote (Chris)


If actually a new Firstname is entered that isn't in the list to pick from, then you have other fields, most of them will be mandatory and not optional to know, won't they? So what about that, Stanlyn?
You keep forgetting this is lookup only. Nothing about the lookup record other than it is either there or not, and just position the cursor on the nearest match, ready for an "ENTER" (chr(13)), "ESC" (chr(27)), or nav arrows. We are not adding or updating the record in the lookup table. If ENTER is pressed with the lookup record selected, then we update the grid table, not the lookup table.

Quote (Chris)


Even if you have no field rules and a mostly empty record is allowed you're introducing sparse data that later needs to be cared for, if you allow new records to be entered by the combobox. If the grid list is not that table but the person column is just about maintaining a foreign key of a person, then you also don't have the other fields of the person record in the grid to enter that. And if you even would have the person list in the grid, then you would have the firstname column in it without a combobox. The navigation to a person then is best done with textboxes under or over the grid.
Lets try this another way using a customer table that we are editing and a postalcode table which is the lookup table...
At the zipcode field in the grid for the customer table, user types part of the zipcode and does a postalcode table lookup and positions the selector on nearest match, whether in the table or not. Pressing ESC cancels and takes us back to the textbox with its value still set to what it was when the lookup was initiated. Pressing ENTER will update the textbox value to the selected value and move focus to next control in tab path.

Hope it explains it a bit better.

Stanley

RE: Multiple Combobox Behaviors

(OP)

Quote (Chris)


There's a case you enter Rickeyzzzz at about 2 minutes and the result of that is that the navigation is at Rickie directly under Rickey. The only real advantage about this near search is that you're not nowhere or at EOF, but surely not that you're at one record below the last match.

The main advantage is in the eyes of the user, where they can clearly see a match, or a NO match, and if no match, they see what the area around where it should be looks like, instead of just being told there is no match. This allows somewhat a 3d view of the data with respect of what they are typing.

Don't you hate it when you are told a problem exists and no other explanation or indicators. This sort of addresses that.

If the user expects a match and doesn't get one, then they can try a different way. If we were doing this on a name field and user types in first name and the other displayed fields indicates the name is not this person, the user can try last name, and if still no match, the user can try company name and on and on. Extremely useful feature.

Thanks,
Stanley

RE: Multiple Combobox Behaviors

That problem is also addressed when they type Rickey and while they type, the list already displays. Then they likely already see Rickey after typing Ri or Ric. There is no need for the entry of Rickeyzzz to go to Rickie, if that's all more interactive than first typing a part and then needing to press F6 to search. If the user can interactively see where he is in the list while typing, ater reaching Rickey they already see the next entry is Rickie. And if the then type Rickeyz that should not get them to EOF. But it should also keep them at the better match Rickey and not put them to Rickie, that's what's not optimal at your idea. And I think you don't see that, as you don't think of the interaction of seeing the list of items while you type.

A combobox could theoretically expand while you still type in the textbox portion. The crux is just is that expanding the list already picks an item. That overwrites the textbox portion and you're interrupted in your typing. So that needs to be avoided or corrected.

Chriss

RE: Multiple Combobox Behaviors

I don't get an interactive list display to work, so I get back to something simpler and less useful, but fitting your needs.

A combobox in combo mode that allows you to enter a partial value - lastname in this demo - and then press END (you can adapt what key to use for the search this in the keypress event) to then SEEK with NEAR ON. Pressing ESC resets to the value memorized at GotFocus() event and ENTER enters the entered name, if it's not found in the list.

The prerequisites of this are mentioned in the code, but for sake of explaining it all in advance:
I intentionally describe what needs to be set by having all that in the Init() code of the combo. You could also set properties more directly, but what you can't set in properties is the prerequisite that the alis for the items needs to be sorted by an index that is also used to SEEK for searching. The combobox must also be used with rowsourcetype 6 (Fields), which determines which fields to see from an alias when the list is expanded, and since that also varies I put this into the init code of the combo1 combobox, too. Notice while the table has 3 columns, setting ColumnCount=2 is only for the display f items to not display the ID column, not even using a width 0. The BoundColumn can still be 3 as that's just about the number of the field in the Rowsource value, not the tables field number nor is it limited by Columncount.

Since the style is combo you can type into the textbox that's initially empty when the controlsource isn't picking a record by ID. I initialized the form property idPerson to 0, too, to ensure that. USed in a grid column you'll alwas have the column.controlsource inherited by the combobox, there's no way around that, the grid column will always override the controlsource of the currentcontrol. But even if there is a record selected, the Format='K' setting means the text portion is selected, so you can type, if you want to search another name, and that overwrites the current selection.

What I first intended and gave up is that typing incrementally finds the item in the list. Instead, you have to activate the search with a key, I picked the END key in this demo, as it's just at the opposite extreme position to ESC on my keyboard, the last key in the first row of keys. The nKeycode for that is 6, the nKeycode for F6 is -5, for example, and you find other key codes in the help of the INKEY() function.

The code also depends on the Rowsource starting with the alias name of the items of the dropdown lilst portion, if you don't specify the first field with its full name alias.field but only use the fieldname, that will fail miserably. It's one of several things I did to have code that will generally work and not only for this Persons table as the rowsource. But there are some things that nevertheless need the knowledge of the specific case, not only in the Init(). The case for ENTERing new records needs to know what field to populate with the Displayvalue entered. I therefore have foreseen a separate Method combo1.NewRecord() that will have the necessary Insert-SQL.

The rest of the combo1.code could also be class code that generally works, so specifics about a combobox instance are in the init or the properties you set and in the NewRcord() method. Just one more thing to pay attention, the name of the ID column to which the combobox is boundto by BoundColumn number is used to also find this field name by Getwordnum(This.RowSource,This.BoundColumn,','). It could be necessary to expand that to alias├╝fieldname in case the fieldname only is also present in another workarea active at the moment. Within a grid the grid workarea is not the list for the combobox items, just take care about that and keep it in mind.

The rest is explainied in the code. The major "magicc" happens in the Keypress event and is pretty much what I told you to use: a SEEK in SET NEAR ON mode. Even that can get to EOF, if you enter something that's even after the last record. I amend this situaation by SKIP -1 in that case, so you get to the last item, if you search for Z, for example, I don'T mean Rickeyzzz, but really just plain Z. The alternative would be that the expanded list would have the first name selected and that's far off Z, obviously.

You can make a class of it by taking the comobo1 procedures and make them class method code. Then you need to individually write the init code or set properties up at designtime and pay attention to some specifics I explained, like the NewRecord method. That should be empty in the class and must be programmed for each individual case. You could generally write in APPEND BLANK and REPLACE the one field you SEEK in with the Displayyvalue, but depending on field rules, foreign key realations etc. that might nort work in general, so I rather don't generalize this but make it a method to be programmed for each special case.

CODE

Local loForm

loForm =Newobject("searchablecombo")
loForm.Show
Read Events

Define Class searchablecombo As Form
   Top = 130
   Left = 198
   Height = 74
   Width = 348
   DoCreate = .T.
   Caption = "Searchable Combo"
   BindControls = .F.
   idperson = 0
   Name = "Form1"

   Add Object combo1 As ComboBox With ;
      Value = 0, ;
      Height = 24, ;
      IncrementalSearch = .F., ;
      Left = 24, ;
      TabIndex = 1, ;
      Top = 24, ;
      Width = 300, ;
      Format = "K", ;
      OldValue = '', ;
      Name = "Combo1"

   Procedure Load
      Close Tables All
      Erase Persons.*

      Create Table Persons(Id Integer Autoinc, LastName C(42), FirstName C(42))
      Insert Into Persons(LastName, FirstName) Values ("Meyer", "Corinne")
      Insert Into Persons(LastName, FirstName) Values ("Bogart", "Humphrey")
      Insert Into Persons(LastName, FirstName) Values ("Hepburn", "Katherine")
      Insert Into Persons(LastName, FirstName) Values ("Clooney", "George")
      Insert Into Persons(LastName, FirstName) Values ("Miller", "Chris")
      Insert Into Persons(LastName, FirstName) Values ("Lewis", "Mike")
      Insert Into Persons(LastName, FirstName) Values ("Smith", "Jenny")

      Index On FirstName+LastName Tag firstlast
      Index On LastName+FirstName Tag lastfirst
      Use
   EndProc
   
   Procedure Init()
      This.BindControls = .T.
   Endproc
   
   Procedure Destroy()
      Clear Events
   Endproc

   Procedure Combo1.Init()
      Use Persons In 0 Shared Order Tag lastfirst
      This.RowsourceType = 6
      This.Rowsource = 'Persons.Lastname, Firstname, Id'
      This.Columncount = 2
      This.ColumnWidths = '137,137'
      This.BoundTo = .t.
      This.BoundColumn = 3
      This.ControlSource = "Thisform.idPerson"
   EndProc 

   Procedure Combo1.GotFocus
      This.OldValue = Transform(This.Value)
   Endproc

   Procedure Combo1.KeyPress
      Lparameters nKeyCode, nShiftAltCtrl

      If nKeyCode = 27 And nShiftAltCtrl=0 && ESC
         This.Value = This.OldValue
         Return
      Endif

      Local lcAlias
      lcAlias = Juststem(This.RowSource) && depends on rowsouretype "Fields" and first field specified by Aliasname.Fieldname
      * Also, this combobox rowsouce must be sorted by index to SEEK in

      * remember NEAR setting
      Local llNearOn, llSetValue
      llNearOn = (Set('Near')=="ON")

      Select (lcAlias)
      If nKeyCode = 13 And nShiftAltCtrl=0 && ENTER
         Set Near Off
         If Not Seek(Alltrim(This.DisplayValue)) And Alltrim(FirstName)==Alltrim(This.DisplayValue)
            * name not found, create a new record...
            This.NewRecord()
         Endif
         * Name either found or added as a new record.
         * make it the selection
         llSetValue = .T.
      Endif

      If nKeyCode = 6 And nShiftAltCtrl=0 && END
         Set Near On
         *Local lcMessage
         *lcMessage = 'Seeking '+Alltrim(This.DisplayValue)
         * Sarch and correct EOF with last item.
         If Not Seek(Alltrim(This.DisplayValue))
            Skip -1
         Endif
         *lcMessage = lcMessage + ', finding '+Lastname
         *Set Message to lcMessage
         llSetValue = .T.
      Endif
      
      * Make the found or newly inserted record the selection
      If llSetValue
         This.Value = Transform(Evaluate(Getwordnum(This.RowSource,This.BoundColumn,',')))
         If nKeyCode = 6 And nShiftAltCtrl=0 && END
            Keyboard '{ALT+DNARROW}'
         EndIf
      EndIf
      
      * Restore NEAR setting as it was
      If llNearOn
         Set Near On
      Else
         Set Near Off
      Endif
   Endproc

   Procedure Combo1.NewRecord()
      * should be an empty method in a general searchable combo class to be specified
      * for a concrete instance of the class only, as there's no general way to know which fields
      * of the table need to be specified in the insert of a new record
      Insert Into Persons (Lastname) Values (This.DisplayValue)     
   Endproc 

Enddefine 

Chriss

RE: Multiple Combobox Behaviors

One more thing:

I asked you whether the combo is used to feed a column of the grid that is a foreign key or just a character column. In this case the result of the combo is a persons ID, not a persons lastname, so it's for a grid column that has a personid foreign key, not for a column of the lastname field. I think you could change that by switching to boundcolumn=1, but have one disadvantage with that: If two persons with same lastname exist, your grid column wouldn't care about that. In fact, it wouldn't care about the other fields of the persons table anyway, as you then just store the lastname into the grid column and the field associated with it in the grid recordsource.

And the other question I considered to you is also unresolved: How do you take care of the other fields, the firstname in this case? The grid column and your search entry can only cover the lastname. You could change it so that an entry of "Tolkien, J.R.R." is split up at the comma and inserted into the persons dbf file, but it would only cover the case of ENTER, not the SEEK which only looks into the index on the lastname+firstname, not lastname+','+firstname. And even if it would, to find that by SEEK you would need to explicitly enter the comma at position 43 in the entered name, not just anywhere. no matter if you'd use char or varchar fields, an index will always store a constant length key, not a variable length key.

I solved your demand - by my own terms at least, let's see what you answer, but it's not really very simple to make use of, if the table for the combo rowsource is more complex in terms of the ENTER feature to add new records on the fly. I get back to why MarK just offered what he offered in the beginning, it makes more sense that way. You may not care about the new records not having a firstname, but in the end you will need to care. Or you can only use this with tables of the type that have two fields - one Keyfield with the record ID and one data field. Such as a table of productcategories or a table of citynames. But even such simple tables usually do have extra data in them. A City may be stored with a geolocation and a category may point to a parent category. You can't maintain such extra data with such a combobox, where you only search and therefore also only enter one of the data fields.

And it may also all be much more complex. You might have a database for events with a guestlist that you might want to expand on demand. But then you will perhaps have an extra person in your data already, just not added to the guest list for that event. So what you would like to maintain is the table of eventguests, not the table of persons. It all boils down to me to have little usage for the new record feature. That needs specific addressing depending on the case.

What remains as a use case would really just be something like an assisted entry of character fields, really just the lastname of a persons list, where you can pick the lastname from persons you already know, which would use the same table maintained in the overall grid as the rowsource for the combobox, too. Or you would have a special extra lastnames table for that matter. And that comes very close to what you get from an autocomplete textbox, too. You can make use of several autocomplete tables for each more specific case, you're not restricted to one autocomplete table for all autocomplete textboxes.

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close