VFP 6 Drop Down Search as User Types

[cafulford]

Hi, I am completely new to VFP and have been asked to look at some code that was written years ago and try to add drop down search capability. I really have no idea on where to start.

In this case it will be when a user starts typing in the product #, a box appears below the entry that lists all of the products that begin with the first digit entry, then narrow accordingly as the user types additional digits.

I know practically nothing about VFP 6 so the more detailed you could be the more helpful.

Thanks in advance for any help that is given.

 

[Mike Gagnon]

Sounds like you need a combobox with incremental search capability turned on.
But the fact that you know nothing about Foxpro, this may not help you.
Are you are programmer?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[cafulford]

Hi Mike,

Thanks for the response. I am a programmer of sorts - Cobol, for what it is worth. I guess I am showing my age. In more modern stuff, I am a website designer so I am somewhat technical. Any help you could give me to point me in the right direction will be a great help.

Charlie

 

[Mike Lewis]

Hi Charlie,

I think what Mike Gagnon was saying is that it's difficult to give a solution to this problem without also having to give a lot of background information.

As Mike points out, the simple answer is that you need a combo box with incremental search. But, in order for you to do that, you also need to know how to work with the form designer, how to set properties and write methods, and how to build the application once you've implemented the solution.

It might be a good idea for you to read up on the basics of VFP programming before you go any further. There are plenty of free on-line resources that will help you with that. Once you feel comfortable with the basics, you can have a shot at implementing the solution.

By the way, an incremental search combo box doesn't exactly meet your spec. Essentially, it's a drop-down list of all the possible values. As the user types each letter, the highlight moves to the first entry that starts with the letters that have been typed so far. It will probably meet your users' needs, but it's now quite the same as the expanded-list feature you see in Internent Explorer's address bar, for instance.

I was also a COBOL programmer once. VFP is quite a different animal. It's a bit daunting at first, but it's definitely worth persevering with.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Gagnon]

I aggre with Mike Lewis. Get the basics first, then tackle this issue.

Once you have the basics down, these may help you
Faq184-1792 and faq184-1812

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[cafulford]

Thanks so much for the feedback. I understand and agree completely. I had to make 1 other change in this program a few months ago so I got my feet wet so to speak but that was a simple verbiage change. This one is a little more tricky but I will get there. Thanks for the advice and links.

Charlie

 

[Mike Lewis]

Good luck with this, Charlie. I hope you didn't think we were trying to discourage you. Play around with the combo box, and come back if you have any specific questions.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cafulford]

Mike(s),

I have figured out where the code is and played around with a combobox in the visual designer. It currently looks like a text box is prompting for a product #, then they do a direct lookup on that product # and display the product # info. Below is the code. Does this look like something that can be easily changed to a combobox or should I try from scratch?

Thanks,

Charlie

Set Exact on

WkProdId = UPPER(Alltrim(thisform.txtProdID.value))

WkLabelName = "Unknown"

If Len(WkProdId) < 1
Thisform.imgCSALogo.Visible = .F.
Thisform.txtSize.value = ""
Thisform.txtComponent1.value = ""
Thisform.txtComp1_Mat.value = ""
Thisform.txtComponent2.value = ""
Thisform.txtComp2_Mat.value = ""
Thisform.txtComponent3.value = ""
Thisform.txtComp3_Mat.value = ""
Thisform.txtMax_Wgt.value = ""
Thisform.txtMax_Force.value = ""
Thisform.txtMax_elongate.value = ""
Thisform.txtMax_Fall.value = ""
Thisform.txtMin_Break.value = ""
Thisform.txtCSA_Class.value = ""
Thisform.txtProd_desc.value = ""
Thisform.txtGeneralMsg.value = "* P R O D U C T N U M B E R E R R O R *"
WkLabelName = ""
Thisform.txtD_Ring.value = ""
Thisform.txtLabel_Fmt.value = ""
Thisform.txtCSA_Std.value = ""
Thisform.txtCSA_Logo.value = ""
Thisform.txtANSI_Std.value = ""
Thisform.txtOSHA_Std.value = ""
**
cMessageTitle = '* * * * * * * * * * * Safewaze Labeling * * * * * * * * * * *'
cMessageText = ' Blank Product Number...Do you want to EXIT?'
nDialogType = 4 + 32 + 0
* 4 = Yes and No buttons
* 32 = Question mark icon
* 0 = First button is default

nAnswer = MESSAGEBOX(cMessageText, nDialogType, cMessageTitle)
If nAnswer = 6 && 7 = they pressed No button (The Yes button is 6)
Clear EVENTS
Close ALL
Thisform.Release
Return
Endif
Return

