DB - Array - Listbox - Field.Caption

[Chrissie]

Hi Have the following code in a Function...

Dim db As Database
Dim rs As Recordset
Dim SQLString As String
Dim ArtType As String

Set db = OpenDatabase("bla.mdb")
SQLString = "SELECT Product.Type, Products.Number, Products.Description, Products.City, Products.Author From Products WHERE ((Products.Type) = '" & ArtType & "');"
Set rs = db.OpenRecordset(SQLString)
If rs.BOF = True And rs.EOF = True Then
MsgBox Error
rs.Close
db.Close
End if

I want rs.Fields("Type"), rs.Fields("Number"), rs.Fields("Description"), rs.Fields("City"), rs.Fields("Author") to be loaded into an array...

I want rs.Fields("Type") to be added into a Listbox with the number of the array...Whenever I click on one of the items in the listbox...I want to see thee rs.Fields("Number"), description, city en author in 4 labels..with Label.Caption...

Can anyone help?

[khalik_ash]

use collection loop till EOF and add to the collection

on listbox change event load ur label collection.item(listbox1.listindex)

[Chrissie]

Can I make my array so I can get these...

MyCompleteArray(listIndex,Type,Number,Description,City,Author)

get the info out of the array...as follows...
MyCompleteArray(0, Number) = rs.Fields("Number") 'first Record number
MyCompleteArray(0, Author) = rs.Fields("Author") 'first Record Author

And so on...

Or is it better to create seperate arrays for every record?
NumberArray() AuthorArray() etc...

[khalik_ash]

Can.... collection are better if u wish to use arrays nothing wrong with it.

[Chrissie]

Can you explain how the Collection works?

[khalik_ash]

Originally posted by Chrissie
Can you explain how the Collection works?

Best explanation u can get it at MSDN..

A named group of related components. For example, a collection named Tax Preparation Objects might contain objects such as EndOfYear, RoyaltyCalc, and ExemptionCalc.


u can have diffrent datatype..

link

[khalik_ash]

MSDN

The Visual Basic Collection Object


A collection is a way of grouping a set of related items. Collections are used in Visual Basic to keep track of many things, such as the loaded forms in your program (the Forms collection), or all the controls on a form (the Controls collection).

Visual Basic provides the generic Collection class to give you the ability to define your own collections. You can create as many Collection objects — that is, instances of the Collection class — as you need. You can use Collection objects as the basis for your own collection classes and object models, as discussed in "Creating Your Own Collection Classes" and "Object Models" later in this chapter.

For example, collections are a good way to keep track of multiple forms. "Multiple Document Interface (MDI) Applications" in "Creating a User Interface" discusses applications in which the user can open any number of document windows. The following code fragment shows how you might use the Add method of a collection object to keep a list of MDI child windows the user has created. This code assumes that you have a form named mdiDocument, whose MDIChild property is set to True.

' Module-level collection in the parent MDIForm.
Public colDocuments As New Collection

' Code for creating a new MDI child document form.
Private Sub mnuFileNew()
Dim f As New mdiDocument
Static intDocumentNumber As Integer
intDocumentNumber = intDocumentNumber + 1
' The following line creates the form.
f.Caption = "Document" & intDocumentNumber
' Add the object reference to the collection.
colDocuments.Add f
f.Show
End Sub

The colDocuments collection acts like a subset of the built-in Forms collection, containing only instances of the form mdiDocument. The size of the collection is adjusted automatically as each new form is added. You can use For Each ... Next to iterate through the collection. If you want to give the form a key by which it can be retrieved, you can supply a text string as the second parameter of the Add method, as described later in this section.

The New keyword in the declaration for the variable colDocuments causes a Collection object to be created the first time the variable is referred to in code. Because Collection is a class, rather than a data type, you must create an instance of it and keep a reference to that instance (object) in a variable.

Like any other object, a Collection object will be destroyed when the last variable that contains a reference to it is set to Nothing or goes out of scope. All the object references it contains will be released. For this reason, the variable colDocuments is declared in the parent MDIForm, so that it exists throughout the life of the program.

Note If you use a collection to keep track of forms, use the collection's Remove method to delete the object reference from the collection after you unload the form. You cannot reclaim the memory the form was using as long as a reference to the form still exists, and the reference the Collection object is holding is just as good as a reference in an object variable.

What's a Collection Object Made Of?
A Collection object stores each item in a Variant. Thus the list of things you can add to a Collection object is the same as the list of things that can be stored in a Variant. This include standard data types, objects, and arrays — but not user-defined types.

Variants always take up 16 bytes, no matter what's stored in them, so using a Collection object is not as efficient as using arrays. However, you never have to ReDim a Collection object, which results in much cleaner, more maintainable code. In addition, Collection objects have extremely fast look-ups by key, which arrays do not.

