Access 2010: Display contents of multiple records to unbound controls in datasheet

Question

I'm using a dynamic pass-through query in Access 2010 to retrieve one or more records from a back-end database. After much trial and error, I plagiarized enough of the right code to retrieve the appropriate records and assign them to unbound text-boxes on my datasheet form during an OnLoad event. The only problem remaining is in displaying multiple records. I've verified that I AM retrieving multiple records, but the contents of each record's fields overwrite the previous values stored to the form's textbox controls, so I always end up with just a single record displayed in my datasheet when I expect to see anywhere from one to 10.

I'm sure it's a simple solution. Can someone please point it out to me?

Private Sub Form_Load()
    Dim sqlString As String
    sqlString = "SELECT Transmitter_ID, Receiver_ID, UTC_Date, Local_Date from Detections"
    If Not IsNull(Me.OpenArgs) Then
        sqlString = sqlString & " where " & OpenArgs
    End If

    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rst As ADODB.Recordset

    'Define and open connection
    cnn.ConnectionString = "DRIVER={SQLite3 ODBC Driver};Database=z:\EWAMP\EWAMP_be_dev.sqlite"
    cnn.Open

    'Define ADO command
    cmd.ActiveConnection = cnn
    cmd.CommandText = sqlString

    'Populate and enumerate through recordset
    Set rst = cmd.Execute
    If rst.EOF Then
       MsgBox "Nothing found...", vbInformation + vbOKOnly
       Exit Sub
    Else
        Do While Not rst.EOF
            '// I'm guessing the problem is with my control assignments, here.
            Me.cntl_Receiver_ID.Value = rst("Receiver_ID")
            Me.cntl_Transmitter_ID.Value = rst("Transmitter_ID")
            Me.cntl_UTC_Date.Value = rst("UTC_Date")
            Me.cntl_Local_Date.Value = rst("Local_Date")
            Debug.Print {Show me the four control values}
            rst.MoveNext
        Loop
    End If

End Sub

Cheers!

DUHdley

Answer 1

I don't believe a form in Datasheet view can be used as an unbound form. But you can use the ADO recordset as the forms recordset.

Set Me.Recordset = rst

Then just be careful not to close your variable named rst until the form closes.

Another alternative solution is to use an in-memory, fabricated, disconnected ADO recordset. Basically, you'd end up creating a new recordset, append fields to it to match your existing recordset, and then move all the data into your new recordset. But I really don't see the point in doing this if you already have a valid, filled ADO recordset.

If you really need/want to display multiple records in an unbound form, I think you would have to use ActiveX controls such as the GridView, ListView, TreeView, or MSFlexGrid. I've noticed that most skilled, professional Access developers stay away from ActiveX controls as much as possible. If and when they do use them, they usually limit it to only the TreeView and the ListView, I think because they are about the only ActiveX controls that add enough value to be worth putting up with whatever problems they might introduce.

I suggest you take a look at this article concerning the differences between DAO and ADO. http://www.utteraccess.com/wiki/index.php/Choosing_between_DAO_and_ADO

Answer 2

A reader in another forum pointed me to a solution similar to that posed by HK1, namely Set Me.Recordset = rst. That fixed my original problem, but created another.

First I re-bound my four textbox controls on the unbound form, and then modified the code significantly, using the sample from http://msdn.microsoft.com/en-us/library/ff835419.aspx. The revised code looks like this:

Private Sub Form_Load()
   Dim sqlString As String
   sqlString = "SELECT Transmitter_ID, Receiver_ID, UTC_Date, Local_Date from Detections"
   If Not IsNull(Me.OpenArgs) Then
      sqlString = sqlString & " where " & OpenArgs
   End If

   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset

   'Define and open connection
   Set cn = New ADODB.Connection
   cn.ConnectionString = "DRIVER={SQLite3 ODBC Driver};Database=z:\EWAMP\EWAMP_be_dev.sqlite;"
   cn.Open

   'Create an instance of the ADO Recordset class,
   'and set its properties
   Set rs = New ADODB.Recordset
   With rs
       Set .ActiveConnection = cn
       .Source = sqlString
       '// .LockType = adLockOptimistic
       .LockType = adLockReadOnly
       .CursorType = adOpenKeyset
       '// .CursorType = adOpenStatic
       .Open
   End With

   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs

   Set cn = Nothing
   Set rs = Nothing
End Sub

The form now display four rows for four returned records, 20 rows for twenty records, and on, up to at least 256k rows (as specified by my parameter set). The only remaining teeny tiny problem is that for four or more records, if I press the "last row" navigation button (>|), the local cursor sets focus to one or more of the intermediate rows, and the control's properties sheet refreshes vigorously (multiple times per second). If I have more form rows than can be displayed on the screen, I can not navigate or cursor to the last row. It's as though the record set is constantly being updated.

As you can see, I've played with the RecordSet LockType and CursorType properties (including adOpenDynamic and adOpenForwardOnly, both of which caused a run-time error with the Set Me.Recordset statement). Toggling the LockType between adLockOptimistic and AdLockReadOnly, and the CursorType between adOpenKeyset and adOpenStatic makes no difference in the retrieval performance (which is fantastically fast now!) or the apparent refresh rate (which is even faster, unfortunately).

Perhaps it's worth mentioning that the "Detections" table the sqlString "selects" from contains ~4M records. I was frustrated in my previous attempts to use a form with a data source bound to a passthrough query of this table, because the query always returned the entire 4M records to the client regardless of the filter/WhereClause/OpenArgs parameter I passed to the form. The solution shown above would be perfect if only I could close the connection (I've tried) or otherwise quiesce the RecordSet after I've invoked it once.