Hi,
I have a tab control in my main form with 8 tabs/pages. Each tab has the same sub form (copy and pasted from first tab to others). I want the subform to requery and show the results based on the tab selected.
In the onchange event in the events for the tab control itself, I am using the below code and the funny thing is it works fine but only for the first tab.
The subform for some reason does not requery when I click on the other tabs. I put a message box showing the selected tab as a test to see if it picks up me changing tabs and these popped up and displayed the correct tab index as I cycled the different tabs but the subform would only requery for the tab index 1, this is after clicking other tabs and returning to it works every time. When I click away the other tabs change the results in the subform but to list all items not the correct items as per the requery. I dont want tab index 0 to do anything which also works fine.
I also tested the sql statements in the query design and they all work correctly. The tab control on the main form is called tabctl_menu and subform is called subfrm_menu_main_items
Code:
Private Sub tabctl_menu_Change()
'Show only the starters
'Starter, Soup, Salad, Main Course, Sundry, Dessert, Extras, Drink
Dim x As Variant
x = Me.tabctl_menu.Value
Select Case Me.tabctl_menu
Case 0
MsgBox x
GoTo endprog
Case 1
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Starter'"
Case 2
MsgBox x
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Soup'"
Case 3
MsgBox x
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Salad'"
Case 4
MsgBox x
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Main Course'"
Case 5
MsgBox x
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Sundry'"
Case 6
MsgBox x
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Dessert'"
Case 7
MsgBox x
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Extras'"
Case 8
MsgBox x
Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Drink'"
End Select
endprog:
End Sub
Not sure what I am doing wrong as the other lines are exactly the same as the one for the tab index 1 only the value is different. I even copied and pasted the line for the tab index 1 that works to the others and still no luck. It is driving me crazy.
Many thanks in advance for your kind assistance.
Kind regards,
B