Loop Through Query Records to Fill Out Form using DAO.Recordset

[Tom Rogers]

I'm trying to use a separate query to fill in as much of one section of an
Access report as possible, up to 4 records.

The Visual Basic code I've written is as follows:
-----
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
temp = GetParentInfo()
End Sub

Public Function GetParentInfo()
ThisRecord = GetThisRecord(1)
Reports![SR4 - VBC]!P1Name = ThisRecord
ThisRecord = GetThisRecord(1)
Reports![SR4 - VBC]!P1Name = ThisRecord
ThisRecord = GetThisRecord(1)
Reports![SR4 - VBC]!P1Name = ThisRecord
End Function

Public Function GetThisRecord(iRecord As Integer) As Variant
Dim Parents As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set Parents = db.OpenRecordset("PI-D", dbOpenDynaset)
Parents.MoveFirst

'If Parents.IsEOF = 0 Then
Parents.Move (iRecord - 1)
'End If
GetThisRecord = Parents![FIRSTNAME]
'GetThisRecord = Parents
End Function
-----

This sort of works, but it returns the first four records in the query,
which is not the desired behavior. I'd like to add some sort of Parents.Find
statement that would apply seach parameters to the Access Query "PI-D". I
know what the SQL would look like, but I don't think the criterion I'd want
to use in that query will be available until run-time, and must therefore be
applied via script. I don't know if the Find method is what I'm looking for.

Also, I'm worried about what will happen when the modified query returns
fewer than 4 records. I tried using IsEOF, but Access doesn't like that. I
found it somewhere on the MSDN website, and it's not very clear when they
start talking about a different language, especially since they're very
similar. What I *want* it to do is, if the number of records returned is
less than the record it's searching for, substitute a blank value instead. I
assume that without error trapping some kind of EOF check, it will throw an
error if I use the Move method to point to a record beyond the bounds of the
recordset.

One more thing -
I don't know how to perform variable name evaluation in VB. For example,

 

[Tom Rogers]

Oops.

As I was saying.

For example, I would like to streamline the GetParentInfo function to loop 4
times, and assign the value of record i to field i-name.
Something like: eval("Reports![SR4 - VBC]!P" & i & "Name = ThisRecord")
I don't think eval will work, though.