Note To be precise, a Variant always takes up 16 bytes even if the data are actually stored elsewhere. For example, if you assign a string or an array to a Variant, the Variant contains a pointer to a copy of the string or array data. Only 4 bytes of the Variant is used for the pointer on 32-bit systems, and none of the data is actually inside the Variant.

If you store an object, the Variant contains the object reference, just as an object variable would. As with strings and arrays, only 4 bytes of the Variant are being used.

Numeric data types are stored inside the Variant. Regardless of the data type, the Variant still takes up 16 bytes.

Despite the size of Variants, there will be many cases where it makes sense to use a Collection object to store all of the data types listed above. Just be aware of the tradeoff you're making: Collection objects allow you to write very clean, maintainable code — at the cost of storing items in Variants.

Properties and Methods of the Collection Object
Each Collection object comes with properties and methods you can use to insert, delete, and retrieve the items in the collection.

Property or method Description
Add method Add items to the collection.
Count property Return the number of items in the collection. Read-only.
Item method Return an item, by index or by key.
Remove method Delete an item from the collection, by index or by key.


These properties and methods provide only the most basic services for collections. For example, the Add method cannot check the type of object being added to a collection, to ensure that the collection contains only one kind of object. You can provide more robust functionality — and additional properties, methods, and events — by creating your own collection class, as described in "Creating Your Own Collection Classes" later in this chapter.

The basic services of adding, deleting, and retrieving from a collection depend on keys and indexes. A key is String value. It could be a name, a driver's license number, a social security number, or simply an Integer converted to a String. The Add method allows you to associate a key with an item, as described later in this section.

An index is a Long between one (1) and the number of items in the collection. You can control the initial value of an item's index, using the before and after named parameters, but its value may change as other items are added and deleted.

Note A collection whose index begins at 1 is called one-based, as explained in "Collections in Visual Basic."

You can use the index to iterate over the items in a collection. For example, the following code shows two ways to give all the employees in a collection of Employee objects a 10 percent raise, assuming that the variable mcolEmployees contains a reference to a Collection object.

Dim lngCt As Long
For lngCt = 1 To mcolEmployees.Count
mcolEmployees(lngCt).Rate = _
mcolEmployees(lngCt).Rate * 1.1
Next

Dim emp As Employee
For Each emp In mcolEmployees
emp.Rate = emp.Rate * 1.1
Next

Tip For better performance, use For Each to iterate over the items in a Collection object. For Each is significantly faster than iterating with the index. This is not true of all collection implementations — it's dependent on the way the collection stores data internally.

Adding Items to a Collection
Use the Add method to add an item to a collection. The syntax is:

Sub Add (item As Variant [, key As Variant] [, before As Variant]

[, after As Variant] )

For example, to add a work order object to a collection of work orders using the work order's ID property as the key, you can write:

colWorkOrders.Add woNew, woNew.ID

This assumes that the ID property is a String. If the property is a number (for example, a Long), use the CStr function to convert it to the String value required for keys:

colWorkOrders.Add woNew, CStr(woNew.ID)

The Add method supports named arguments. To add an item as the third element, you can write:

colWorkOrders.Add woNew, woNew.ID, after:=2

You can use the before and after named arguments to maintain an ordered collection of objects. For example, before:=1 inserts an item at the beginning of the collection, because Collection objects are one-based.

Deleting Items from a Collection
Use the Remove method to delete an item from a collection. The syntax is:

object.Remove index

The index argument can either be the position of the item you want to delete, or the item's key. If the key of the third element in a collection is "W017493," you can use either of these two statements to delete it:

colWorkOrders.Remove 3
-or-

colWorkOrders.Remove "W017493"

Retrieving Items from a Collection
Use the Item method to retrieve specific items from a collection. The syntax is:

[Set] variable = object.Item(index)

As with the Remove method, the index can be either the position in the collection, or the item's key. Using the same example as for the Remove method, either of these statements will retrieve the third element in the collection:

Set woCurrent = colWorkOrders.Item(3)
-or-

Set woCurrent = colWorkOrders.Item("W017493")

If you use whole numbers as keys, you must use the CStr function to convert them to strings before passing them to the Item or Remove methods. A Collection object always assumes that a whole number is an index.

Tip Don't let Collection objects decide whether a value you're passing is an index or a key. If you want a value to be interpreted as a key, and the variable that contains the value is anything but String, use CStr to convert it. If you want a value to be interpreted as an index, and the variable that contains the value is not one of the integer data types, use CLng to convert it.

Item Is the Default Method
The Item method is the default method for a Collection object, so you can omit it when you access an item in a collection. Thus the previous code example could also be written:

Set woCurrent = colWorkOrders(3)
-or-
Set woCurrent = colWorkOrders("W017493")

[Chrissie]

Thanks a lot...you're help is really appreciated