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,field 4.field5
displayed like that.
I have a database containing 5 fields.
Can I display in one listbox the selected record from
field1,field2,field3,field
displayed like that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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("SE LECT * 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("Distribu ter").Valu e
lstTrack.AddItem Recordset.Fields("Track"). Value
Recordset.MoveNext
Loop
Recordset.Close 'Close database when finished
Database.Close
End Sub
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("SE
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")
lstDistr.AddItem Recordset.Fields("Distribu
lstTrack.AddItem Recordset.Fields("Track").
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.)
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("SE LECT * 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
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("SE
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
ASKER
Many thanks for your time and patience
kennys,
Did that solve your problem?
Did that solve your problem?
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
/Ture
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("Pr
Data1.Recordset.Fields("Pr
Data1.Recordset.Fields("Un
Data1.Recordset.MoveNext
Loop
End Sub
Ture Magnusson
Karlstad, Sweden