Changing a Main Forms' Sub Forms with VBA

[OwenHall]

I am trying to develop an MS Access form with several sub forms. I wanted to use a single master form and have several child sub forms as sources. What I need to know is how to do this.

The plan is to have the user enter a code A,B,C,N in a text box.

---------------------------------------------------
A = frmSubFormA
B = frmSubFormB
C = frmSubFormC
N = frmSubFormN
---------------------------------------------------

Depending on which code is entered in the text box, the relevant Sub form will appear.

txtCode = A
---------------------------------------------------
A = frmSubFormA
---------------------------------------------------

I tried using a simple (VISIBLE = TRUE/FALSE), bit of code but the link table I created for the orders, recorded an order in all of the sub form tables.

I know that it is possible to use SOURCEOBJECT in the VBA but don't know how to code it right, so that the correct Sub-form is used.

Any response would be helpful.

 

[Tofias1]

OwenHall,

This code should work.

'Calls form based on answers
Private Sub Call_Click()
If CallForm = "Y" Then 'CallForm is name of text box

stDocName = "Table2" 'table name

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, , , stLinkCriteria

Elseif CallForm = whatever ....

'follow same as above

Else


'Do nothing or put in last open last form
' so when foe example it equals s c x y it will
' always open up a certain form whatever you want.

End If

End Sub

Just paste this code behind a comand button.

Tofias 1

 

[tunsarod]

While you could code to set variable data sources for a single subform, unless the data structure is identical for each data source this would be a complex route to go.

I suggest that you do the following:

1. Equip your form with a Tab Control sized appropriately for the subforms.

2. Give the Tab control as many Pages as you have subforms.

3. Allocate one subform to each Tab Control Page.

4. Use the Wizard to set up an Option Frame with an option for each subform/page. (4 according to your example)

5. In the Option Frame's After_Update event add the following code. Note you must change the names to your preferences.

Private Sub MyOptionFrame_AfterUpdate()

MyTabCtl.Value = MyOptionFrame.Value - 1

' Notice that the Frame value for the 1st Option
' will be one but the page number for the 1st Tab Ctl
' Page is zero. Hence the Tab control page is set to
' the Frame's value minus one.

End Sub

When the user selects the appropriate option (A,B,C or N) ONLY the relevant subform will appear.

Rod

 

[Laois]

Just put one subform on your main form and then change the SourceObject property depending on what the user enters. When you put the sub form on the main form give it a name like SubFormHolder. Remember this is not the actual name of the subform just the name of the sub form control that displays the sub form on the main form.

Private Sub txtCode_AfterUpdate()

Select Case txtCode
Case "A"
Me.SubFormHolder.SourceObject = "frmSubFormA"
Case "B"
Me.SubformHolder.SourceObject = "frmSubFormB"
Case "C"
Me.SubFormHolder.SourceObject = "frmSubFormC"
Case "D"
Me.SubformHolder.SourceObject = "frmSubFormD"
End Select

End Sub

I use this method all the time and it works great. You can also use this method with option buttons. In your case you would have 4 option buttons labeled A, B, C and D. When a user clicks an option button you run the above code in the AfterUpdate event of the option group. This is much better than Tab controls because only one subform will be open at a time.

Hope this helps

Dermot