Create a quick search in text field 1

[PichiMedia]

I am using VFP8. I have a table called CLIENTS with 2 fields, one is called CLIENT_NAME(character) and the other one is called VALUE(numeric). I have a simple form, on which I dragged a text field (TEXT1), and a combo box (COMBO1). I've inserted the CLIENTS table at the DATA INVOIREMENT window, so this table has been added there. I've linked the combo box to the CLIENTS table, on CLIENT_NAME field, so when I click on the combo, I can see all the clients name from the table. What I want to do is a quick search procedure, whenever I type a letter in the text field TEXT1, the combo box to show the clients with that letter. I trid to add a SET FILTER TO command in the INTERACTIVE CHANGE on TEXT1:

SET FILTER TO ALLTRIM(THISFORM.TEXT1.VALUE)=SUBSTR(CLIENT_NAME,1,LEN(ALLTRIM(THISFORM.TEXT1.VALUE)))
GO TOP
THISFORM.REFRESH

This works, I mean, I type in the TEXT1 the letter C, and the combo box shows me CURT, which is what I want it to do. But if I try to click on the combo box I get this error:

THISFORM CAN ONLY BE USED WITHIN A METHOD

I press OK on this error, and then it works again, I type another letter in the TEXT1 and the combo box shows the name that starts with that particular letter, but again, if i try to click on the combo I get the same error. Is there a way to eliminate this? I read the help section, because the error window provides a HELP button and it says I have to use a METHOD. I'm really new to VFP, please can anyone help me? Thank you very much.

 

[ilyad]

I don't think that using SET FILTER command is a good idea. If you really want to use it, then you need to do this:

set exact off
lcFilter = [Client_name = '] + alltrim(THISFORM.TEXT1.VALUE) + [']

set filter to &lcFilter
locate

However, there is cleaner and better way to achieve the desired functionality. The code belongs to Marcia Akins and I'm not sure, if it's OK to post it here.

The same question was asked just few days ago on UT where a link was posted to Marcia's message.

 

[TamarGranor]

You'll find a QuickFill combo on my website at

http://www.tomorrowssolutionsllc.com/session_materials.htm.

Grab the examples in Developing Applications for Everyone.

It usss only a combo rather than a combo plus textbox, but it works like the combos in Quicken.

Tamar

 

[ilyad]

I think this code can be easily modified to be used with conjunction with a textbox.

 

[PichiMedia]

Thank you guys, for your help, I did tried to add this an the Interactive Change of TEXT1:

set exact off
lcFilter = [Client_name = '] + alltrim(THISFORM.TEXT1.VALUE) + [']

set filter to &lcFilter
locate


but it does nothing. Then I've downloaded the files from the website, but it's to complicated for me to get all those, I'm really new to all these, but I'll keep digging. Thank you very much for your time.

 

[Mike Lewis]

PichiMedia,

This doesn't answer your question, but I'd like to suggest that you don't use VALUE as a field name. As you know, VALUE is a keyword in VFP (it's the name of a property), and it's generally not recommended to use keywords as field names or variable names.

In this case, it's unlikely to do any harm, but avoiding keywords in this way is a good habit to get into.

I hope you manage to solve the combo box problem.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

Try this; I have not tested it and you will have to fine tune:

**** Combobox.INIT()
with this
.addproperty("LastDisplayValue","")
.addproperty("lastselstart",0)
.addproperty("lastsellength",0)
endwith 

*** Combobox.interactivechange()

LOCAL lnStyle, lnLastKey, lcDisplayValue
lnLastKey=lastkey()
lnStyle = this.style
IF this.style = 2
	this.style = 0
ENDI
IF (lnLastKey>=32 and lnLastKey<=126)
	IF this.selstart>=1
		lcDisplayValue=substr(this.displayvalue,1,this.selstart-1)+(chr(lnLastKey))
	ELSE
		lcDisplayValue=(chr(lnLastKey))+allt(this.displayvalue)
	ENDI
	IF empty(lcDisplayValue)
		lcDisplayValue=alltrim(chr(lnLastKey))
	ENDI
	FOR i = 1 to this.listcount
		IF upper(lcDisplayValue)==upper(substr(this.list(i),1,len(lcDisplayValue)))
			this.displayvalue=this.list(i)
			IF .t. or len(alltrim(this.displayvalue))>len(lcDisplayValue) && added .t. to allow it to keep searching even
				this.selstart=len(lcDisplayValue)                      && when an item matches completely.
				this.sellength=len(allt(this.displayvalue))-len(lcDisplayValue)
			ENDI
			this.lastdisplayvalue = this.displayvalue
			this.lastselstart = this.selstart
			this.lastsellength = this.sellength
			RETURN
		ENDI
	ENDF
	if this.acStyle = 2
		this.displayvalue = this.lastdisplayvalue
		this.selstart = this.lastselstart
		this.sellength = this.lastsellength
	endif
ENDIF

For textboxs see my F&Q's

 

[PichiMedia]

