I would like to allow user to select a different dataset by using combo box and when user select, the subform will show the dataset that the user selected.
Is this possible?
For example;
User can choose either Data1 or data2 from the combo box, then when user select data 1 the subform will show all the data from data1.
You use the source object of the subform to set to different data. You may also elect to have a tab control without the tab visible. Load each subform on each tab. When user select from combo switch page of tab.
1. if you want to use subform
insert a blank subform control on your form.
on its Property->Data->Source Object,
put the table, query, report or form you want to show.
use the arrow key to select.
but since when the form opens the combo has no value yet
(unless ofcourse you set a value on the Form's Load event),
don't put any Source Object yet on the subform,
making it blank when the form load.
now, on AfterUpdate event of the combobox, you need to
populate the subform with table (table1 or table2 depending):
Code:
Private Sub combo_AfterUpdate()
'sometimes blank subform is named by access as "Child" + number, ie: Child0
'Objects are always qualified
'for table = table.table1
'for query = query.query1
'for forms = form.formname
Me.Child0.SourceObject = "Table." & IIF(Me.combo = "Table1", "Table1", "Table2")
End Sub
2. if you want to use Tab control
(you cannot use tables here or query, only forms and reports)
insert a Tab control on your form.
if there are 2 tabs, right-click on the 1st tab
and add another Page (on right-click menu Insert Page)
click on the second Page, drag your form Table1 (from navigation pane)
and put it on this page.
click on the third Page, drag form Table2 and put to this page.
on Property Sheet->combo box just below it, select the TabCtl (TablCtlxxx)
and on Format->Tab Style: None
now on your combos AfterUpdate Event, switch Pages of TabCtl
Code:
Private Sub combo_AfterUpdate()
' remember that Pages are zero based meaning Page1 actually is item 0
Me.TabCtl0 = IIF(Me.combo = "Table1", 1, 2)
End Sub
Are you simply looking to access a sub-set of data in a particular table e.g. Male members of a Tennis club as opposed to Female members
If so just build a single dropdown Combo box displaying just Male or Female options, then after making the required selection take the User to a Form that uses as its Data Source a Query of the Members table that uses the selected value in the Combo box as the Criteria for Member Sex.
Thankyou for your reply! I want the subform to display two difference table in database.
For instance,
select new po, the new po table will be shown in the subform
select printing po, the printing table will be shown in the subform
It might be easier to simply open a switchboard and chose a new database. When the new database opens you have everything you need, forms, reports etc.
This is a typical switchboard, you still have to create it and code the buttons. New forms and reports but in the long run it could be less confusing for your users.