Pop-up Subforms based on option buttons

[JaneAFan]

I have created a form with an option group that has 2 options ("Binary" and "Range") and a related subform that would list values for "Binary". On the parent form, if they choose Binary from an option group, I want the subform in datasheet view to pop up, where they can enter items into a list (the binary values), but if they choose the Range option from the group, I don't want any subform showing.

If I don't use the popup part and just have the subform always showing underneath the form, as I scroll through the parent form's records, it does show the related child records (which is what I want), i.e., if the parent is hair color, the binary values are brown, red and blonde, and if the parent says ethnicity, the subform shows Hispanic, Asian, and White. This tells me I did get the relationship part correct.

However, if I have the subform pop-up using the "on mouse up" event procedure on the Binary option using the following VB:

Private Sub Option10_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
DoCmd.OpenForm "frmBinaryValues", acFormDS
End Sub

the form does popup when I want it to, but it shows ALL records in the underlying child table, not just the ones that are related to the parent record. Is there VB I would use (somewhere near acFormDS?) to enact showing only the child records, not the whole form?

[KnooKie]

the problem you are having is probably because the master and child link properties of the parent form and subform are not set and thus your records are not related and filtered acordingly. The subform subsequently shows all records.

You could conceivably set these in the event you specify, however, perhaps a better way of approaching the problem is to have the option buttons within an option group frame (you may already have this?) and show the subform with whatever properties set using the click event of the option group frame. Within the click event just use something like.......

SELECT CASE MyFrameName
CASE 1   'the binary option button if set to option 1
    'show the subform here and set the master and child link properties - there are other ways of doing this but this is easier to explain
CASE 2   'the range option button if set to option 2
    'do NOT show the subform here
END SELECT

hope this helps ?

[JaneAFan]

The select case part works fine as far as the showing/not showing when I want it to. However, I am still having the same problem where when it does show the subform, it is all records, not just child records. How do I define the parent/child relationship in the VB in this instance? I'm kind of confused because the proper relationship is already defined in Access via related tables; do I have to define it again here? How?

[LICKY]

A suggestion is to use a query as the sub-form's data source. The query would be designed to only select records which have the master records ID. You can the the sub-form invisible so that when the option is selected it makes the sub-form visible and requeries it. I have used this for a multi option search form.

[KnooKie]

licky's suggestion sounds good.

the relationship you have defined between your tables is good for ensuring that data in one table remains related to data in another but will NOT filter down data for you on your form.

To filter data to only show records you want to you will have to use the master/child properties of a subform or pass variables into your queries.

If you put something like...

Forms!MyParentFormName!MyMainAndSubformLinkingID

into the criteria part of a query and then use this query as a subforms control source (or listbox or combo box rowsource for that matter) then you will filter down data on the subform based on the ID on the main form as long as you requery the subform as you move from record to record on the main form (current event)

[JaneAFan]

the relationship you have defined between your tables is good for ensuring that data in one table remains related to data in another but will NOT filter down data for you on your form.

That is the part I didn't understand, since that was all I needed to filter an embedded subform, but it wasn't enough for a pop-up/code-generated one.

I have solved the issue by basing the subform on this query:

SELECT tblBinary.BinaryValue
FROM tblBinary
WHERE (((tblBinary.AdjustmentName)=[Forms]![frmAdjustments]![AdjustmentName]));

and putting this VB in the on click event of the option choice that needs the pop-up:

Private Sub Option10_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
DoCmd.OpenForm "frmBinaryValues", acFormDS
End Sub

and it works perfectly.

I was going to use the select case code on the option group (not a specific option choice), but that led to a kind of unexpected problem: when scrolling through existing records on the parent form, you can't see the data on the subform by once again clicking the case 1 option. You have to change the option to case 2, then back to case 1, and up pops the subform with filtered records. By moving the VB to the option itself, if they look at the first record, which already has option 1 selected, if they click it again, they can see the subform, which is what the user wanted.

So all of the scripting questions I had and answers that you all provided could have been avoided if I had just based the subform on a query in the first place. I seem to have a habit of making Access harder for myself than it has to be.

Thanks for your help everyone.