access 97 limit of 64000 records in combo box

[samcoinc]

Well I just found out that access 97 has a limit of 64000 records or so in the combo box control. I have a table of customers that I needed to have a combo box that workers could start typing in the name of the company and it would show up in the box. The table has over 600000 records. Time to come up with a different way of doing it.

I made a form with a subform running a query of the large table. In the company field criteria I put Like [Forms]![Form1]![Text4] & "*" where text4 is a control on the main form. the code behind the main form is

Option Compare Database
Dim temphold As Variant
Option Explicit


Private Sub Text4_Change()
Dim i As Variant



DoCmd.RunCommand acCmdRefresh
If ([Forms]![Form1]![Text4] <> temphold Or IsNull(Me!Text4) Or IsNull(temphold)) Then
For i = 1 To 10000
Next i
SendKeys "{f2}"
Else
Me!Text4 = temphold & " "
SendKeys "{f2}"

End If
temphold = [Forms]![Form1]![Text4]
End Sub

So as they start typing in the company name each character they type narrows down the query. The hardest part was if someone typed in a space. I needed to code extra to make that work. I don't like using the sendkeys command but it was the only way I could think of to make the cursor in the text box return to the end of what was typed. The for next loop was needed because if the f2 was sent too fast the query would not filter the new data. Its a little clunky still but the filtering is very fast for 600000 records.

Kind of messy I know - anyone have any other way of doing this?

thanks
sam

 

[dcx693]

You can use a variation of the technique used here: Lookup List.

 

[samcoinc]

You can use a variation of the technique used here: Lookup List.

Thanks
I looked at Fizzio first example with the which is scary close to what I am doing. I attached an example using fizzios data and my code in form1. If you double click on the company name it will open the customer form. Notice the space bar works in my example which doesn't in fizzio.

It is in access 2000 because I did it before class here at collage. I am taking some javascript classes

thanks
sam

 

[dcx693]

I took a look at the example. It certainly works, but my gosh. I'm a little anal about code. I replaced your entire Text0_Change() routine with these two lines and deleted the temphold variable. I'm not sure also what you were trying to do with your code loop:
Me.Refresh
Me.Text0.SelStart = Me.Text0.SelLength

I'm not sure what you were testing temphold for, but the form seems to work with just those 2 lines. Check it out and let me know.

 

[samcoinc]

As you can tell I was self taught. The main thing the temp hold is for is so that if someone types a space the and the form requerys the space is stripped out of the list box. So the hold keeps track of the last change so if after the requery the size of the text didn't change it assumes a space was typed and adds a space to the text box. I did use your
Me.Text0.SelStart = Me.Text0.SelLength
which works better than my sendkey - thanks
With just
Me.Refresh
Me.Text0.SelStart = Me.Text0.SelLength
when somone types a space it gets stipped off after the refresh.

sam

ps - I did take the loop out and it worked just fine. I don't know why it didn't work before.