Popuplating a form from a query 1

[mikejcurtis]

I have a form that I want to have most of the combo box fields filled out using queries. The queries contain the unique values in a single field in the data table. I created methods that create the queries for the combo boxes and that works right. The problem is that I need to let the user type in a value if it doesn't appear in the list. When I type in anything not on the list, the field value disappears as soon as I leave that field.

 

[Mike Lewis]

Mike,

If you want to let the user type in new values, it's best not to use a query directly to populate the combo. Instead, run your query, sending the results to a cursor. Set the combo's RowSourceType to 0, then scan the cursor, adding each value in turn, using the combo's AddItem method.

You can check to see if the user has entered a new value by checking the Value property. If it is blank, the user has typed something in; in that case, use the DisplayValue property to find what they have typed. The best place to do that is in the combo's Valid event.

Hope this makes sense.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[mikejcurtis]

Please excuse my ignorance, but would you actually put a SCAN ENDSCAN in the AddItem? (I'm a little new to this.)
How would I add each value in turn? What would associate the Query with the Combobox?

then scan the cursor, adding each value in turn, using the combo's AddItem method.

 

[rgbean]

When I read "query", I usually think in terms of an SQL Select command - not a SCAN ENDSCAN. If you can't write a SQL command that can gather the data you need in the combobox, then just use Mike's suggestion of checking the value in the valid code and then add it when necessary. A Refresh() will be necssary to see it in the new list.

Rick

 

[mikejcurtis]

Can you give me an example of how to add a value that doesn't appear in the dropdown for the combobox? I think I understand that I would check the DisplayValue and the Value for the box and if the Value is empty, and the DisplayValue is not, I add it to the the cursor, but I don't get how to add it to the cursor.

 

[Mike Lewis]

Mike,

Sorry to have taken so long to come back to you on this.

would you actually put a SCAN ENDSCAN in the AddItem?

No. The code I mentioned, with the SCAN/ENDSCAN, would go in the Init of the combo, or possibly the Init of the form that holds the combo. You would only need to do this once.

If the code is in the Init of the combo, it would look something like this:


THIS.RowSourceType = 0
SELECT Customer
SCAN
THIS.AddItem(Customer.Cust_Name)
ENDSCAN
THIS.ListIndex = 1


Can you give me an example of how to add a value that doesn't appear in the dropdown for the combobox?

In the combo's Valid, do something like this:


IF EMPTY(THIS.Value)
* User has entered a new value
* Do something with the new value
* For example, add it to the original table
SELECT Customer
APPEND BLANK
REPLACE Cust_Name WITH THIS.DisplayValue
* Requery to make the new value visible
THIS.Requery
ELSE
* User picked an existing value
* Do something with this value
ENDIF


This code is only meant to give you the general idea. Hope it helps.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)