Array Loading and Referencing

What would be the best(and most efficient) way to do this? I have a dropdown list on my form and when the user clicks on an item, I want the description of the item in the list box to appear (in a textbox elsewhere on the form). I have all of the descriptions in a SQL Server database. (144 of them) I was thinking of reading the database on form load and putting the descriptions into an array or is there a better way to do this? I'm not too familiar with building and/or referencing arrays so any code would help greatly.

Thanks.

I do this exact thing in a few of my apps. Since I'm not an expert at using multi-dimensional arrays, i use an array of user-defined types. On form load i load in the descriptions and their actual values into the array. Then while populating the combobox, i set the itemdata value to the index of that item in the array. When the user clicks on an item, i grab the itemindex value out and then go to the array with that index and pull back the description. Here is a sample of what I'm doing:

'dimension the UDT
Type ItemClass
iTranslationCode as Integer
sDescription as string
sMidWestCode as string
sWestCoastCode as string
sLeasedPurchased as string
End Type

'global variable to hold the UDTs
public gudtItemClassCode() as ItemClass

'this builds the array of user defined type objects.
public Function LoadItemClassData() as Boolean
Dim sSQL as string
Dim rsTemp as rdoResultset
Dim sTemp as string
Dim i as Integer

sSQL = "SELECT * FROM sbcclass..cmr_TranslationCodes (nolock) ORDER BY TranslationCode"

set rsTemp = dbClass.OpenResultset(sSQL, rdOpenStatic, rdConcurReadOnly)

'load the array with the values for the ItemClassCode list box
i = 0
While Not rsTemp.EOF

ReDim Preserve gudtItemClassCode(i)

gudtItemClassCode(i).sDescription = rsTemp!Description
gudtItemClassCode(i).sWestCoastCode = rsTemp!ItemClassCodeWest
gudtItemClassCode(i).sMidWestCode = rsTemp!ItemClassCodeMidWest
gudtItemClassCode(i).iTranslationCode = rsTemp!TranslationCode
gudtItemClassCode(i).sLeasedPurchased = rsTemp!LeasedPurchased

rsTemp.MoveNext
i = i + 1
Wend

LoadExit:
on error resume next
rsTemp.Close
set rsTemp = nothing
Exit Function

handler:
MsgBox error & "Please contact your Catalog Administrator for assistance.", vbExclamation, "Load Item Class Data"
resume LoadExit
End Function

'In the load i call this routine to populate the combobox with the descriptions, and then give the itemdata value the key to the table from which the data comes.
public Sub LoadItemCode(cboBox as Control)
Dim iCounter as Integer

on error GoTo handler

for iCounter = 0 to UBound(gudtItemClassCode)
cboBox.AddItem gudtItemClassCode(iCounter).sDescription
cboBox.ItemData(cboBox.NewIndex) = gudtItemClassCode(iCounter).iTranslationCode
next iCounter

Exit Sub

handler:
MsgBox error
End Sub


'now when the user clicks on a item...
private Sub cboItemClass_Click()
'when they choose an item, use the index of the item to get the corresponding item in the
'array and then pull back the class code
txtItemClassCode.Text = GetItemClassMidWest(cboItemClass)
End Sub

public Function GetItemClassMidWest(cboBox as Control) as string
Dim iCounter as Integer

iCounter = cboBox.ListIndex
GetItemClassMidWest = gudtItemClassCode(iCounter).sMidWestCode

End Function




I hope this makes sense and helps.

John


John Pirkey
MCSD
www.ShallowWaterSystems.com

Ok, I put all of this code in, of course modified for my use. I do have some questions though as there are some new types I'm not familiar with.

I have put the Type ItemClass in a public module. Is this correct? Do I need to declare it as a Public Type? or is there such a thing?

Also, when I compile, I get an error on the line 'Dim rsTemp as rdoResultset' saying that 'User-defined type not defined'. What does this mean? I have put the Public Functions in a public module.

Lastly, what exactly gets passed when you pass a control (i.e. a listbox) to a function? Does the index get passed or do the actual text values get passed or both?

Sorry for asking questions but I want to make sure I understand this for future use.

Thanks.

the "user-defined type not defined" means that rdoResultSet isn't defined. You know how you did that Public Type in a module? That's a type. the rdoResultSet needs to be declared just like the one you did earlier, because it's not a pre-defined vb type.

1)If you're putting your Type in a seperate module, you need to declare it as Public.

2)rdoResultSet is analogous to The ADO or DAO Recordset type. If you're using ADP or DAO for you data access, then you need to modify the code to use your access method instead of RDO

3)When you pass an object to a procedure, you are passing the entire object (or some would say a pointer to the object) to the Procedure. You can see this by running the following code:

'In a BAS somewhere
public Sub DisplayContents(txtBox as TextBox)
MsgBox txtBox.Text
End Sub
'In your form
DisplayContents Text1



What was done here, was a reference to an object of the Class TextBox was passed to the procedure, the procedure accessed the Text property of the object that was passed to it and displayed the value in that property.

2) Getting back to this, I guess the question is how do I declare rsTemp? I'm confused on this.
Do I need to do this:
Type rdoResultset
FieldName as String
Description as String
End Type

If you're using ADO, you would declare it as ADODB.Recordset.

Dim rsTemp as ADODB.Recordset

Okay, the rdoResultset thing is because this code is using RDO instead ADO. Its large project and we havene't the time to convert it yet. You can just delete that line and replace rdoResultset with ADODB.Recordset so you can use that variable name for your recordset.

I also have the type declaration in a public module. I don't declare as anything just
Type Name
Properties...
End Type

Then declare your variable of that type.
Public GlobalVarOfTypeName as TypeName

When passing a control, a reference to the control is passed, so in the target procedure, i can get to any property of the control - text, index, listitems, listindex, visible, left, top, style - anything. You can sort of think of it as, VB creates a duplicate control and gives the function that control to play with. In reality, though, this is NOT the case. VB actually passed the memory pointer to the control to the function and then the function has access to the control. This is what's known as passing "ByRef" or by reference. I'm not sure what happens if you pass a control by value or "ByVal".

Hope this helps,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

Never thought to try it, but I would imagine that if you passed a control "ByVal" that VB would create an exact copy of the object, manipulate that copied and leave the actual control untouched. Modifying properties in the procedure would have no effect on the actual control. This would be a good idea if you wanted a "gauranteed read only" procedure.

Thanks, that was very helpful. One more thing now. In the following code, I am receiving an error on the Set rsTemp= line saying that 'Object required'. Where does the dbClass come into play here? Is that where its bombing out?

public Function LoadItemClassData() as Boolean
Dim sSQL as string
Dim rsTemp as ADODB.Recordset
Dim rsFld as Recordset
Dim sTemp as string
Dim i as Integer

sSQL = "SELECT * FROM ModelFieldDesc"
set rsTemp = dbClass.OpenResultset(sSQL, rdOpenStatic, rdConcurReadOnly)

'load the array with the values for the ItemClassCode list box
i = 0
While Not rsTemp.EOF
ReDim Preserve gudtItemClassCode(i)
gudtItemClassCode(i).sFieldName = rsTemp!FieldName
gudtItemClassCode(i).sDescription = rsTemp!Description
rsTemp.MoveNext
i = i + 1
Wend

End Function



Also, I presume that the '!' between rsTemp!FieldName is a designator for the fields? And what is the difference between using a rs.Open vs a rs.OpenResultSet?

Thanks, again for the help.

The object dbClass in this example is was declared somewhere else in the code. Modify your declaration section to declare an ADODB.Connection object (name it dbClass if you'd like). Change the "Set rsTemp=" line to set rsTemp = dbClass.Execute(sSQL)

Thanks Kyle - yes, the dbClass object was declared elsewhere in the project.

John Pirkey
MCSD
www.ShallowWaterSystems.com

That worked. But once again, I realized another problem. My listbox is sorted differently than when I arranged my array. Therefore, when I click on a value in the listbox, I get the description based on the index number and not the description for the item selected in the listbox. Is there a way I can pass the value(text) selected in the listbox to the function instead of the control? Or is there a way to search the array for the value of the listbox.text and bring back the description?

I hope this is clear.