How to assign all fields returned by a SQL SELECT statement to an array?

[jesse07]

Hello, I have a listbox that list the names and phone numbers of clients. I want to look up in a large table (84 fields) off of the phone number from the listbox. My question is how do I assign all 84 fields to an array from a table, so they can be easily used to fill out variuos reports.
[code=vb]
Dim strSearch As String
Dim strSQL As String

strSearch = Me.lstCompanies .Column(5) ' set strSearch = to phone number

strSQL = "SELECT * FROM Table1 WHERE contact_phone= strSearch;"

DoCmd.RunSQL strSQL
[/code]

This should select all records from the table, but I want to save them into an array(1), array(2)... all the way up to 84.

[Atli]

Hi Jesse.

This being more of a VB question than a MySQL question, I am going to move this over the the Visual Basic forums, where I believe your are more likely to get an answer.

I would also ask that you remember to post any code within [code] tags. Even a small code snippet, as the one you posted above, is much harder to read without them.

Moderator

[jesse07]

I am still stuck on this. If anyone could help me out, It would help me a lot.

Thanks

[BlackMustard]

Hello, I have a listbox that list the names and phone numbers of clients. I want to look up in a large table (84 fields) off of the phone number from the listbox. My question is how do I assign all 84 fields to an array from a table, so they can be easily used to fill out variuos reports.
[code=vb]
Dim strSearch As String
Dim strSQL As String

strSearch = Me.lstCompanies .Column(5) ' set strSearch = to phone number

strSQL = "SELECT * FROM Table1 WHERE contact_phone= strSearch;"

DoCmd.RunSQL strSQL
[/code]

This should select all records from the table, but I want to save them into an array(1), array(2)... all the way up to 84.

a way easier way to do it is to use a RecordSet. a RecordSet is basically the same thing as a multi-dimensional array, but with a bunch of methods and properties added that makes it ideal to use for database data handling. with a RecordSet, you could also select many lines at a time from the table, and still be able to keep track of it all. to use a recordset, append the below sample code to fit your needs:

In your code module
[code=vb]
Dim adoConn As ADODB.Connectio n
Dim adoRecSet as ADODB.Recordset

Public Sub GetContact()
Set adoConn = New ADODB.Connectio n
adoConn.Connect ionstring = "insert your connection string here"
adoConn.Open

Set adoRecSet = new ADODB.Recordset
Dim strSearch As String
Dim strSQL As String

strSearch = Me.lstCompanies .Column(5) ' set strSearch = to phone number

adoRecSet.Open strSQL = "SELECT * FROM Table1 WHERE contact_phone=' " & strSearch & "';"

adoRecSet.MoveF irst

' You can now access the columns in the first row by using adoRecSet(intCo lumnNumber). Example (pretending that the name is in the first column):
Dim strName as String
strName = adoRecSet(1)
' You can also use the column names from the DB, for example:
strName = adoRecSet("cont act_name")

' If you wish to change a record, you can do this by assigning variables directly to the fields, and then use the Update method
adoRecSet("cont act_phone") = "+1 (234) 567-8900" ' Assigns a new phone number to the contact
adoRecSet.Updat e ' Updates the database table to match the Recordset

' To move to the other records, use the following commands:
adoRecSet.MoveN ext
adoRecSet.MoveP revious
adoRecSet.MoveF irst
adoRecSet.MoveL ast

' Don't forget to close the Recordset, the DB Connection and to dispose objects when you're done
adoRecSet.Close
adoConn.Close

Set adoRecSet = Nothing
Set adoConn = Nothing
End Sub[/code]

please note that i haven't tested this sample code - i just wrote it as i recall using it long ago from the top of my head - so there might be some bugs in it. but i think you can figure out how to use it by some trial and error...