Using combox box to change subform display

[Nishikawa]

Hi,

I have a subform within a form. I also have another 6 additional subform. I am wondering if it is possible that to use a combo box to change the display of the subform.

e.g. Form A is now showing subform 1. User click "2" in combo box and the form now shows subform 2.

Is it possible? If so, can someone teach me how to do it?

I tried using tab but the resuls is not that great.
I tried using requesry but there are too many different variable and I do not how to do it.

Your helpi is greatly appreciated.

 

[wazz]

subform *controls* have a *form* inside them.

do you want to use only one subform *control* and change the form inside it, or, show and hide different subform controls?

- if it's the first idea you need to change the 'Source Object' of the subform (and 'Link Child Fields' and 'Link Master Fields' if a 1:N relationship). note: if it's not a 1:N relationship, just change the source object and you're done. you can treat the subform like another form sitting on top of your main form.

    If Me.sfrm1.SourceObject = "frm1" Then
        Me.sfrm1.SourceObject = "frm2"
    Else
        Me.sfrm1.SourceObject = "frm1"
    End If

- if it's the second idea you can make the subform.name.visible (or not).

maybe this question will be enough to get you on the right track...

 

[Nishikawa]

Forgive me but I forgot to mention that my subform is in datasheet view, therefore the combo box is located in the main form.

Can i actually just have all 7 subforms in the main form and make 6 of them invisable? I believe that in datasheet mode is not possible.

 

[wazz]

you can use many subforms if you wish and you can make 6 of them invisible and change which one is visible by the combobox. this should be ok if you are the only user, if this is multi-user maybe not. it's ok if they are datasheets.

(i think) you should use many subforms on a tabcontrol. tabcontrols are not difficult once you get used to them. them first "trick" is to remember to click the tab before you place a subform on the page. if you don't click the tab, the subform will be placed on the main page not on the tab.

 

[Nishikawa]

My database will be a multiuser environment.

Anyway, The top part of my main form shows a few world clock and other details while the tab is slightly below the clocks. The size of the 6 subforms are 22" by 13" (I know, it is very big. the database is meant to be seen on a 30" monitor. The problem in using tab is that all my formulas has to be changed (and I mean a lot) and that while changing tabs, MS access bring the subform to the top of the screen, hiding the clocks from the viewers and this is extremely troublesome (my users are very pampered.....)

Are there any other options available wazz? I'm sorry to be giving you so much restriction.

 

[wazz]

have you figured out why the subforms are moving? i can't help thinking they are not properly "attached" to the tab control.

anyway, another option would be to use page breaks. i don't know how many pages you can make with those big forms but page breaks work really well. on your combo's after update, goto page. something like that.

 

[Nishikawa]

Hmm... I will try to do that.
Out of my 6 subforms, 3 of them are from the same source but display differently due to queries. Is that anyway to requery a subform so that it is able to display different information?

 

[wazz]

- if the fields are the same in your different queries, change the record source of the subform.
- if the fields are different, see the first bullet point in post#2 (updated).

 

[Nishikawa]

How do I change the record sourse via combo boxes? Do I use this method?
I got this code from this thread
http://www.access-programmers.co.uk/forums/showthread.php?t=121948&highlight=requery+subform


Private Sub filterOptionGroup_AfterUpdate()
With Forms!frmMain
Select Case Me.filterOptionGroup
Case 1
.RecordSource = "SELECT * FROM frmMain WHERE FirstName Not Like 'Jim'"
Case 2
.RecordSource = "SELECT * FROM frmMain WHERE FirstName Like 'Mike'"
Case 3
.RecordSource = "frmMain"
End Select
End With

Me.sfmCurrent_Year.Form.Requery
Me.sfmCurrent_Year.Form.Refresh

 

[wazz]

replace frmMain with the name of a table or query.