Restrict unbound cboSelector in subForm to the ParentForm ID

[Mishanya]

I have subForm with records of CRM conversations. It's linked to the
ParentForm by ClientID.
I've also put an unbound cboSelector on the subForm to select needed
ConversationID details and kind of hoped it will restrict itself
automatically to the ClientID value of the ParentForm, provided the
link-condition is global for all the subform controls. Well, it is not.
How do I restrict the list of the cboSelector? To filter it by AfterUpdate
event of the ParentForm cboSelectClientID seems cubersome? Maybe to add
ClientID field to the subForm selector query and put some criteria to it
(me!ClientID or something)?

 

[Beetle]

Add the ClientID field from your Conversations table to the row source
of your cboSelector combo box, and set it's criteria to equal the combo
box on your main form, so it's properties might look like;

Row Source: "Select ConversationID, ClientID, ConversationDetails
From tblConversations Where tblConversations.ClientID="
& Forms!YourMainForm!cboSelectClientID

Column Count: 3
Column Widths: 0cm, 0cm, 8cm (or whatever)

If you open the QBE designer (by clicking the elipse to the right of the
Row Source in the properties sheet for your combo box) you can just
add the ClientID field and put Forms!YourMainForm!cboSelectClientID
in the criteria row (of course, replace YourMainForm with the actual
name of your main form).

Then, in the After Update event of the combo box on your main form
(cboSelectClientID) you simply requery the combo box on your subform
like;

Private Sub cboSelectClientID_AfterUpdate()

Me!YourSubformControl.Form!cboSelector.Requery

End Sub

YourSubformControl should be the name of the subform control (box)
that contains your subform. It is usually (but not necessarily) the same
as the name of the subform itself.

 

[Mishanya]

Great!
Thanks a lot, Sean!

Add the ClientID field from your Conversations table to the row source
of your cboSelector combo box, and set it's criteria to equal the combo
box on your main form, so it's properties might look like;

Row Source: "Select ConversationID, ClientID, ConversationDetails
From tblConversations Where tblConversations.ClientID="
& Forms!YourMainForm!cboSelectClientID

Column Count: 3
Column Widths: 0cm, 0cm, 8cm (or whatever)

If you open the QBE designer (by clicking the elipse to the right of the
Row Source in the properties sheet for your combo box) you can just
add the ClientID field and put Forms!YourMainForm!cboSelectClientID
in the criteria row (of course, replace YourMainForm with the actual
name of your main form).

Then, in the After Update event of the combo box on your main form
(cboSelectClientID) you simply requery the combo box on your subform
like;

Private Sub cboSelectClientID_AfterUpdate()

Me!YourSubformControl.Form!cboSelector.Requery

End Sub

YourSubformControl should be the name of the subform control (box)
that contains your subform. It is usually (but not necessarily) the same
as the name of the subform itself.

 

[Mishanya]

Thanks again for Your previous answer.
For the same price...
Is there a way to make the subform always stand by for a new record?
The regular way of
DoCmd.RunCommand acCmdSelectRecord
does not seem to work in On Current/Open/Load while applied to subform?

 

[Beetle]

Is there a way to make the subform always stand by for a new record?

I'm not sure what this statement means. Can you elaborate a bit more
on what you want to happen?

 

[Mishanya]

When I select ClientID in the ParentForm, the subform is automatically loaded
with the first relevant record.
I want it to load blank, ready for a new entry. Normally I put
DoCmd.GoToRecord , , acNewRec
in the On Open event, bur it works only with independent forms, wich are
loaded by themselves. What in the case of subform?