Populate Listbox w/ field values from database table

[sm43]

Hi,
How can I populate a Listbox with values retrieved from a column in a database-table.. for e.g, by tying the listbox to an ado recordsource or something..which property of the listbox do i set? how can this be done at design time, without writing vb code..


Thanks.

Saad

 

[robotman757]

The easiest way you could do this is set the property of the field to Lookup Wizard. The wizard will ask if you want to use a table or type in the values. Just tell it what table and field holds the data you want and voila!
Then you should be able to set the listbox on the form just fine. No code needed this way.

Dan Walker
robotman757@cox.net

 

[sm43]

Wait, is this method going to work in Visual Basic 6. I don't see any wizard Lookup ellipses associated with any of the property of the Listbox. My database is in SQL Server. I'm trying to associate my listbox in VB 6 to table-field values from this database.

There's one other problem that I've noticed. I bind the listbox to a field in a database-field - i.e. the values selected on the Listbox will go into a database-table field - and set the MultiSelect property to - Simple or Extended - I then run the application but the Multiselect value is set back to None.. I need Multiselect enabled on this listbox - I think it's doing it because I have it bound to an adodc datasource at design time.. how can I enable Multiselect?

Thanks!

Saad

 

[robotman757]

I apologize, but I thought you were using Access and just referring to a table. If this is the case, you setup any field using design view of the table and then set the property of the field by clicking next to it. The last choice should be lookup wizard or lookup list. When you choose that, Access runs a little wizard and asks what table to reference the data, and you can choose the table that will store the values you want. It could just be one table with a single field in it. The form would have the listbox, and it's control source would be the field with the lookup list. I could try to write an example and post it on my website later if you want. If this is not what you are referring to, I am sorry.

 

[CCLINT]

For ADO:

Set a reference to the DataList control...not the ListBox:

PROJECT|COMPONENTS "Microsoft DataList Controls 6.0 (OLEDB)"

Add the control to a form.

Set the following properties:

Set DataList1.DataSource = Adodc1_ToBindOn
DataList1.DataField = "SomeFieldToBindOn"

Set DataList1.RowSource = Adodc1_ForListItems
DataList1.ListField = "SomeFieldToShowInList" [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[sm43]

Dan,
I'm not using Access for the development in this particular case. My database is in Sql Server 2000, and my front-end is Visual Basic 6. I've actually done all this successfully in Microsoft Access but now my job is converting it all into SQL Server and Visual Basic 6. The SQL Server part was easy.. but the VBA->Visual Basic conversion wasn't quite so simple because the Listboxes are different in the versions of the language. In short, the Listbox in VB 6 doesn't let you specify a Row Source property and let you populate it database values at design-time.

CCLINT, thanks for your reply. I've actually successfully managed to use a DataList to populate it with values from a field and also bound it to a field in the database. However, there is one problem. I cannot get it to allow Multi-selection. It doesn't have any property with which you can specify multiselect behavior. Now, meanwhile, I've been working with a Listbox control, and have populated it retrieving data in code. A Listbox DOES have a Multiselect property - and I set it to 'Simple' or 'Extended'. But no matter, which of these I set it to, when I run the program, it always reverts to Simple.. I'm confused, why is that. And how do I avoid this behavior? Ideally, ofcourse, it would be better if a way was found to allow muliselect in a DataList, as a Datalist would be much easier to maintain (with a Listbox I would have to worry about updating it, everytime a new value was added - during program execution - to the database-field where this Listbox is getting its values from..


Thank you.

Saad

 

[CCLINT]

Generally, you cannot bind a list box to a field and then be able to multi-select.

When multi selecting with a bound control, which selection should be used for the bound field?
This, of course, would not make sense.

Either you need multi-select and no bound field, or single select and a bound field is possible.

If you remove the bound field, then you should be able to multi select - of course.

This is the idea behind a bound control.

I would re-think what needs to be accomplished here.

While MS apps allow this, I have never investigated as to why, and what happens when a multi selection is made (maybe then the list box doesn't bind the field to a selection at that moment, or only the first selected entry?)

If you still want this possibility, then you are going to have to use the list box from the "Microsoft Forms 2.0" control (cannot distribute the control, but most, if not all, systems should have it already, and if not, they can load it freely)
This is the same list box used by ACCESS. Here, you will be able to do what you want. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[sm43]

CCLint, THanks. Yeah, I did find the Microsoft Forms 2.0 component. I think it's installed with Microsoft Office, or when you select to install Visual Database Objects in your Visual Basic 6 installation program. I did have to browse for it in Components Manager as it wasn't appearing automatically in the list. The component, when included in the project, gave second version of nearly every default visual basic control (ones that appear at startup at the creation of every new project ; Textbox, Listbox, Label, comboBox etc). The Listbox that Forms 2.0 gave me has some additional properties - closer to the Access control - but it doesn't have the RowSource property (the one that lets you define a recordsource for populating your ListBox).

Referring your point about Multiselection and Bound fields, Well it's strange, but in Access when you bind a Listbox to a field (by specifying ControlSource property), and set MultiSelect to Simple or Extended, the Multiselect property sticks, it doesn't switch back to None. I would just imagine, and I thought this is how it worked, that the multiselections would just go into the Bound field as comma or semi-colon seperated values. If I'm not recalling incorrectly, I think I once got that to occur in Access - then I later ran into some other snag and had to code the whole thing. I think I'll just have to explore the path of loading and extracting the values from the Listbox in-code, for the sake of storing them in the database-field. I'm not sure though, what events I would have to use, and how I would cope with the navigating forward and backward through records, I can't seem to think of a way to do it without writing the same code in some different events - basically the navigating Forward and Backward buttons' Click Events and some event that triggers just after the Form has fully loaded, and the Save button click event for storing the values into the database-field..


Saad

 

[CCLINT]

I would load the values into a list box manually using a recordset object, looping through the records, and using the "Add" method of the list box.
Put this code in a seperate proceedure.
Call it, when the records for the list need to be re-defined, either in the click event of the navigation buttons and right after the recordset is (re)opened or after you requery the rs,

or, in the ADO MoveComplete event of the recordset object.
For this event it would be good to have an additional form/class level private boolean variable to prevent the list box record loading proceedure from being called when not needed (for example, if a data grid is bound to the rs, the event will be called when the rs is opened, and after the grid is loaded)
To get this event, use a form level recordset object variable for the main records, and declare it using WithEvents.
Then, in the code window you will be able to select the rs object in the left dropdown, and it's events in the right dropdown. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[PraveenMenon]

Do while not rst.eof
List1.Additem rst.Fileds("CustName&quot
rst.movenext
loop All the Best
Praveen Menon
pcmin@rediffmail.com