ГлавнаяMS ACCESS Как преодолеть ограничение в 10000 строк для List или ComboBox
Как преодолеть ограничение в 10000 строк для List или ComboBox
Автор А.М.Пешкин
24.09.2001 г.
Действительно, как??? ;)
Enot 17.08.2001
Как преодолеть ограничение в 10000 строк для List или ComboBox
Пешкин Алексей 17.08.2001
Буквально вчера лазил, и там есть такое: odyssey.apana.org.au (по-моему, так). Правда, это не исправление проблемы, а один из путей её преодоления.
А вот и пример оттудова:
MS-Access Tips for Serious Users Provided by Allen Browne,
Этот e-mail защищен от спам-ботов. Для его просмотра в вашем браузере должна быть включена поддержка Java-script
------------------------------------------------------ Combos with Tens of Thousands of Records Combos become unworkable with many thousands of records, even many hundreds in Access 2. By loading records into the combo only after the user has typed the first three or four characters, you can use combos far beyond their normal limits, even with the AutoExpand property on.
This is the idea:
Leave the combo’s RowSource property blank. Create a function that assigns the RowSource after a minimum number of characters has been typed. Only entries matching these initial characters are loaded, so the combo’s RowSource never contains more than a few hundred records. Call this function in the combo’s Change event, and the form’s Current event.
Example: Look up Postal Codes from Suburb For this example you need a table named Postcodes, with fields Suburb, State, Postcode. You may be able to create this table from downloaded data, for example postcodes for Australia. Make sure all three fields are indexed.
You also need a combo with these properties:
Name Suburb RowSource BoundColumn 1 ColumnCount 3
'Step 1: Paste this into the General Declarations 'section of your form’s module:
Dim sSuburbStub As String Const conSuburbMin = 3 Function ReloadSuburb(sSuburb As String) ' First chars of Suburb.Text Dim sNewStub As String
sNewStub = Nz(Left(sSuburb, conSuburbMin),"") ' If first n chars are the same 'as previously, do nothing. If sNewStub <> sSuburbStub Then If Len(sNewStub) < conSuburbMin Then 'Remove the RowSource Me.Suburb.RowSource = _ "SELECT Suburb, State, Postcode FROM " & _ "Postcodes WHERE (False);" sSuburbStub = "" Else 'New RowSource Me.Suburb.RowSource = _ "SELECT Suburb, State, Postcode FROM " & _ Postcodes WHERE (Suburb Like """ & _ sNewStub & "*"") ORDER BY Suburb, State, Postcode;" sSuburbStub = sNewStub End If End If End Function
'Step 2: In the form’s Current event procedure, enter 'this line:
Call ReloadSuburb(Nz(Me.Suburb, ""))
'Step 3: In the combo’s Change event procedure, you 'could also use a single line. The code below 'illustrates how to do a little more, blocking 'initial spaces, and forcing "Mt " to "Mount ":
Dim cbo As ComboBox ' Suburb combo. Dim sText As String ' Text property of combo.
Set cbo = Me.Suburb sText = cbo.Text Select Case sText Case " " ' Remove initial space cbo = Null Case "MT " ' Change "Mt " to "Mount ". cbo = "MOUNT " cbo.SelStart = 6 Call ReloadSuburb(sText) Case Else ' Reload RowSource data. Call ReloadSuburb(sText) End Select Set cbo = Nothing
'Step 4: To assign the State and Postcode, add this 'code to the combo’s AfterUpdate event procedure:
Dim cbo As ComboBox Set cbo = Me.Suburb If Not IsNull(cbo.Value) Then If cbo.Value = cbo.Column(0) Then If Len(cbo.Column(1)) > 0 Then Me.State = cbo.Column(1) End If If Len(cbo.Column(2)) > 0 Then Me.Postcode = cbo.Column(2) End If Else Me.Postcode = Null End If End If Set cbo = Nothing
The combo in Use As the user types the first two characters, the drop-down list is empty. At the third character, the list fills with just the entries beginning with those three characters. At the fourth character, Access completes the first matching name (assuming the combo’s AutoExpand is on). Once enough characters are typed to identify the suburb, the user tabs to the next field. As they leave the combo, State and Postcode are assigned.
The time taken to load the combo between keystrokes is minimal. This occurs once only for each entry, unless the user backspaces through the first three characters again.
If your list still contains too many records, you can reduce them by another order of magnitude by changing the value of constant conSuburbMin from 3 to 4, i.e.: