ComboBox start at last record

[Christine Kempenaar]

I am new in the world of ACCESS'97.
But now I have to maintain an existing ACCESS'97 application and I have the following problem:

There is a Form, containing a ComboBox from which the user must select an item/record from a table.
That's a peace of cake ... Well, it is until you reach the limit of 65k records in that table.
Our table contains more than 99xxx records and beyond recordnumber 65xxx they are not displayed anymore.
This is solved by a SQL statement in the KeyUp-event of the ComboBox.
When Len(me.combobox.Text) >= 1 i.e. 'p', the sql query returns all records that start with character 'p'.
Next the user types character 'o' after the first 'p' and the sql query returns a smaller subset of the table;
But then the problem occurs, when the combobox dropdown button is pushed, the showlist starts at the bottom
of the found recordset??? instead of the first one. When next the DEL key is pressed, the showlist will jump to
the first record in the recordset. But the user does not want to press the DEL key every time to go to the first record.
I tried with SendKeys to add the DEL key, but SendKey triggers the KeyUp-event again and I end up in a loop.
If I look in the debug window (or with debug.print statement) I do see that the recordset contains the right
records in the right order. When I examine combobox.ListIndex it contains some large number, and only after pressing
the DEL key it returns to 0.
So I tried to force ListIndex to 0 by statement 'me.combobox.ListIndex = 0', but that statement also assigns
'me.combobox.Text/Value' to the first recordsetvalue; and thats also not wanted????

I'll hope someone can help me to solve this problem or can tell me what I do wrong.
Thanks
Christine Kempenaar

 

[Alexandre]

Without actually seeing the code you have I can t say what is wrong. What you describe seems OK.
Try to compare what you did with the following method, it may help you to identify the problem.
Re-post if you still need help.

MS-Access Tips for Serious Users
Provided by Allen Browne, abrowne@odyssey.apana.org.au


--------------------------------------------------------------------------------

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)
Dim sNewStub As String ' First chars of Suburb.Text

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.:

Const conSuburbMin = 4

Alex

[This message has been edited by Alexandre (edited 02-05-2002).]

 

[Christine Kempenaar]

Thanks a lot, next Friday or Saterday I will compare with suggested method.

 

[Christine Kempenaar]

It works great, also on my table with 99200 records. Again many thanks.

 

[Alexandre]