Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ListBox & RowSource

Status
Not open for further replies.
Eugen Fintina

Eugen Fintina

Programmer
Apr 27, 2005
29
0
1
RO
I have a ListBox with RowSourceType = 6 (Fields) and tried two ways to set RowSource for it:

[ol 1]
[li]lcRowSource1 = "MyTable.MyFirstField,id"[/li]
[li]lcRowSource2 = "alltrim(alltrim(MyTable.MyFirstField) + ' ' + alltrim(MyTable.MySecondField)),id"[/li]
[/ol]

If I use lcRowSource1 to initialize ListBox, all it's ok.
But when I use lcRowSource2 the things go crazy. It seems that, somehow, the pointer "jump" to the first record.
Is there any explication for this behavior?

All the best,
Eugen
 
Sort by date Sort by votes
When you set the RowSourceType to 6, the RowSource must take this format:

Code: 
alias.field1, field2, field3, ...

In other words, you precede the first field name with the alias, but not the subsequent ones.

So, if you have just one column in the listbox, then the RowSource would be something like:
Code: 
Customers.CustName

If you have multiple columns, it will be something like:

Code: 
Customers.CustName, Email, Phone

And be sure to set the ColumnCount property to match the number of fields.

In your example, your first setting is correct. Your second setting appears to evaluate to the actual values of the fields (rather than the names of the fields), which is clearly wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Thank you Mike, Chris!
I'm sure that you are right. Meanwhile I found a workaround that solved my problem.
But, if my memory didn't lie me, I'm pretty sure that in the past I populated ListBox with a complex expression (even with values from different tables) without using SQL.
Maybe, I'm wrong. I need to find and check that old application.

Bye,
Eugen
 
Upvote 0 Downvote
Eugen, yes you can use an expression as the RowSource, but it must evaluate to something valid. Your expression [tt]alltrim(alltrim(MyTable.MyFirstField) + ' ' + alltrim(MyTable.MySecondField)),id[/tt] is meaningless in the context of a RowSource.

Perhaps if you tell us exactly what you want the listbox to contain, we might be able to suggest the best solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Mike, do you have an example of an expression for a RowSource? The help doesn't support that and I also see no RowSourceType that would help.

As you set it to fields, it has to be of the form "table.fieldname,fieldname,fieldname" and when your expression "alltrim(alltrim(MyTable.MyFirstField) + ' ' + alltrim(MyTable.MySecondField)),id" evaluates to such a field list, well then evaluate it yourself and set the Rowsource to the result of the expression, not to the expression itself.

Code: 
lcRowSource2 = alltrim(alltrim(MyTable.MyFirstField) + ',' + alltrim(MyTable.MySecondField))+",id"

I still think the first field has to be tablename.fieldname.


Chriss
 
Upvote 0 Downvote
Just to clarify my previous comment ...

Suppose that MyTable is positioned at the first record. And suppose that that record contains [tt]Alfreds[/tt] in the first field, and [tt]Futterkiste[/tt] in the second field. Then the RowSource [tt]alltrim(alltrim(MyTable.MyFirstField) + ' ' + alltrim(MyTable.MySecondField)),id[/tt] would evaluate to [tt]Alfreds Futterkiste, ID[/tt], which is surely not what you want.

And if the table happened to be positioned on another record, you would get a completely different RowSource.

It could be that you are confusing RowSource with ControlSource. If so, you might like to spend a few minutes looking at the Help pages for those two entries.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Chris, that's exactly what I have been saying. In my first post above, I showed an example of a valid RowSource for when the RowSourcetype is "fields".

What puzzles me is that Eugen didn't get a runtime error when he tried to set the RowSource to that particular expression.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Thanks Mike, our posts overlapped. I also think the ' ' has to be a comma.

And Eugen, since you first set a variable lcRowSource2, you could suspend and inspect it before setting it to the listbo rowsource.

Chriss
 
Upvote 0 Downvote
Unfortunately, I can't get the code that I spoke about because now it belongs to the client and it is too complicate to get it from him, but I managed to reconstruct something similar.
Please, take a look.

Code: 
PUBLIC oform1

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

DEFINE CLASS form1 AS form
	BorderStyle = 2
	Height = 263
	Width = 529
	DoCreate = .T.
	AutoCenter = .T.
	Caption = "Form1"
	Name = "Form1"

	ADD OBJECT list1 AS listbox WITH ;
		Height = 246, ;
		Left = 6, ;
		Top = 8, ;
		Width = 258, ;
		Name = "List1"

	ADD OBJECT command1 AS commandbutton WITH ;
		Top = 211, ;
		Left = 318, ;
		Height = 43, ;
		Width = 169, ;
		Caption = "Release", ;
		Name = "Command1"

	ADD OBJECT text1 AS textbox WITH ;
		Height = 187, ;
		Left = 270, ;
		Top = 8, ;
		Width = 252, ;
		Name = "Text1"

	PROCEDURE RefreshTextBox
		Thisform.Text1.Value = Chr(215) + Chr(215) + Chr(215) + " List values " + Chr(215) + Chr(215) + Chr(215) + Chr(13) ;
			+ "List Item: " + Alltrim(Thisform.List1.List(Thisform.List1.ListItemId)) + Chr(13) ;
			+ "List Item ID: " + Alltrim(Thisform.List1.Value) + Chr(13) + Chr(13) ;
			+ Chr(215) + Chr(215) + Chr(215) + " Table fields values " + Chr(215) + Chr(215) + Chr(215) + Chr(13) ;
			+ "Table First Name: " + Alltrim(crTemp.first_name) + Chr(13) ;
			+ "Table Last Name: " + Alltrim(crTemp.last_name) + Chr(13) ;
			+ "Table ID: " + Alltrim(Str(crTemp.Id)) + Chr(13) ;
			+ "Table RecNo: " + Alltrim(Str(Recno()))

		ThisForm.Text1.Refresh
	ENDPROC

	PROCEDURE Init
		Close Databases All
		Create Cursor crTemp (Id N(5), first_name C(20), last_name C(30))

		Insert Into crTemp (Id, first_name, last_name) Values (1, "John", "Doe")
		Insert Into crTemp (Id, first_name, last_name) Values (2, "Crazy", "Horse")
		Insert Into crTemp (Id, first_name, last_name) Values (3, "Rita", "Dotson")
		Insert Into crTemp (Id, first_name, last_name) Values (4, "Kane", "Goodwin")
		Insert Into crTemp (Id, first_name, last_name) Values (5, "Estelle", "Mcleod")
		Insert Into crTemp (Id, first_name, last_name) Values (6, "Gabriella", "Beltran")
		Insert Into crTemp (Id, first_name, last_name) Values (7, "Pheobe", "Morales")

		Index On Alltrim(first_name) + Alltrim(last_name) Tag Name Ascending

		LcRowSource = "Alltrim(Alltrim(crTemp.first_name) + ' ' + Alltrim(crTemp.last_name)), id"
		With Thisform
			.List1.ColumnCount = 1
			.List1.BoundColumn = 2
			.List1.RowSourceType = 6
			.List1.RowSource = LcRowSource
			.List1.SelectedID(1) = .T.
			.RefreshTextBox()
		Endwith
	ENDPROC

	PROCEDURE list1.InteractiveChange
		Thisform.RefreshTextBox()
	ENDPROC

	PROCEDURE command1.Click
		ThisForm.Release 
	ENDPROC

ENDDEFINE

Eugen
 
Upvote 0 Downvote
Ok, thanks for that demonstration.

I think we misunderstood your problem. You see the first record in the listbox, instead of the current.
Well, is there a controlsource determining the selection? Or are you using SelectedID, as per your example?

Chriss
 
Upvote 0 Downvote
The form where I must use this ListBox is a complex one (it has, maybe, more then 150 controls, a lot of custom methods and properties, tables, cursors and so on). I can't divide it into more forms because of client's express requirement. After a lot of optimizations, it works smoothness and very decent as time of execution.

The only thing that gives me headaches is this ListBox. The items, that populate it, is filtered based on some complex previously choices.

After the user choose one item from this ListBox I must open another form that will permit him to edit a record. That record is identified by an ID that is pass as parameter to the new form. The ID is "picked" from the same table from where are collected values for ListBox.

All happens with no issues if I have something like "MyTable.MyFirstField, id" as RowSource for ListBox (as is documented in Help file and you correctly said).

But if I use something like "alltrim(alltrim(MyTable.MyFirstField) + ' ' + alltrim(MyTable.MySecondField)),id" (undocumented and, possible, incorrect) the ID (parameter that I pass to the second form) has the value for the first record from the filtered table NOT for the current one.

Settings for ListBox are same that I used in example. As you can saw in my example, after moving into ListBox, the position of the record's pointer seems to be correct.

As I said, I found a way to avoid the problem, but I’m curious about the facts that can generate such behavior.

All the bests,
Eugen
 
Upvote 0 Downvote
Eugen,

I must admit that I am a bit confused about this. I have run your code, and it seems to work correctly. But I can't understand how you can set the RowSource to an expression. With a RowSourceType 6, you are supposed to set the RowSource to a list of fields (with the first, and only the first, preceded by an alias). You are not doing that, and yet the listbox still displays the desired values.

Can you tell us how you knew to set the RowSource in this way? Did you find an example somewhere? Or some documentation?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
The old application that I previously reminded was written in FPD 2.6. And I learned FPD from Help and from a borrowed book (I don't remind title or author). At that moment the internet was very, very expensive, I had few incomes and surely, I couldn't pay for it. So, if I read somewhere, I think that I read in FPD 2.6 Help or in that book (which was mainly translated from Help). If I didn't read anywhere then I didn't know that I make a mistake and I tried it. 😊

The truth is that I'm very carefully with user interface. I think that it must be pleasant, friendly, easy readably and more... human readably. That's why, perhaps, I tried something else.

But that’s are story or... history. Please take a look at the code below. I put in RowSource fields from two tables and a nested IIF. Why it's working, I don't know. But it seems to work.

Code: 
PUBLIC oform1

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

DEFINE CLASS form1 AS form

	BorderStyle = 2
	Height = 263
	Width = 529
	DoCreate = .T.
	AutoCenter = .T.
	Caption = "Form1"
	Name = "Form1"

	ADD OBJECT list1 AS listbox WITH ;
		Height = 246, ;
		Left = 6, ;
		Top = 8, ;
		Width = 234, ;
		Name = "List1"

	ADD OBJECT command1 AS commandbutton WITH ;
		Top = 222, ;
		Left = 302, ;
		Height = 32, ;
		Width = 169, ;
		Caption = "Release", ;
		Name = "Command1"

	ADD OBJECT edit1 AS editbox WITH ;
		Height = 208, ;
		Left = 246, ;
		ReadOnly = .T., ;
		ScrollBars = 0, ;
		Top = 8, ;
		Width = 276, ;
		Name = "Edit1"

	PROCEDURE RefreshEditBox
		Thisform.Edit1.Value = Chr(215) + Chr(215) + Chr(215) + " List values " + Chr(215) + Chr(215) + Chr(215) + Chr(13) ;
			+ "List Item: " + Alltrim(Thisform.List1.List(Thisform.List1.ListItemId)) + Chr(13) ;
			+ "List Item ID: " + Alltrim(Thisform.List1.Value) + Chr(13) + Chr(13) ;
			+ Chr(215) + Chr(215) + Chr(215) + " Table fields values " + Chr(215) + Chr(215) + Chr(215) + Chr(13) ;
			+ "crName: First Name = " + Alltrim(crName.first_name) + Chr(13) ;
			+ "crName: Last Name = " + Alltrim(crName.last_name) + Chr(13) ;
			+ "crName: ID = " + Alltrim(Str(crName.Id)) + Chr(13) ;
			+ "crName: RecNo = " + Alltrim(Str(Recno())) + Chr(13) + Chr(13) ;
			+ "crCountry: ID = " + Alltrim(Str(crCountry.Id)) + Chr(13) ;
			+ "crCountry: Country = " + Alltrim(crCountry.country) + Chr(13) ;
			+ "crCountry: Continent (for IIF) = " + Alltrim(Str(crCountry.continent))

		Thisform.Edit1.Refresh
	ENDPROC

	PROCEDURE Init
		Close Databases All
		Create Cursor crName (Id N(5), first_name C(20), last_name C(30))

		Insert Into crName (Id, first_name, last_name) Values (1, "John", "Doe")
		Insert Into crName (Id, first_name, last_name) Values (2, "Crazy", "Horse")
		Insert Into crName (Id, first_name, last_name) Values (3, "Rita", "Dotson")
		Insert Into crName (Id, first_name, last_name) Values (4, "Tin", "Man")
		Insert Into crName (Id, first_name, last_name) Values (5, "Estelle", "Mcleod")
		Insert Into crName (Id, first_name, last_name) Values (6, "Maria", "Popescu")
		Insert Into crName (Id, first_name, last_name) Values (7, "Rodriguez", "Morales")

		Index On Id Tag Id
		Index On Alltrim(first_name) + Alltrim(last_name) Tag Name Ascending Additive

		Create Cursor crCountry (Id N(5), country C(20), continent N(5))

		Insert Into crCountry (Id, country, continent) Values (1, "Great Britain", 1)
		Insert Into crCountry (Id, country, continent) Values (2, "U.S.A.", 2)
		Insert Into crCountry (Id, country, continent) Values (3, "Germany", 1)
		Insert Into crCountry (Id, country, continent) Values (4, "Land of Oz", 3)
		Insert Into crCountry (Id, country, continent) Values (5, "France", 1)
		Insert Into crCountry (Id, country, continent) Values (6, "Romania", 1)
		Insert Into crCountry (Id, country, continent) Values (7, "Spain", 1)

		Index On Id Tag Id

		Select crName
		Set Relation To Id Into crCountry
		Locate

		LcRowSource = "Alltrim(Alltrim(crName.first_name) + ' ' + Alltrim(crName.last_name)) + ";
			+ "' -> ' + Alltrim(crCountry.country) + ': ' + ";
			+ "IIF(crCountry.continent = 1, 'Europe', IIF(crCountry.continent = 2, 'America', 'Magical Land')) ";
			+ ", id"

		With Thisform
			.List1.ColumnCount = 1
			.List1.BoundColumn = 2
			.List1.RowSourceType = 6
			.List1.RowSource = LcRowSource
			.List1.SelectedID(1) = .T.
			.RefreshEditBox()
		Endwith
	ENDPROC

	PROCEDURE list1.InteractiveChange
		Thisform.RefreshEditBox()
	ENDPROC

	PROCEDURE command1.Click
		ThisForm.Release 
	ENDPROC

ENDDEFINE
 
Upvote 0 Downvote
Well, while it is a surprising feature, nobody needs it as you could also always do a query to have such expressions executed and collect the results in the fields of a cursor or array you bind to the control.

I guess your problem comes from extensive use of multiple such things, but mainly you GO TOP in the records, don't you? A thing that does that is having a quirky definition of rowsource and controlsource.

Anyway it is, your problem seems to be in the positioninig of the record pointer of some workarea. If you don't find it, a solution is to define a few more workareas (I know there only where 10 and later 15, now they are as good as unbound) that you control independently.

Without being in the code and rules and working of a form, we could only stab around in the dark. RELATIONs can move record pointers, of course. Locates, Seeks, anything. As you talk of a complex form it reminds me of the spaghetticode you can get if something has a long history and the art of programming is in the decouplling of things, not in coupling them too tightly.

Chriss
 
Upvote 0 Downvote
Eugen,

I would argue against your use of SET RELATION here. Better to create a single cursor to hold exactly the data you want to show in the listbox - and in the order in which you want to show it - and to use that cursor as your RowSource.

And the easiest way to do that is to set the RowSourceType to 2, and set the RowSource to the name of the cursor.

For example, suppose you have a cursor named csrName, which contains two fields. The first field has the fullname, in the format ALLLTRIM(FirstName) + " " + ALLTRIM(LastName). The second field has the ID. Then, to populate the listbox, all you need to do is to set the RowSoureType to 2, and the Rowsource to "csrName". If you don't want the ID to be visible, use the ColumnWidths property to hide it. That's really all you need.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sashaDG
  • Question
Combobox in grid with different RowSource for each row
Replies
15
Views
662
sashaDG
sashaDG
Bryan - Gendev
  • Locked
  • Question
Contents of a listbox
Replies
9
Views
100
Chris Miller
Chris Miller
german12
  • Locked
  • Question
How to create and manage a listbox 2
Replies
6
Views
152
german12
german12
german12
  • Locked
  • Question
Company Listbox to fill with companies worldwide listed at stock markets 4
Replies
14
Views
163
TamarGranor
TamarGranor
SitesMasstec
  • Locked
  • Question
Forms: Parent & Child 1
Replies
10
Views
149
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top