MS Access 2003 - VBA for SELECT query and only choosing specfic record for form

Question

Okay so on this form, I create a grid, almost looks like a bar chart with little "cells" stacked by month. Each of these "cells" are square sub forms, and I create the little square forms that I use as the sub forms.

So what I wanted to know is, what is the code (VB) for running a select query, and only working with specific records within that query? For example, each one of these little "cell" like forms that become the sub-forms, represent a organization, and if I run a top Nth query I get the 10 Top store by whatever....so the idea is to use each little form as a representative of each record from that query. So for the first cell I want to run the SELECT query, get the results, and work only with the first record. Then on the second form, run the exact same query, and work only with the second record, and so on!

Its a little weird I suppose, but it will give them exactly what they want, and the only part I am not sure about, is the "defining exactly which record I want to use in the VBA"

Thanks!

Answer 1

If i understand your request correctly yuo can do something like this.

Table Structure

ID Autonumber,
Col1 Text

VBCode

Private Sub Command0_Click()
Dim rec As Recordset
Dim id As Integer

    Set rec = CurrentDb.OpenRecordset("SELECT TOP 10 * FROM Table1")

    While Not rec.EOF
        id = rec.Fields("ID")
        rec.MoveNext
    Wend
End Sub

Answer 2

You treat the SQL statement as the definition of a pseudoTable, i.e. a set of records (RecordSet) that you can manipulate as if it was a table.

Dim cnn As ADODB.Connection
Dim pseudoTable As ADODB.Recordset
Dim strSQL As String

    Set cnn = CurrentProject.Connection
    Set pseudoTable = New ADODB.Recordset

    strSQL = "SELECT Title FROM realTable where realID < 1000;"
    pseudoTable.Open strSQL, cnn, adOpenStatic, adLockOptimistic
    If Not pseudoTable.BOF And Not pseudoTable.EOF Then
        pseudoTable.MoveFirst
        Do Until pseudoTable.EOF
            ' do something with the table
            pseudoTable.MoveNext
        Loop

The above code should give you a good start.

Answer 3

You could change the SQL for each sub form to read:

SELECT TOP 1 ID, F1, F2 From Table

SELECT TOP 2 From Table WHERE ID NOT IN (SELECT TOP 1 ID From Table)

SELECT TOP 3 From Table WHERE ID NOT IN (SELECT TOP 2 ID From Table)

<...>

SELECT TOP 10 From Table WHERE ID NOT IN (SELECT TOP 9 ID From Table)

Or, seeing you already have a somewhat odd set-up, you can write each ID to one of 10 hidden textbox controls using a recordset, and use each of these textboxes as the link master field with ID as the link child field for the subforms.

Link Master Field and Link Child Field are properties of the subform control, not the form contained.