Access 97 -- Tab Control

[bfergus]

Access 97 --- Tab Control<br>
<br>
On a form, I have placed a Tab Control with 5 pages. The pages are named All, Due, Late, Completed, Fyi and Held. There is a listbox on each page use to display the result of a query. They are named ListBox1... ListBox6. Above the tab control I have placed two toggle buttons in a group(Inbox, Outbox), and two option buttons (sort) in a group (Number, Subject). <br>
<br>
The queries are called InBoxAllNumber, OutBoxDueSubject etc.,. I have created four queries for each page. Based on the users' selection, I build the query name and store it in a variable called BUILDIT. A sting called ctr is used to store the selection's page number. Can you help me in finding the correct way to reference the appropriate listbox to change its relevant properties without having to write a lot of code. <br>
<br>
Below is my problem function.<br>
<br>
Sub InOutAll()<br>
Dim BuildIt As String<br>
Dim ctr As String<br>
<br>
If Action.Value = 1 Then 'select inbox<br>
BuildIt = "InBox"<br>
Held.Visible = False<br>
ElseIf Action.Value = 2 Then 'select outbox<br>
BuildIt = "OutBox"<br>
Held.Visible = True<br>
End If<br>
Select Case TabPage.Value<br>
Case 0 'All<br>
BuildIt = Trim(BuildIt) + "All"<br>
ctr = "1"<br>
Case 1 'Due<br>
BuildIt = Trim(BuildIt) + "Due"<br>
ctr = "2"<br>
Case 2 'Late<br>
BuildIt = Trim(BuildIt) + "Late"<br>
ctr = "3"<br>
Case 3 'Completed<br>
BuildIt = Trim(BuildIt) + "Completed"<br>
ctr = "4"<br>
Case 4 'FYI<br>
BuildIt = Trim(BuildIt) + "Fyi"<br>
ctr = "5"<br>
Case Else ' only available with OUTBOX<br>
BuildIt = Trim(BuildIt) + "Held"<br>
ctr = "6"<br>
End Select<br>
If SortBy.Value = 2 Then ' Order by Subject<br>
BuildIt = BuildIt + "Subject"<br>
End If<br>
"ListBox" & ctr & ".RowSource" = BuildIt<br>
"ListBox" & ctr & ".RowSourceType" = "Table/Query"<br>
"ListBox" & ctr & ".ColumnHeads" = True<br>
"ListBox" & ctr & ".ColumnCount" = 7<br>
"ListBox" & ctr & ".ColumnWidths" = ".667 in;.667 in;.667 in;.667 in;.667 in;.667 in;.667 in;"<br>
Me.Requery<br>
End Sub<br>
********************************************************************

 

[Hallsys]

Just dim a control object called lst and in your select case statement instantiate the appropriate list object.<br>
<br>
Ex. Case 4<br>
Set lst = me!Listbox4<br>
<br>
Change all the "ListBox" & ctr &... to lst.<br>
Then add a line at the bottom as:<br>
<br>
lst.requery<br>
<br>
Presumably you don't need to requery the whole form, just the affected listbox.