storing SQL results in a var

[shrive22]

Does anyone know how to store the results from an sql statement in vb code in a variable. The resluts from the query are going to change based upon what the user chooses on my form??

thanks

 

[Russell Hauf]

[no

 

[mdmilner]

You may shoot me for offering, but I'd (mis)read this original message and proposed a quick little reply for a completely different situation.

But, since I went and spent the 10 minutes, I'll throw it down right here in case anybody wants to use something similar.

The question I tried to answer was, how would you read the contents of an entire table into a variable via SQL and VBA. This is pretty basic stuff but like I said, since I just wrote it down from scratch, and *somebody* might need it, here ya go.

Public Function Readtbl()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim astrStore() As String
Dim x, intMax As Long    ' Counter

Set dbs = CurrentDb()

strSQL = "Select * from tblName"

Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
  .MoveLast
  intMax = .RecordCount
  ReDim astrStore(intMax)
  .MoveFirst
  x = 1
  Do Until rs.EOF
    astrStore(x) = .Fields(0).Value            'First Column is Zero
    'strarrStore(x) = !SpecificFieldName       'Normal way of referencing fields
    x = x + 1
    .MoveNext
  Loop
End With

Set rs = Nothing
Set dbs = Nothing
End Function