Endif

Use M:\Easy32\data\sw_prod.dbf Shared
Set ORDER TO TAG prod_id OF M:\Easy32\data\sw_prod.cdx IN sw_prod
Go Top
NoProductFound = "N"
Seek(WkProdId) IN sw_prod

If NOT Found()
Use
Use M:\Easy32\data\sw_prod.dbf Shared
Set ORDER TO TAG prod_id_2 OF M:\Easy32\data\sw_prod.cdx IN sw_prod
Go Top
Seek(WkProdId) IN sw_prod
If NOT Found()
NoProductFound = "Y"
ENDIF
Endif

If NoProductFound = "Y"
Thisform.imgCSALogo.Visible = .F.
Thisform.txtSize.value = ""
Thisform.txtComponent1.value = ""
Thisform.txtComp1_Mat.value = ""
Thisform.txtComponent2.value = ""
Thisform.txtComp2_Mat.value = ""
Thisform.txtComponent3.value = ""
Thisform.txtComp3_Mat.value = ""
Thisform.txtMax_Wgt.value = ""
Thisform.txtMax_Force.value = ""
Thisform.txtMax_elongate.value = ""
Thisform.txtMax_Fall.value = ""
Thisform.txtMin_Break.value = ""
Thisform.txtCSA_Class.value = ""
Thisform.txtProd_desc.value = ""
WkLabelName = ""
Thisform.txtD_Ring.value = ""
Thisform.txtLabel_Fmt.value = ""
Thisform.txtCSA_Logo.value = ""
Thisform.txtCSA_Std.value = ""
Thisform.txtANSI_Std.value = ""
Thisform.txtOSHA_Std.value = ""
**
Thisform.txtGeneralMsg.value = "* P R O D U C T N U M B E R E R R O R *"
cMessageTitle = '- - E R R O R - -'
cMessageText = 'Product Number not found on file...'
nDialogType = 0 + 64 + 0
nAnswer = MESSAGEBOX(cMessageText, nDialogType, cMessageTitle)
Thisform.txtGeneralMsg.value = "* P R O D U C T N U M B E R E R R O R *"
Return
Endif


* 06/15/2009 Added below to show CSA logo based on new SAP Classification field CAF
If sw_prod.csa_logo = "Y"
Thisform.txtCSA_Logo.value = "Y"
Thisform.ImgCSALogo.Visible = .T.
Else
Thisform.txtCSA_Logo.value = "N"
Thisform.ImgCSALogo.Visible = .F.
Endif
* 06/15/2009 End CAF

Thisform.txtGeneralMsg.value = ""
Thisform.txtSize.value = sw_prod.size

Thisform.txtComponent1.value = sw_prod.Component1
Thisform.txtComp1_Mat.value = sw_prod.Comp1_Mat
Thisform.txtComponent2.value = sw_prod.Component2
Thisform.txtComp2_Mat.value = sw_prod.Comp2_Mat
Thisform.txtComponent3.value = sw_prod.Component3
Thisform.txtComp3_Mat.value = sw_prod.Comp3_Mat

Thisform.txtMax_Wgt.value = sw_prod.Max_Wgt
Thisform.txtMax_Force.value = sw_prod.Max_Force
Thisform.txtMax_elongate.value = sw_prod.Max_elong
Thisform.txtMax_Fall.value = sw_prod.Max_Fall
Thisform.txtMin_Break.value = sw_prod.Min_Break
Thisform.txtCSA_Class.value = sw_prod.CSA_Class
Thisform.txtProd_desc.value = sw_prod.prod_desc
WkLabelName = sw_prod.label_fmt
Thisform.txtLabel_Fmt.value = sw_prod.label_fmt
Thisform.txtD_Ring.value = sw_prod.d_ring
Thisform.txtCSA_Std.value = sw_prod.csa_std
Thisform.txtANSI_Std.value = sw_prod.ansi_std
Thisform.txtOSHA_Std.value = sw_prod.osha_std

Use

 

[cafulford]

Hi Mike(s),

I have another question:

I got a combobox to work using the builder just to see how it would behave and as you said, it is not exactly as the users requested and it does not appear like it would work for us. Is there a way with coding that it could work exactly as I had hoped?

Another thought - what about a textbox as it stands now but with a pop-up list via a key-press, that could pass the selection to the text field?

Thanks,

Charlie

 

[Mike Lewis]

Charlie,

With the combo box that you set up in the builder, check that the IncrementalSearch property is set to .T. If it isn't, you will see the drop-down list, but hitting the keys won't do what you want. Also, be sure that the Style property is set to 2.

