Combo Box Lookup with over 62,000 records

[pcbins]

Is there an easy way to get a combo box to work well with over 62,000
records? I tried http://www.allenbrowne.com/ser-32.html but I think there are
too many syntax errors in the code.

Here is my current combo box source code:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts].FNAME;

Thanks in advance!

 

[karl dewey]

Change as below and set AutoExpand property to Yes.

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].[LNAME] & ", " &[Student DB All Districts].[FNAME], [Student DB
All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts].FNAME;

Have you thought about using cascading combos where the first is the District?

 

[pcbins]

Thank you, but the lookup still runs very slow and wants to lock up the
database.

I'm trying to find a way to get it to limit the names it will load for each
time you search. Say, perhaps, to ask the first few letters of the last name
and pullup only those names.

Check my additional comment I added below.

Change as below and set AutoExpand property to Yes.

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].[LNAME] & ", " &[Student DB All Districts].[FNAME], [Student DB
All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts].FNAME;

Have you thought about using cascading combos where the first is the District?

--
KARL DEWEY
Build a little - Test a little

Is there an easy way to get a combo box to work well with over 62,000
records? I tried http://www.allenbrowne.com/ser-32.html but I think there are
too many syntax errors in the code.

Here is my current combo box source code:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts].FNAME;

Thanks in advance!

 

[karl dewey]

Try it this way ---
WHERE ([STUDENT DB Other Districts].LNAME) Like [Enter start of Last Name] &
"*"
--
KARL DEWEY
Build a little - Test a little

Is there an easy way to get a combo box to work well with over 62,000
records? I tried http://www.allenbrowne.com/ser-32.html but I think there are
too many syntax errors in the code.

Here is my current combo box source code:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts].FNAME;

Thanks in advance!

Forgot to mention the field "Data ID" is 0 width, the search is on last name
(LNAME)

I tried adding a "WHERE ((([STUDENT DB Other Districts].LNAME)=[Enter Last
Name]))" But then the last name has to be exact and I have trouble with
resetting the control!

 

[Paul Shapiro]

I haven't used that part of Allen Browne's code, but I would be very
surprised if any of his posted code has syntax errors. The code looks fine
to me. Maybe you introduced the syntax errors in adapting the code for your
application? You could try posting your code here and maybe Allen or someone
else might help.

Is there an easy way to get a combo box to work well with over 62,000
records? I tried http://www.allenbrowne.com/ser-32.html but I think there
are
too many syntax errors in the code.

Here is my current combo box source code:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All
Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All
Districts].FNAME;

Thanks in advance!

Forgot to mention the field "Data ID" is 0 width, the search is on last
name
(LNAME)

I tried adding a "WHERE ((([STUDENT DB Other Districts].LNAME)=[Enter Last
Name]))" But then the last name has to be exact and I have trouble with
resetting the control!

 

[pcbins]

Hey! that works great, Karl.
Now how to get it to ask for a new criteria when I click on the Combo Box?

Try it this way ---
WHERE ([STUDENT DB Other Districts].LNAME) Like [Enter start of Last Name] &
"*"
--
KARL DEWEY
Build a little - Test a little

Is there an easy way to get a combo box to work well with over 62,000
records? I tried http://www.allenbrowne.com/ser-32.html but I think there are
too many syntax errors in the code.

Here is my current combo box source code:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts].FNAME;

Thanks in advance!

Forgot to mention the field "Data ID" is 0 width, the search is on last name
(LNAME)

I tried adding a "WHERE ((([STUDENT DB Other Districts].LNAME)=[Enter Last
Name]))" But then the last name has to be exact and I have trouble with
resetting the control!

 

[pcbins]

Ok, so far I have:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All
Districts] WHERE (([STUDENT DB All Districts].LNAME) Like ([Enter start of
Last Name] &
"*")) ORDER BY [Student DB All Districts].LNAME, [Student DB All
Districts].FNAME;

Now anyone know how to get the Combo to ask for new parameter when you click
on the drop-down arrow? Or is that even possible?

 

[John W. Vinson]

Ok, so far I have:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All
Districts] WHERE (([STUDENT DB All Districts].LNAME) Like ([Enter start of
Last Name] &
"*")) ORDER BY [Student DB All Districts].LNAME, [Student DB All
Districts].FNAME;

