How to populate a subform from unbound parent control

[Dean Slindee]

I have a parent form with a combobox that contains a list of values. Each
value could be the parameter passed to a stored procedure resulting in a
recordset. I cannot bind the combobox to a table, as selecting a combobox
value would be interpreted by Access as a row update.



Because I cannot bind the combobox to a table, I also cannot bind any
subform column to the combobox. Therefore I have resorted to trying to load
the subform datasheet with the following code:

strSQL = "SELECT *" & _

" FROM dbo.tblMailing " & _

"INNER JOIN dbo.tblMailingGroup ON dbo.tblMailing.MailingID =
dbo.tblMailingGroup.MailingID " & _

"WHERE dbo.tblMailingGroup.MailingGroupID = " & "'" &
cboMailingGroupID.Value & "' " & _

"ORDER BY LastName"

Set rst = New ADODB.Recordset

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Forms!frmMailingGroupMember.RecordSource = strSQL



I would like to use the Forms!... statement above, but the subform is not
being instantiated automatically when the parent form is instantiated. It
appears in the parent form, but it is not within the Forms collection when
execution arrives at the Forms!...statement. Is there some way to force
instantiation of the subform on Parent load event? Or another approach to
my desired result?



Thanks in advance,

Dean S

 

[John W. Vinson]

Because I cannot bind the combobox to a table, I also cannot bind any
subform column to the combobox.

Actually, you can! The subform wizard doesn't offer it as one of the options,
but it's fine to put the name of a combo box (or other control) into the
Master Link Field property of a Subform. The Child Link Field needs to be a
field in the subform's recordsource; it will inherit the value selected in the
combo box without any need for that combo box value to be stored in the
mainform.

John W. Vinson [MVP]