unbound subform on unbound form: getting data into subform

[EVPLS]

Hello,

I use a form to administer circulars (new products released, products
deleted). The main form contains circular number (ID), author, distribution
(world-wide or region) and date published. The subform contains information
on the product(s) and categoriy (whether new or deleted). The "unbound form"
business is to reduce traffic on a low-performance network. Data changes or
new records are committed through UDATE or INSERT INTO queries.

All combo and textboxes on both forms are unbound, recordsources are set in
the AfterUpdate event of a combobox to select a circurlar.

This assigns the recordsources:

Me.RecordSource = "SELECT C.* FROM " & _
"tblCCirculars AS C WHERE " & _
"(((C.CircularID) = '" & strSelect & "'));"
Me.sfrProductsCategories.Form.RecordSource = _
"SELECT P.CircularID, P.CategoryID, " & _
"P.ProductID FROM tblCProducts AS P " & _
"WHERE (((P.CircularID) = '" & _
strSelect & "')) ORDER BY P.CategoryID, " & _
"P.ProductID;"

I use this to assign values to the unbound controls on the main form:

Set rst = Me.Recordset

With Me
.cboAuthor = rst!Author
.cboDistribution = rst!Distribution
.txtCircularID = rst!CircularID
.txtPublished = rst!PublishedDate
End With
rst.Close

So far, so good. Now I try to assign corresponding values to the subform:

Set rst = Me.sfrProductsCategories.Form.Recordset

If Not rst.EOF Then
Me.sfrProductsCategories.Form.cboCategoryID = _
rst!categoryid
Me.sfrProductsCategories.Form.cboProductID = _
rst!ProductID
Me.sfrProductsCategories.Form.txtCircularID = _
rst!CircularID
End If

The "relation" usually is one-to-one but may be one-to-many. The subform
will display n+1 records, i.e., at least two.

As is, those n+1 records on the subform will all display the first productid
found in the subform's recordset. When I add a loop (do until rst.eof ...
movenext ... loop), the last productid is displayed n+1 times.

I guess I'm falling victim to the subform being referenced as a whole rather
than record by record but I can't seem to find a solution.

Has anybody out there got an idea? Thank you very much in advance for any
hints.

 

[Arvin Meyer [MVP]]

Unbound controls on a subform will always be identical. You can avoid this
by using a third party grid control, or by binding a recordset as you need
to.

Me.SubformName.Form.Recordsource = "Select ..."