Loop thru recordset to populate report

[Robert5833]

Hi all;
I have a problem with a report; there a number of text boxes that get their
value from a DLookUp functions from two different tables, with the Primary
Key of the "One" table as a foreign key in the "Many" table. When I run the
report, the text boxes display only the first occurrance referenced index
value of a stored record (even though a referenced field displays the true
reference; i.e., cboUnitID1 as shown below).

I need it to return all records that are related to the Primary Key.

I have the function in the On Load event of the report.

When I watch the code in break mode, sure enough only the first of the
foreign keyed record is returned.

Is there a loop function I could use to run the table and match all
instances, and return a stack of recordsets meeting that criteria?

I've seen a number of examples of loop functions posted, but honestly I
don't know what kind of action to have the procedure do for the returned
values, when all I want to do with it is populate the assigned text boxes.

Two examples below (others are similar) of the string/statement I'm using
right now:

This one is on the "One" table, and returns all related records because the
report is based on an SQL statement; so there's only one "instance" to find
and return (otherwise I have the first record return problem here too):

Private Sub Report_Load()

Me.tboParentCurrentHour = DLookup("[fldPCurrentHour]", "tblUnit1",
"[fldUnitID1] =" & Forms!frmTrackUnit![cboUnitID1])

This one is pulling data from the "Many" table, and only returns one
instance to the report:

Private Sub Report_Load() (in the same procedure as above...)

Me.tboChildCurrentHour = DLookup("[fldCCurrentHour]", "tblUnit2",
"[fldUnitID2]=" & Nz([Forms]![frmTrackUnit]![cboUnitID2], 0))

Thank you in advance for your time and assistance.

Robert

 

[Allen Browne]

The best way to solve this would be to create a query that uses both tables
(so it show fields from both of them), and use that as the Record Source for
your report. That will be much quicker than using DLookup().

You may need to use an outer join, or records that are null won't appear.
Details in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

The problem with the approach you have taken is that the form doesn't change
record each time the report reads a record. Consequently, referring to:
[Forms]![frmTrackUnit]![cboUnitID2]
keeps referring to the same record in the form all the time.