Thank you very much, this works like a charm, i've been going mad for the past few days, and didn't thought I'll manage to do it, but thanks to you guys, now it works. I need to do some minor modifications to display the name as I want to, but it works , thank you!

 

[Imaginecorp]

Mike Lewis:
Seems like you put the fear of God into Pichimedia. That was a fast response.....

Pichimedia: No offence meant, just teasing you {BG}

 

[FoxEgg]

Imaginecorp (IS/IT--Management)
24 Mar 07 13:35
Try this; I have not tested it and you will have to fine tune:

I tested it and it is brilliant... Thanks

JF

 

[Imaginecorp]

Thank you: I am glad I could help...

 

[FoxEgg]

Whoops...

if this.acStyle = 2
        this.displayvalue = this.lastdisplayvalue
        this.selstart = this.lastselstart
        this.sellength = this.lastsellength
endif

reposts an error for this.acStyle

I think it should be

if this.Style = 2

Hope that is correct...

 

[NasibKalsi]

Create a form level property f_IncrementSearch by clicking

form, new property

then

thisform.f_Search = thisform.Text1.Value

SET FILTER TO ALLTRIM(THISFORM.f_Search)=SUBSTR(CLIENT_NAME,1,LEN(ALLTRIM(THISFORM.f_Search)))
GO TOP
THISFORM.REFRESH


***************************************
* Another way

thisform.f_Search = alltrim(thisform.Text1.Value)

if empty(thisform.f_Search)
set filter to && Show All
thisform.refresh
return
endif
set filter to at(thisform.f_Search,CLIENT_NAME) > 0

GO TOP
THISFORM.REFRESH


Nasib Kalsi

imaginecorp: will it work ?

 

[Olaf Doschke]

Hi Nasib Kalsi,

I'm not Imaginecorp, but no, it has the same problem. You are moving in the right direction, but ilyad has it right, you should only put THISFORM.Refresh() after it to make the combobox show the filtered table.

PichiMedia: The problem with the initial code of yours is, that the filter is evaluated outside of the scope of THISFORM.

It's comparable to the problem this key assignment has:

ON KEY LABEL F6 DO Messagebox(thisform.caption)

Even if a form is running and active and you make that key assignment inside a form, the code Messagebox(thisform.caption) is run outside of the form scope, thisform is not known then. What the error message want's to say is, that you can only refer to thisform inside a method, more precise a form method.

Like that key assignment a filter is also not applied just once in the moment you SET FILTER TO something, which you do in a form (or form.control) method. It's also evaluated whenever the table is accessed. The moment you access the combobox it queries it's rowsource and finds there is a filter set on that alias. That filter is evaluated and that is not done within a method, that is done on a more general level. So that evaluation does not work if THISFORM is part of the filter expression.

You have the same kind of problem when using a form property and again use thisform, using a (local) variable is also no solution, a public variable would work, but it's not a good idea to have global variables.

So you do it as ilyad, you put the whole filter expression in a string variable and then use macro substitution with SET FILTER &lcFilter. That way the expression is not refering to thisform, a form property or a variable, not even the lcFilter variable. lcFilter is compiled at that time, more exactly the whole line is comiled after &lcFilter is substituted by the value of lcFilter. It's like you initially wrote SET FILTER TO Client_Name="C", if you entered C to the textbox.

Although everyone suggests to not use that approach, with only few records this is sufficiently fast and despite the scope problem it's quite easy to make use of SET FILTER if you let macro substitution do it's magic.

So at the interactive change of your search textbox put this code:

set exact off
lcFilter = [Client_name = '] + alltrim(THIS.VALUE) + [']

set filter to &lcFilter
locate
thisform.refresh()

The only weakness is, if you input ' inside the text searchbox. So you might simply suppress that character. As you can use '," and [] as string delimiter and as there are names like O'Brien, I'll use [] and " within. Also, make sure you set the filter to your table, if it is named "clients" do it like this:

set exact off
Local lcFilter, lcClientname
* replace " with nothing:
lcClientname = chrtran(alltrim(THIS.VALUE),["],[])
lcFilter = [Client_name = "] + lcClientname + ["]

set filter to &lcFilter in clients
select clients
locate 
thisform.refresh()

Bye, Olaf.

 

[NasibKalsi]

Thanks Olaf.

More or less I knew that problem. Thanks for further explanation. Also I know that
on key lable F5 thisform.property format does not work.


But I tried my method, and it does work fine. I do not know what is the catch.

It looks like to me that, form variable created works as public during the life of the form, but TextBox of the type thisform.text1.value does not.


lcFilter = "Client_name = '" + alltrim(THIS.VALUE) + "'"

Yes this will work for sure, because you are passing a literal as expresion and a SMART WAY. I did not try to read the expression in the first instance as ilyad put it and continue reading until solution as accepted by the pichimedia. I felt the code is long and offred my version ...

Note: I have hard time visualizing the statement with []. I use them in rare situations when I have no resort. excuse me to change the code.


Nasib Kalsi