Vba – Choosing specific record for form

ms-accessrecordsetvba

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"

Best Solution

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.

alt text http://ltd.remou.com/linkchildlinkmaster.png

Related Question