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.
kennysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mcriderCommented:
Here you go... Just replace !Fieldx in the code below with your field names from the database.

Cheers!

THE CODE:

'ASSUMING YOU ALREADY HAVE THE DATABASE AND RECORDSET OPEN...
With MyRecSet
   .MoveLast
   MyTotalCount = .RecordCount
   .MoveFirst
   For iVal = 0 To MyTotalCount - 1    ' WALK THROUGH ALL THE RECORDS RETURNED
      List.AddItem !Field1 + ", " + !Field2 + ", " + !Field3 + ", " + !Field4 + ", " + !Field5
      .MoveNext
   Next iVal
End With
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tureCommented:
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
0
kennysAuthor Commented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

vettrangerCommented:
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.)
0
mcriderCommented:
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

0
kennysAuthor Commented:
Many thanks for your time and patience
0
mcriderCommented:
kennys,

Did that solve your problem?
0
kennysAuthor Commented:
Yes. Sorry I didn't mention that !!
0
tureCommented:
Then, kennys, you should accept and grade mcrider's question so that he receives his well-earned points...

/Ture
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.