Combo Box Help

[Auguy]

I need help with a VFP 6.0 combo box. The controlling table on the form is a check table that contains checks from different companies. Each company has several different checking accounts. What I am trying to do is filter the checking accounts available in the combo box limiting them to the matching company (field in check table). The combo box's record source is the checking accounts table. In the got focus of the combo box I select the checking accounts table and set a filter to the corresponding company ID. In the lost focus I reset the filter. This works fine, but when I move the record pointer to another check record with a different company ID, the combo box's display is empty, but looking at the record it has a value. I hope this is clear enough. Any ideas as to why this doesn’t work? I thought about selecting the records into a temp cursor, but thought this would be easier and faster because of the limited number of records in the checking accounts table (10-20). I have tried requery and refresh with no success.

Auguy
Sylvania, Ohio

 

[ilyad]

There was a similar thread about two months ago, check thread 1349281

 

[ilyad]

I'm sorry, I don't know yet how to put a link to the thread on this forum, so I give you full link

http://tek-tips.com/viewthread.cfm?qid=1349281

 

[Auguy]

Thanks Ilyad. Getting the filter to work in the combo isn't a problem and I don't need an interactive search. It's when I move to another record and the combo box appears blank even though the filter has been reset and there is a valid value in the table. When I move to a record I only want the combo box to have values associated with the given company ID.

Auguy
Sylvania, Ohio

 

[ilyad]

Did you move a record pointer after SET FILTER command? That's required to activate the filter.

Personally I don't use SET FILTER command in my applications at all. You may also try SET KEY command instead.

 

[Auguy]

I tried moving the pointer in earlier testing, will re-visit and see if it works now. I normally don't use the set filter either, but thought this was a good spot for it due to the snall number of records involved.

Auguy
Sylvania, Ohio

 

[ilyad]

Let me know if you would be able to sort it out. I'll take a closer look into the problem at night.

 

[bborissov]

Auguy,
For this kind of queries I prefer to use parametrized CusrorAdapters, or if you use a version before VFP8 Local Views. Just store the variable and use CursorRefresh (or REQUERY([YourLVorCAalias]))


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Imaginecorp]

This works fine, but when I move the record pointer to another check record with a different company ID, the combo box's display is empty, but looking at the record it has a value.

This is because the value does not exist in the choices... i.e. Table record has a/c# 123, the drop down choices do Not have 123 as a choice.

I would not set filters, rather use a select statement. 10-20 records in the checking account table is even better as it would take nano seconds for the query to run.

Create a method in the form and put your select statement there. It has to be called from 2 places. once when the form is displayed
1. Combobobox's Init()
2. When() Event of the combobox.

The problem here is if there is no company ID, (during an Add) the combobox will be empty, which is fine as there are accounts to select, once a value is entered and the combobox gets the focus, the Query would have already ran...

Again this is to give you an idea on how to do it, you will have to fine tune...

 

[Imaginecorp]

sorry;
"...which is fine as there are accounts to select..."
should be
"...which is fine as there are No accounts to select...

 

[Auguy]

I think I have it working now (I think). I put the select for the particular company in my edit method and the select for all companies in my save and undo methods. I still had to play around with setting focus to them to get them to display the actual value and not an empty combo box. I guess the question really is, what should you do when you change the available records in the row source to get the combo box to display properly. I tried requery, but that didn't help.

Auguy
Sylvania, Ohio

 

[Imaginecorp]

Auguy: here is some code to help you understand how to do this.
Be aware this is the simplistic way of doing it...
Once the combobox in the example below has a controlsource it will never be empty as long as there is a value for it in the table.

PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.Show
RETURN


	**************************************************
*-- Form:         form1 (e:\imaginecorp\form111.scx)
*-- ParentClass:  form
*-- BaseClass:    form
*-- Time Stamp:   05/16/07 03:21:06 PM
*
DEFINE CLASS form1 AS form


	Top = 33
	Left = 41
	Height = 159
	Width = 375
	DoCreate = .T.
	Caption = "Form1"
	Name = "Form1"


	ADD OBJECT combo1 AS combobox WITH ;
		Height = 24, ;
		Left = 78, ;
		Style = 2, ;
		TabIndex = 2, ;
		Top = 65, ;
		Width = 169, ;
		Name = "Combo1"


	ADD OBJECT text1 AS textbox WITH ;
		ControlSource = "Customers.companyname", ;
		Height = 23, ;
		Left = 73, ;
		TabIndex = 1, ;
		Top = 30, ;
		Width = 199, ;
		Name = "Text1"


	ADD OBJECT command1 AS commandbutton WITH ;
		Top = 118, ;
		Left = 252, ;
		Height = 27, ;
		Width = 84, ;
		Caption = "Next", ;
		TabIndex = 3, ;
		Name = "Command1"


	PROCEDURE Load
		If Select("customers") > 0
			Select customers
		Else
			Select 0
			Use Home()+"\samples\northwind\customers.dbf" Shared
		Endif
		If Select("orders") > 0
			Select orders
		Else
			Select 0
			Use Home()+"\samples\northwind\orders.dbf" Shared
		Endif
		SET ORDER TO tag Customerid
	ENDPROC


	PROCEDURE combo1.Init
		Select customers
		cId = customers.customerid
		Select orders.orderid ;
			FROM orders ;
			WHERE orders.customerid = cId ;
			INTO Cursor oCursor
		With This
			.Clear
			.RowSource = "oCursor.orderid"
			.RowSourceType = 6
			.Value = oCursor.orderid
		Endwith
	ENDPROC


	PROCEDURE command1.Click
		Select customers
		If !Eof()
			Skip
		Endif
		Thisform.Refresh
		Thisform.combo1.Init()
	ENDPROC


ENDDEFINE
*
*-- EndDefine: form1
**************************************************

 

[Auguy]

Thanks Imaginecorp, I will use this code as base for my situation.

Auguy
Sylvania, Ohio