Combo Box list size

[scorpio46]

I have a table with 106,000 records and growing I use a combo box to select a record however I cant get the displayed list to show more than 65535 records Is there a way to display all the records? in the drop down list from the combo box

[ranman256]

You shouldn't. The box will get bigger and slower.
use a blank text box, user fills, press enter, open a query with the result(s).
edit.

[scorpio46]

will this give a list then?? maybe 150 records might be looking for a record ID that is WD... 001 to 150 I've sort of solved it by having a combo box on main form that sorts in decending order it works just have to copy the Record ID to the subform record just a pain not to be able to select it in the subform (datasheet format)

[scorpio46]

the application uses a main form a work order form with 3 subforms 1 with labour 1 for parts and 1 for transport the parts subform has partid field that uses a combo box to display selectable records opening a text box isn't the answer reading 106,000 records is pretty quik so a few more additions wont slow it down much more. ??? is it possible to set it to read more than the 65k it now shows

[June7]

Review http://allenbrowne.com/ser-32.html

[scorpio46]

cant get past this comes up with an error
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM Postcodes WHERE (False);"
sSuburbStub = ""

Compile error
Invalid use of Me keyword

[June7]

Did you put the code behind form? Code compiles for me, no error.

[scorpio46]

behind the form?

[scorpio46]

Private Sub Form_Current()
Call ReloadSuburb(Nz(Me.Suburb, ""))
End Sub

[scorpio46]

I put the function in the general declarations of the form ??

[scorpio46]

ok now in back form I think never heard of that term in the general declarations along with all the other code?
looks like this in Form_Form1
Option Compare Database
Dim sSuburbStub As String
Const conSuburbMin = 3
Function ReloadSuburb(Suburb As String)
Dim sNewStub As String ' First chars of Suburb.Text
sNewStub = Nz(Left(Suburb, 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

Private Sub Suburb_AfterUpdate()
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
End Sub
Private Sub Suburb_Change()
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
End Sub
Private Sub Form_Current()
Call ReloadSuburb(Nz(Me.Suburb, ""))
End Sub

Now get compile error
method or data member not found

[scorpio46]

populates but cant select a record get error then
Me.State = cbo.Column(1)

[June7]

Post code between CODE tags and indentation will be retained and will be more readable.

So not getting compile error any more?

I've never actually tried this code so never really took a good look at it. No idea why that line fails.

Don't have a db where I can test and don't want to build. If you want to provide db for analysis, follow instructions at bottom of my post.

[scorpio46]

No once I put all the code into form1_Form it worked fine but it was just the code to add values to fields that failed, most probably as the form didn't have those fields will put them in and see what I get.
I may not have explained my Db very well
It has a main form that is based on a generated form for a workorder with 3 sub forms. One of the subforms allows the user to input a part drawn from a Parts table and plug in the partnumber Partname and Retailprice. The Partnumber has a combobox to select parts but as it is so big it wont fully populate the combo box. that's why I was asking not sure if what you provided will work need to experiment a bit more with the after update section to assign the values. I did it on a postcode table I mite need to create a sale table.

[scorpio46]

Thanx a lot for the help finally figured out to just use the SalesPartid field on the form and put the code in it rather than have it display all the records which is what I was doing then it populates the other fields
all good thanx again was very very helpful