How to fill a list box in a form with unique labels. 4

[german12]

I have a VFP file with about 1500 stock movements which are often repeated.
Filename: aktien2
Field name : ak2begriff (character)

I use this .dbf for a form (called form1)
Init for form1: use aktien2
The form1 has a listbox (called picklist)
I want filled it with unique values of the shares, therefore I filled
these properties for the list box:

RowSource of picklist =
select distinct(ak2begriff) as "ak2begriff" from aktien2

RowSourceType of picklist =
3 (SQL statement).

When I start the form, I see the result of the SQL statement
as a query - and if I click away this display, then the listbox
disappeared.

Then this error message comes:
Cannot access the selected table.
Select distinct(ak2begriff) as "ak2begriff" from aktien2

What is my fault?

Thanks
Klaus


Peace worldwide - it starts here...

 

[Mike Lewis]

Klaus,

This is not the best way to populate a listbox.

First, when the RowSourceType is 3, the RowSource should be like this:

[tt]select distinct(ak2begriff) as "ak2begriff" from aktien2 [highlight #FCE94F]INTO CURSOR csrBegriff[/highlight][/tt]

The name of the cursor, doesn't matter - csrBegriff is just an example.

That said, I suggest that instead of RowSourceType of 3, you set RowSourceType to 6. Create the cursor in the Init:

[tt]select distinct(ak2begriff) as "ak2begriff" from aktien2 INTO CURSOR csrBegriff[/tt]

Then set the RowSource to "csrBegriff.ak2begriff".

Also, the AS clause in the SELECT is redundant, as you will never need to reference the column by name. But it will do no harm to keep in in place.


CORRECTION: You do need the AS clause, as the field name is needed for the RowsSource.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Thanks Mike - I am sure you can show me the right way - however there is still an issue. May be that I had not understood something.

select distinct(ak2begriff) as "ak2begriff" from aktien2 INTO CURSOR csrBegriff

this command was placed in the init-method of the listbox (named picklist)

Then set the RowSource to "csrBegriff.ak2begriff".

this was also placed in the istbox (named picklist)

As the select command leads to a temporary Cursor-file, I get the message that the rowsource csrBegriff.ak2begriff is unknown.

Can you help?

Klaus



Peace worldwide - it starts here...

 

[Mike Lewis]

Klaus,

The errror message indicates that the error occured in the listbox's Refresh method. As far as I can see, there should not be any code in that method.

Also, where are you setting the Rowsource? In the Init method, or in the form designer?

I have just run the following code, and it works. This code is in the listbox's Init. It doesn't need any other code or property settings to achieve the goal.

SELECT DISTINCT(company) AS company FROM customer INTO CURSOR csrCust
this.RowSourceType = 6
this.RowSource = "csrCust.Company"

Obviously you would have to change the names of the fields and table, but you should be able to use this code as a model for what you want to achieve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Mike,
first of all - thank you for the model.
Indeed - it works for me as well.
I just created as a test a form with one Listbox - and that was running also well.

Unfortunately it does not work - in connection with my form where I want to implement a listbox with unique values.
I assume a file conflict - but can not fix it.

My form uses the dbf aktien2 - and it is used for several other evaluations on the form - and that runs.

I show you the init of the form and the init of the listbox below.
and then the error which VFP shows when I start the form.





Init command for the form (named Form1)
CLOSE DATABASES
SET TALK OFF
SET SAFETY OFF
SET DATE GERMAN
SET POINT TO ','
USE aktien2

*aktien2 is the basic and only dbf that has to be evaluated.

Init command for the listbox (called picklist)
SELECT distinct(ak2begriff) as begriff FROM aktien2 INTO CURSOR csrCust
this.RowSourceType = 6
this.RowSource = "csrCust.begriff"


this leads to

What is that?

Klaus


Peace worldwide - it starts here...

 

[german12]

Mike Y.
Thanks.
My thread has crossed with yours.
For tonight it is too late - however I will also test your code.

Klaus


Peace worldwide - it starts here...

 

[Mike Lewis]

Klaus,

The VFP Help includes this information regarding the error you are seeing:

[The error occurs when a] ... SQL SELECT statement that recreates a table or cursor that is specified by the RowSource property is issued. When the SQL SELECT statement executes, Visual FoxPro closes then opens the table.

This could be what is happening in this case. The solutiuon is to "set the RowSource property to an empty value (""), issue the SQL SELECT statement, and reset the RowSource property to its previous value."

If this does not help, check the rest of the Help topic for this error message (drill down to Reference -> Error Messages).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Why I bother to say anything I don't know.

On the contrary. Klaus thanked you for your suggestion, gave you a star, and said he would try your code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Mike Yearwood:

I tried your code.
However what stands for

THIS.AddProperty("iaData(1)")

What has to be replaced for ...iaData...

The array which is built up to that point shows this:

I would like to have the array column "init" to be filled into the listbox.
Of course VFP does not understand ..iadata... and leads to

Please help me as a beginner a bit.

Thank you
Klaus




Peace worldwide - it starts here...

 

[Chris Miller]

Look deeper into RowSourceType and you may get it to work.

I see you got much advice to go for other, simpler, easier to handle rowsoucetype, nevertheless a query does work, if you do it by the rules of the help on "How to: Choose the Type of Data for a List or Combo Box" and then the section "SQL Statement Source Type"

The help tells you to care for the INTO CURSOR clause of the SQL Select, otherwise a browse window appears. I don't think that's accurate, but the query result will be in a workareay called "query" and then two or more controls with such a rowsourcetype without INTO CURSOR will overwrite each others result.

The second problem is your source data has to be available when the query is run, and that's even before form.init, so the data to query from has to be opended either in LOAD eent of the form, in data environment via its graphical overview of tables in the D or in the DE code section.

Several situations and combinations of them can lead to the error "Cannot access the selected table." It also would help to know which table and when this occurs and what error number exactly, because there are several similar error messages.

The SQL select statements should be something like SELECT DISTINCT field as colname FROM table INTO CURSOR curlistbox1, and then 2,3, etc. Distinct workarea names. The case a form can run twice also has to be thought of, or form with multiple pages or containers each having a listbox1. So the actual unique name must cover all the cases you can think of. Sounds horribly complicted, but you have the same problem with unique workarea names when you pick the rowsourcetype of table/alias.

For several reasons it is indeed simpler to only use cursors for rowsources and prepare them with code, if necessary. The SQL statement type is not so bad, if you do listbox.requery() in that case it literally requeries the data, it executes the SQL query once more. For that to work, obviouslly the tabl in the FROM clause has to be still available. It can help to not just put the table name there, but the actual file name of the DBF.

It's a big no-go with the grid control and therefore I guess it's so widely comon sense to not use it for anything and just concentrate on table/alias rowsource types. But to me it also has it's use cases. The form designer is not the best place to write the query in, though. And you always have to think about what's in scope and thus available at any time the query could run or rerun.



Chriss

 

[Nigel Gomm]

German12

in the combobox's .init

THIS.AddProperty("asource(1)")
SELECT DISTINCT(company) AS company FROM customer INTO array this.asource
THIS.RowSourceType=5
THIS.RowSource="THIS.aSource"


or add a property .aSource(1) to your combobox's baseclass and you won't need the .addproperty() .


hth

n

 

[german12]

Mike Yearwood:

mike yearwood,
Thanks for the documentation of your code, it really gave me a better understanding.
With the RowSourceType = 5 the program has now also done what I wanted.

Init in Form1 : Use aktien2.dbf

Init in the list box (your code adapted to my environment:
IF NOT DODEFAULT()
RETURN .F.
ENDIF

*This creates an array of the list values.
LOCAL ARRAY laCompanies[1]
SELECT DISTINCT(ak2begriff) AS company FROM aktien2 INTO array laCompanies

*This creates an array property for the array data in the control.
THIS.AddProperty("iaData(1)")
*This makes the array property big enough for the data
DIMENSION THIS.iaData[alen(laCompanies,1),alen(laCompanies,2)]

*This copies the array data into the array property.
ACOPY(laCompanies,THIS.iaData,1,-1,1)
*
THIS.RowSourceType=5
THIS.RowSource="THIS.iaData"

That works perfect.
I had only two lines of code to adapt.

The yellow listbox shows now the unique titles of shares.

Klaus

Peace worldwide - it starts here...

 

[german12]

Chris, thank you very much for your explanations of the problems that can arise and also for the valuable place in the VFP help file.
As described in the post to Mike Y. above, I specified the source file in the form's init (not in the load-Event as you suggested).
I'll try that again.

I had my problem with only one source file.
However, if I have a form in which the contents of several files should appear in different list boxes on the form, then there can probably be file conflicts even more easily.
I have to test that again.

Klaus

Peace worldwide - it starts here...

 

[german12]

Nigel, thanks also for your hint.
Compared to Mike Yearwood's code ... but in your code is NOT a dimension for the array which Mike Y has in his code.

*This makes the array property big enough for the data
DIMENSION THIS.iaData[alen(laCompanies,1),alen(laCompanies,2)]

What is the reason?

Klaus



Peace worldwide - it starts here...

 

[german12]

Mike Lewis:
Thanks for your multiple help with troubleshooting, and of course also for trying to clarify something which I couldn't have done because of my language.

Klaus


Peace worldwide - it starts here...

 

[Nigel Gomm]

Klaus,

but in your code is NOT a dimension for the array

..because the SELECT builds the final array direct with "into array this.asource"

no copying between arrays required.

n

 

[Chris Miller]

Form Init happens after all controls inits, so that the form init can handle things the controls.
So yes, data has to be made available in the form DE or in the form load already, otherwise you easily get the "cannot access ..." error.
A query will also look for a dbf with that name, by file name. So CD or SET DEFAULT into the data directory would also help, but again, before form.init()

Chriss

 

[Nigel Gomm]

Mike Y

the array is created by the addproperty (or in the baseclass) it's just not populated if the table is empty.

and you can always

if _TALLY = 0
this.asource(1) = "(none found)" && or somesuch
endif

immediately after the select.

n

 

[german12]

Thanks to all of you again for the many hints I received regarding my question..

I've now had no trouble popularizing 3 listboxes
on a form.
The code for each list box (shown above by Mike Yearwood) was practically the same, I only had to adapt the SQL statement (in the INIT of each listbox) to the *.dbf to be selected as the data source.

There were no file conflicts. and I had not to open or close a file via code.
The result is this:

It would be very easy and save to create and populate lots of further list boxes with different row-sources in a form or formset.
That means I had only to copy one listbox and change the init (SQL)code just a little bit.

As recommended by you the init of the form contains no use of dbf-files.
Just this:
CLOSE DATABASES
SET TALK OFF
SET SAFETY OFF
SET DATE GERMAN
SET POINT TO ','

Other evaluations of all used dbf's are coded (or can be coded) in the click/doubleclick or other methods of the respektive listbox.

Great!

I would not hesitate to ask for the solution of other problems in this forum here.
I don't know a better one.

Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

The code for each list box (shown above by Mike Yearwood) was practically the same, I only had to adapt the SQL statement (in the INIT of each listbox) to the *.dbf to be selected as the data source.

Just so you are aware, RowsourceType = 5 means Array, the Listbox is now bound to the array the SQL Select INTO ARRAY creates.

It's working, as we can see, but won't update the list with a Listbox.Requery(). Your own idea still is valid...


...Or let me put it this way: You use a query rather than binding to a list of stock share names in another table, as you don't have that share name list and it would be overwhelmingly much to many share names. Instead, you have a list of relevant share items and their price development over time, perhaps a CSV. And you don't bother normalizing this, as the list of the relevant share names relevant varies, but the constant is that you'd like to pick from the share names of this list.

Depends on which timeframe we talk, whether the feature to get the latest list of share names from the query is sufficient just once per form start or you'd like to requery that, when the table data also shifts in new share names and shifts out others. Then it's still not the most idea solution, but you'd not need to reinit the whole form to get the current name list.

Chriss