Now anyone know how to get the Combo to ask for new parameter when you click
on the drop-down arrow? Or is that even possible?

Requery the combo box in its GotFocus event, if you want to do this! There are
better techniques, though. See http://allenbrowne.com/ser-32.html for an
example. Basically it intercepts typing into the combo box (which you should
certainly be doing rather than scrolling; autocomplete is very helpful!) to
change the combo's rowsource on the fly, with no popup prompts interrupting
the flow.

 

[pcbins]

Well, I will keep working on it when I have time. I am having trouble
converting his example to work with my situation. So I was looking for
something quick and easy I can do until I have more time to play with it.

Ok, so far I have:

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All
Districts] WHERE (([STUDENT DB All Districts].LNAME) Like ([Enter start of
Last Name] &
"*")) ORDER BY [Student DB All Districts].LNAME, [Student DB All
Districts].FNAME;

Now anyone know how to get the Combo to ask for new parameter when you click
on the drop-down arrow? Or is that even possible?

Requery the combo box in its GotFocus event, if you want to do this! There are
better techniques, though. See http://allenbrowne.com/ser-32.html for an
example. Basically it intercepts typing into the combo box (which you should
certainly be doing rather than scrolling; autocomplete is very helpful!) to
change the combo's rowsource on the fly, with no popup prompts interrupting
the flow.

 

[a a r o n _ k e m p f]

if you were using SQL Server, you would never have trouble with
locking

Thank you, but the lookup still runs very slow and wants to lock up the
database.

I'm trying to find a way to get it to limit the names it will load for each
time you search. Say, perhaps, to ask the first few letters of the last name
and pullup only those names.

Check my additional comment I added below.

Change as below and set AutoExpand property to Yes.

SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].[LNAME] & ", " &[Student DB All Districts].[FNAME], [StudentDB
All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts].FNAME;

Have you thought about using cascading combos where the first is the District?

Is there an easy way to get a combo box to work well with over 62,000
records? I triedhttp://www.allenbrowne.com/ser-32.htmlbut I think there are
too many syntax errors in the code.
Here is my current combo box source code:
SELECT [Student DB All Districts].[Data ID], [Student DB All
Districts].LNAME, [Student DB All Districts].FNAME, [Student DB All
Districts].MI, [Student DB All Districts].DOB FROM [Student DB All Districts]
ORDER BY [Student DB All Districts].LNAME, [Student DB All Districts]..FNAME;
Thanks in advance!

 

[pcbins]

I wish someone, instead of telling me how wonderful Allen Browne's solution
is, would show me how to convert it so that it would work for me....

 

[John W. Vinson]

I wish someone, instead of telling me how wonderful Allen Browne's solution
is, would show me how to convert it so that it would work for me....

I've run Allen's code and gotten no syntax errors at all! What specific error
messages are you getting? What version of Access? If it's 2007, do you have
the database in a Trusted Location?

 

[pcbins]

This is what I've put in the General Declarations of the form:
--------
Dim sNAMELOOKUPStub As String
Const conNAMELOOKUPMin = 5
Function ReloadNAMELOOKUP(sNAMELOOKUP As String)
Dim sNewStub As String ' First chars of NAMELOOKUP.Text

