[AccessD] Combos with Tens of Thousands of Records

[Kostas Konstantinidis]

I have made exactly the same...
New database, Postcodes with the same fields as the example
Then copy/paste the function and the other steps code
I am working with Office 365 64b

The function copied from the new database General/Declarations

Option Compare Database
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




-----Original Message-----
From: AccessD <accessd-bounces+kost36=gmail.com at databaseadvisors.com> On
Behalf Of Stuart McLachlan
Sent: Wednesday, June 23, 2021 9:59 AM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Combos with Tens of Thousands of Records

FWIW, I've just created a new database, built a Postcods table, created a
form, placed a combobox on it and pasted the code from steps 1,2 and 3.  It
works fine.


On 23 Jun 2021 at 16:48, Stuart McLachlan wrote:

> In that case, can you post the actual function as you have it entered.
> 
> 
> On 23 Jun 2021 at 9:38, Kostas Konstantinidis wrote:
> 
> > Hi David
> > I have already tried it... doesn't work
> > Thank's
> > 
> > -----Original Message-----
> > From: AccessD
> > <accessd-bounces+kost36=gmail.com at databaseadvisors.com> On Behalf Of
> > David Emerson Sent: Wednesday, June 23, 2021 9:29 AM To: 'Access
> > Developers discussion and problem solving'
> > <accessd at databaseadvisors.com> Subject: Re: [AccessD] Combos with
> > Tens of Thousands of Records
> > 
> > Without looking at the code in detail, try replacing Me. With Me!
> > 
> > Regards
> > 
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> > 
> > 
> > 
> > -----Original Message-----
> > From: AccessD
> > <accessd-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On
> > Behalf Of Kostas Konstantinidis Sent: Wednesday, 23 June 2021 5:34
> > pm To: 'Access Developers discussion and problem solving'
> > <accessd at databaseadvisors.com> Subject: [AccessD] Combos with Tens
> > of Thousands of Records
> > 
> > Hi all,
> > I use a combo box with more than 85.000 records (names) and I am
> > trying Allen Browne's solution at http://allenbrowne.com/ser-32.html
> > I followed his instructions step by step but the code stops on the
> > function's point
> >             Me.Suburb.RowSource = "SELECT Suburb, State, Postcode
> >             FROM
> > Postcodes WHERE (False);"
> >             sSuburbStub = ""
> >  With the error "Invalid use of Me keybord"
> > Any idea?
> > Thank's all
> > /kostas 
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> > -- 
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com