Passing SQL SELECT parameters to FORM/Listbox

Visual FoxPro General

Gary Demi


I have a form with a listbox,

I have changed the source on the listbox to a SQL SELECT, in the form of SELECT * FROM DBF INTO CURSOR myform WHERE anum=thisform.anum

thisform.anum is passed from the calling program as a parameter and is initialized in form.init,
yet apparently the sql select statement is called prior to the form.init (why ) and thisform.anum has an invalid value.

Can I use the SQL SELECT with a variable where clause in the listbox, and if so, how should those values be passed.





Top

Re: Passing SQL SELECT parameters to FORM/Listbox

Naomi Nosonovsky


I think the simplest way would be to set listbox RowSource in run-time rather than in design time.

BTW, I rarely use type SQL for RowSource and prefer to use Alias instead.

E.g. in the Init method of the form you can do

Code Snippet

select * from myTable into cursor curMyList where ... nofilter

with thisform.ListBox1

.RowSourceType = 2 && Alias - check this

.RowSource = 'curMyList'

.requery()

endwith

BTW, to answer on your question why - form's controls Init fire before Form's Init (and always container's Init fire after its inner objects Init), so the property has to be available in Load or if this is impossible, objects properties have to be set in run-time.

In certain cases I even add grid on the fly if grid's cursor is only going to be available in the Init.



Top

Re: Passing SQL SELECT parameters to FORM/Listbox

CetinBasoz

Prefer SQL rowsourcetype (3) instead of alias.

Init codes are ran in the order "innermost object first, outermost object last". Thus form.init is the last in the sequence. However solution is very simple. Do not set listbox's rowsourcetype and rowsource neither in PEM sheet nor in its init (leave at their default).

In form init or whereever you have the needed parameters are ready, set them. ie: In form init:

with this.lstSomeListbox

.RowSourceType = 3

.RowSource = "select * from myTable where myField = thisform.anum into cursor crsListSource"

endwith

Whenever anum changes you just requery listbox ( thisform.lstSomeListBox.requery() ). It's simple and slick and better than using alias.



Top