sNewStub = Nz(Left(sNAMELOOKUP, conNAMELOOKUPMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sNAMELOOKUPStub Then
If Len(sNewStub) < conNAMELOOKUPMin Then
'Remove the RowSource
Me.NAMELOOKUP.RowSource = "SELECT LNAME, FNAME, MI, DOB
FROM [STUDENT DB All Districts] WHERE (False);"
sNAMELOOKUPStub = ""
Else
'New RowSource
Me.NAMELOOKUP.RowSource = "SELECT LNAME, FNAME, MI, DOB
FROM [STUDENT DB All Districts] WHERE (LNAME Like """ & _
sNewStub & "*"") ORDER BY LNAME, FNAME, MI;"
sNAMELOOKUPStub = sNewStub
End If
End If
End Function
---------------
This produces the following error:

"Microsoft Office Access can't find the macro 'Call ReloadNAMELOOKUP(Nz(me.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under."

(it won't allow me to name the combo box the same name as the field I'm
looking up, I assume because it is a field on the form.)

 

[John W. Vinson]

"Microsoft Office Access can't find the macro 'Call ReloadNAMELOOKUP(Nz(me.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under."

(it won't allow me to name the combo box the same name as the field I'm
looking up, I assume because it is a field on the form.)

It sounds like you have the text

Call ReloadNAMELOOKUP...

actually in the property line of some event on the form's properties. It
should be either

[Event Procedure]

with that code in the VBA window, or

=ReloadNameLookup(...

with an equals sign rather than the CALL.

 

[pcbins]

Well I did it how the wonderful allen browne instructions says to do it...
"Step 2: In the form's Current event procedure, enter this line: Call
ReloadSuburb(Nz(Me.Suburb, ""))" ... and thats how I did it:
-----------
Private Sub Form_Current()
Call ReloadNAMELOOKUP(Nz(Me.NAMELOOKUP, ""))
End Sub
-----------
Since his code is always perfect, it must not work for my situation.

"Microsoft Office Access can't find the macro 'Call ReloadNAMELOOKUP(Nz(me.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under."

(it won't allow me to name the combo box the same name as the field I'm
looking up, I assume because it is a field on the form.)

It sounds like you have the text

Call ReloadNAMELOOKUP...

actually in the property line of some event on the form's properties. It
should be either

[Event Procedure]

with that code in the VBA window, or

=ReloadNameLookup(...

with an equals sign rather than the CALL.

 

[John W. Vinson]

Well I did it how the wonderful allen browne instructions says to do it...
"Step 2: In the form's Current event procedure, enter this line: Call
ReloadSuburb(Nz(Me.Suburb, ""))" ... and thats how I did it:

The error message you say you got:

"Microsoft Office Access can't find the macro 'Call ReloadNAMELOOKUP(Nz(me.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under."

unquestionably means that you're trying to execute a Macro named
'Call ReloadNAMELOOKUP(Nz(Me.' - and of course there isn't one.

Doublecheck again that you have either blank or [Event Procedure] in every one
of the properties on the Event tab of your form's properties, and not the Call
statement.

 

[pcbins]

Good News! I figured out how to reset the parameters easily. I just added a
control button next to the combo box called "reset list" and coded it to
requery the combo box.

I also moved the input request to the District Name, since we usually have a
list of students sorted by district. So when we get to the next district we
just have to click on the reset button and enter the next district name.

Anyway, this works great for me so I thought I would pass this on to anyone
who needs a quick and easy way to get over the 64k hurdle.

And, Thanks Again Karl Dewey!

 

[pcbins]

Since I found something that seems to work great, I'll give up on this idea
for now. I have other matters to tend to. But this is what I am using now,
incase anyone reading this is interested:

Combo87 Rowsource:
-----------
SELECT [STUDENT DB All Districts].[Data ID], [STUDENT DB All
Districts].LNAME, [STUDENT DB All Districts].FNAME, [STUDENT DB All
Districts].MI, [STUDENT DB All Districts].DOB FROM [STUDENT DB All Districts]
WHERE ((([STUDENT DB All Districts].DISTRICT) Like ([ENTER DISTRICT] & "*")))
ORDER BY [STUDENT DB All Districts].LNAME, [STUDENT DB All Districts].FNAME;
-----------
Reset Command Button:
-----------
Private Sub ResetNameList_Click()
Me.Combo87 = Null
Me.Combo87.Requery
End Sub
-----------

Thanks to everyone for your input!!
pcbins

Well I did it how the wonderful allen browne instructions says to do it...
"Step 2: In the form's Current event procedure, enter this line: Call
ReloadSuburb(Nz(Me.Suburb, ""))" ... and thats how I did it:

The error message you say you got:

"Microsoft Office Access can't find the macro 'Call ReloadNAMELOOKUP(Nz(me.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under."

unquestionably means that you're trying to execute a Macro named
'Call ReloadNAMELOOKUP(Nz(Me.' - and of course there isn't one.

Doublecheck again that you have either blank or [Event Procedure] in every one
of the properties on the Event tab of your form's properties, and not the Call
statement.