Using an option box to select which form to open in a sub-form

[MrChips]

I have a number of different forms that I would like to call into a sub-form.
I would like to select which form to display using an option box on the main form.
What would be the correct VBA code to use in the select case please?



I thought it would be simple, but after an afternoon's trying, I have been forced to request help.
There is plenty of advice on the net, but I couldn't get anything to work.

Any advice that you can give me will be greatly appreciated.

I am using Access 365 with windows 11

[Welshgasman]

I used a combo, that would hold the form name and a description, with form name hidden, then just opened what was selected. Same with reports.

[Gicu]

Use an Option Group control instead of individual option boxes. Set the values of each option to the name of the form you want in your subform Then in the AfterUpdate event of the group simply have:

Me.sfrmYourSubform.SourceObject=Me.grpSelectForm

Cheers,

[Micron]

If sticking with the option group, I'd probably make the option values the name of the subform to load. Then the AfterUpdate event of the frame would be

FormRefereceHere.SubformControlNameHere.SourceObject = frameReferenceHere

That syntax is the best I can do without knowing the names of any of the objects involved.

[Gicu]

I understood that the OP has one subform control in which he wants to "load" various forms, there my suggestion to use the forms' names as the values.

[MrChips]

Welchgasman.
Thank you for your reply.
I currently have 10 buttons which each opens a form. When they have all been opened, it leaves the screen very untidy, which is why I would like them to be opened in one sub subform. I appreciate your reply. Thank You.

[MrChips]

I am sorry. Bit of a medical disaster here. Haven't been able to test your solutions. But will do, hopefully, shortly. Thank you all for your responses.
Dave P

[MrChips]

Thanks Micron.
When I tried to add the form names in the values field of the frame (option), it advised me the field was too long. (unless, I course, I should have put the form name in the label (column one of the option choices.!)
Also, to be honest, I didn't quite understand which was FormReferenceHere.

[MrChips]

Gicu,
Thanks.
I have used select case to decided which piece of code to run, as in the attached screen dump.
When I do so, I get the response as per the second screen dump.

It is several years ago that I used Access, and with the aid of Google search, I have got a lot of it remembered, but this is eluding me, and I would like to clean up what I have at the moment.
Many thanks for your time on this so far.

[Micron]

FormReferenceHere indicates to put a form reference there. Could be as simple as Me - all depends on your design.
Select Case is over complicating this IMO but if you want to use it, put a break point at the Select line and make a choice on the form. The code should stop at your break point. Then F8 to step through code. You should be able to see what Frame2 value is. It won't be "Me.f_Info_subform_blank.SourceObject = Me.f_1_secs"

Simpler:
Private Sub Frame2_AfterUpdate()
Me.f_Info_subform_blank.SourceObject = Me.Frame2 '<< where, as stated, option value is the name of the form to load
End Sub

[Welshgasman]

So what cannot it not find?, that would normally be highlighted?
What is f_c_scribes meant to be?

[davegri]

chips-davegri-v01.zip

Like others have suggested - see attached barebones framework DB:

Option Compare Database
Option Explicit


Private Sub fmChoose_Click()
    Select Case fmChoose.Value
        Case 1
            Me.CTL_subfrm.SourceObject = "subfrm1"
        Case 2
            Me.CTL_subfrm.SourceObject = "subfrm2"
        Case 3
            Me.CTL_subfrm.SourceObject = "subfrm3"
        Case 4
            Me.CTL_subfrm.SourceObject = "subfrm4"
        Case 5
            Me.CTL_subfrm.SourceObject = "subfrm5"
    End Select
End Sub