• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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.
0
kennys
Asked:
kennys
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now