You might also like to experiment with the setting of _INCSEEK. Set it to a higher number to make the combo more sensitive the multiple keystrokes.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cafulford]

Hi Mike,

Thanks for the input. Where is the _INCSEEK? I can not find reference to it anywhere including the help. Is that in VFP 6?

Thanks,

Charlie

 

[cafulford]

Hi Mike,

Sorry to badger, this is a bit frustrating. From what I can tell, _INCSEEK did not come about until VFP 7. It looks like I should be able to use _DBLCLICK but I went to the properties and added _DBLCLICK = 100 (clicks) and nothing changed at all. I also tried seconds like _DBLCLICK = 5.5. Is there a trick to getting a system cariable to work?

Thanks again,

Charlie

 

[Mike Lewis]

Charlie,

You were right to look for _DBLCLICK. I hadn't realised you were using VFP 6.0 (my mistake; you did mention it at the start of the thread).

You say you "went to the properties and added _DBLCLICK = 100". _DBLCLICK has got nothing to do with properties. It's a variable that is always present. You just need to do _DBLCLICK = followed by a suitable value in your code, for example in the Init of the form.

That said, I'm not saying for sure that this will solve your problem. You said earlier that the combo you set up with the builder didn't work. _DBLCICK / _INCSEEK was just one suggestion for something for you to try.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cafulford]

Thanks Mike,

I understand now about how that is used and where it should go.

After speaking with the users, I have been asked to do just add a combo box in addition to the text box they currently use. The combo box will be used only if they do not remember the product # (10% of the time) or if a newbie is training.

My problem now is when I add the combo box to the existing form using the builder, I get a file in use error but when I go to see the code, there is not any. It is as if the builder does everything in the background and does not produce any code. I was hoping to find the code where it is trying to open an already open file so I could comment it out.

I tried not using the builder and just setting properties for the file but that did not work either. I have also search relentlessly in hopes of finding an example w/no luck.

Thoughts, suggestions?

Thanks so much,

Charlie

 

[Mike Lewis]

Charlie,

It depends on the setting of the combo's RowSourceType. If it's set to 6, the VFP will link the combo to a table (the one specified in the RowSource). That table will remain open as long as the combo is alive.

This doesn't depend on whether you use the builder. The builder is just an interface for setting properties. It doesn't produce any code. In fact, it doesn't do anything that you can't do directly in the properties window.

I'm not sure how best to get rid of your "file in use" error. It depends on what other code you've got, and what files are being used.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cafulford]

Hi Mike,

I figured it out. I may be wrong but from what I can tell when you open a file like:

Use M:\Easy32\data\sw_prod.dbf Shared

The file is closed with just USE like:

USE

It appears to me that there was an extra USE that was closing the file so when I commented that out, all is well.

Thanks so much for your help on so many newbie questions. The problem is, I am asked to do this by default because there is no one else here who can but something only comes up every few months so learning it is almost impossible if you do not get a chance to use it.

Thanks again,

Charlie

 

[Mike Lewis]

Glad you've figured it out, Charlie. You're right that USE on its own will close the file (or, more exactly, the table). It operates on the table in the current work area (unless you add an IN clause).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Imaginecorp]

Charlie;
You could use a combobox to do an "auto complete", i.e. when a user types a character the combobox will display a whole word starting with that character. But unfortunately to come up with something like Google dropdowns etc need programming skills.
Try this code and see if this is what you want:

using the designer, create a form, drop a combobox on the form. In the dataenvironment of the form, drop a table. In the properties of the combobox, in "rowsource" type the name of the field which you want to display preceded by the table name i.e. Customer.ID, where Customer is a table and ID is the field name. In the "Rowsourcetype" of the combobox select "6-Fields", In the "InteractiveChange" of the combobox paste the following code

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))+Alltrim(This.DisplayValue)
	Endif
	If Empty(lcDisplayValue)
		lcDisplayValue=Alltrim(Chr(lnLastKey))
	Endif
	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)
				This.SelStart=Len(lcDisplayValue)
				This.SelLength=Len(Alltrim(This.DisplayValue))-Len(lcDisplayValue)
			Endif
			Return
		Endif
	Endfor
Endif

Run the form...

 

[Mike Gagnon]

Imaginecorp

As in my FAQ metionned above (which looks strangely like you code), careful of the displayvalue.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Imaginecorp]

Mike; It is YOUR code. I must have got it from you (this site etc) many moons ago...I have a habit of saving code that Impresses me (obviously this did) and modifying it to suit our needs. I have not used this in our app, had no need to, but if I do in the future will definitely acknowledge it as yours... Thanks