John Vinson mentioned the problem with having 10 combos on a form and the
negative impact on performance it would have. I had such a situation about 3
or 4 years ago. It had 6 list boxes rather than combos and the form took
almost 2 minutes to load. I came up with the following strategy that makes
the problem invisible to the users.

All the list boxes had no row source and the height property was set to 0.
Above each list box was a command button with the name of the list so the
user could click the list he wants. At that point, the Click event of the
list box populates the list:

Private Sub cmdBillProdOffering_Click()

DoCmd.Hourglass True

Call ResetScreen(6)

With Me.lstBillProdOffering
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT ProjectID " & _
"FROM actual_res_export " & _
"WHERE ProjectID 'Billable' And BillCat = 'unbillable' "
& _
"ORDER BY ProjectID;"
End If
.Height = 3015
.SetFocus
End With
DoCmd.Hourglass False

End Sub

The ResetScreen function was used to keep the list boxes in sync:

Private Sub ResetScreen(lngSelector As Long)
If lngSelector = 6 Then 'BillProdOffering
With Me.lstMActivity
.Height = 0
.RowSource = ""
End With
Me.lblMactivity.Caption = "All Included"
Me.lblMactivity.ForeColor = 0
End If

If lngSelector = 5 Then 'MActivity
With Me.lstActivity
.Height = 0
.RowSource = ""
End With
Me.lblActivity.Caption = "All Included"
Me.lblActivity.ForeColor = 0
Me.lblMactivity.Caption = "All Included"
Me.lblMactivity.ForeColor = 0
End If

If lngSelector = 4 Then 'Activity
With Me.lstBillNetwork
.Height = 0
.RowSource = ""
End With
Me.lblBillNetwork.Caption = "All Included"
Me.lblBillNetwork.ForeColor = 0
End If

If lngSelector = 3 Then 'BillNetwork
With Me.lstPool
.Height = 0
.RowSource = ""
End With
Me.lblPool.Caption = "All Included"
Me.lblPool.ForeColor = 0
Me.lblActivity.Caption = "All Included"
Me.lblActivity.ForeColor = 0
End If

If lngSelector = 2 Then 'Pool
With Me.lstHomeRoom
.Height = 0
.RowSource = ""
End With
Me.lblBillNetwork.Caption = "All Included"
Me.lblBillNetwork.ForeColor = 0
Me.lblHomeRoom.Caption = "All Included"
Me.lblHomeRoom.ForeColor = 0
End If
End Sub


Since it is a combo, you might want to just make the combo visible/invisible
rather than using a height property, but other than that, it concept is the
same.

--
Dave Hargis, Microsoft Access MVP


"laskowv" wrote:

I have a table which holds the ID, DivisionID, Division term (ex: 2007-2009,
2009-2011), followed 10 fields (type = number) for the 10 officer positions.
I have 52 divisions and 178 terms that have to be tracked (1921-2099). Each
10 Division Officers need to be maintained; for example here are the 10
offices and the EmployeeID numbers:

ID DivID Term Term Pres 1VP 2VP Chaplain RS Reg. Treas. Hist.
CS Lib Parl
40 29 88 4/30/2009 26694
29776 35877 32121 36207 34987 34985 36942 34863 36497 26088
41 29 90 4/30/2011 29776
35877 2461 36311 36053 36498 36575 37209 33860 36207 26694

This allows me to only have about 5000 records. If were to make the table
just have a field called officer position and the the employeeid; it would
cause the file to hold over 92,000.

My issue is two- fold:
1) I have a combo box on the form that uses the employee number from the
field to each of the 10 fields. It works fine, but the form is very slow to
open -- but once open it works great.

2) I need to generate reports from this table. I need to list all of the 10
officers names. I started creating the query and linked the President field
to the Employee table to get the name; but when I went to link the 1st VP
that's when Access "yelled" at me.

How do I link to the Employee table for each of the 10 officers in order to
get their names? Is there another way?

Please help.