Link to home
Start Free TrialLog in
Avatar of kennys
kennys

asked on

Records in a Listbox

An absolute beginner asks:-

I have a database containing 5 fields.
Can I display in one listbox the selected record  from
field1,field2,field3,field4.field5
displayed like that.
ASKER CERTIFIED SOLUTION
Avatar of mcrider
mcrider

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ture
ture

kennys,

Here is an alternative solution.
The following setup:

A form with one data control (data1) and one listbox (list1).

Settings for data1:
Databasename: C:\Northwind.mdb
Recordsource: Products

Double-click on the form and add this code:

Private Sub Form_activate()
  Data1.Recordset.MoveFirst
  Do Until Data1.Recordset.EOF
    List1.AddItem _
    Data1.Recordset.Fields("ProductId") & ", " & _
    Data1.Recordset.Fields("ProductName") & ", " & _
    Data1.Recordset.Fields("UnitPrice")
    Data1.Recordset.MoveNext
  Loop
End Sub

Ture Magnusson
Karlstad, Sweden
Avatar of kennys

ASKER

I could not get either of the two examples to work !!
I enclose my code which opens 5 list boxes and dislays the records
When I tried the second examle the underscore would not be accepted.
I repeat I am a beginner

Private Sub mnuList_Click()
'Call procedure "Showlists"
showlists

'When list is selected from the menu
'clear list boxes
lstCdId.Clear
lstTitle.Clear
lstArtist.Clear
lstDistr.Clear
lstTrack.Clear

                     'Path to database
Set Database = OpenDatabase(App.Path & "\cds.mdb")
                                        'Table
Set Recordset = Database.OpenRecordset("SELECT * FROM CDdata")

Data1.Recordset.MoveFirst
'as the loop moves down the records
'read the records into the each listbox
Do Until Recordset.EOF 'Loop until no more records
    lstCdId.AddItem Recordset.Fields("cd Id").Value
    lstTitle.AddItem Recordset.Fields("cd Title").Value
    lstArtist.AddItem Recordset.Fields("Artist").Value
    lstDistr.AddItem Recordset.Fields("Distributer").Value
    lstTrack.AddItem Recordset.Fields("Track").Value
  Recordset.MoveNext
Loop
Recordset.Close 'Close database when finished
Database.Close
End Sub
A couple of notes on technique in your code :

1. don't name your objects "database" and "recordset". Its too easy to visually confuse them with the VB objects of the same name. Name them something like "dbCDInfo", and "rsCDInfo".

2. Don't include imbedded spaces in your field names. Let capitalization changes separate words included in the field name.

Also, Win98 Plus comes with a database that does substantially what it looks like you're trying to do above ... plus ... it will read the CD in your drive, go to the web, find the CD on the web, and enter all of this data into the database for you. (Just thought you might want to know about that.)
First, I noticed in the code you provided that you have database field names that have spaces in them... For example: "cd Id" and "cd Title".

I consistantly have problems when I do this... Especially when trying to use the field in SELECT clauses.

I suggest you replace the spaces in your field names with underscores, or completely remove the spaces.

Once you've done that, this should work, assuming you have replaced the spaces with nothing ("cd Id" becomes ""cdId", "cd Title" becomes "cdTitle").

This routine will put all of the fields in the lstCdId listbox delimited by "," so make the listbox wide enough to see the data when you test it.

Cheers!

THE CODE:


Private Sub mnuList_Click()
'Call procedure "Showlists"
showlists

'When list is selected from the menu
'clear list boxes
lstCdId.Clear

                     'Path to database
Set Database = OpenDatabase(App.Path & "\cds.mdb")
                                        'Table
Set Recordset = Database.OpenRecordset("SELECT * FROM CDdata")

With Recordset
   .MoveLast
   MyTotalCount = .RecordCount
   .MoveFirst
   For iVal = 0 To MyTotalCount - 1    ' WALK THROUGH ALL THE RECORDS RETURNED
      lstCdId.AddItem !cdId + ", " + !cdTitle + ", " + !Artist + ", " + !Distributer + ", " + !Track
      .MoveNext
   Next iVal
End With

Recordset.Close 'Close database when finished
Database.Close
End Sub

Avatar of kennys

ASKER

Many thanks for your time and patience
kennys,

Did that solve your problem?
Avatar of kennys

ASKER

Yes. Sorry I didn't mention that !!
Then, kennys, you should accept and grade mcrider's question so that he receives his well-earned points...

/Ture