combo box sort order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
My Access 97 front end mde has one main form for all users to fill in on a
daily basis. There is a combo box that lists thousands of possibilities and
in 95% of cases, this list is out of sort order (due to recent additions to
the table) unless the back end has recently been compacted. The Row Source
for the combo is a stored query. If I change the query to sort the records,
the combo box takes ages to drop down the first time it's selected after the
form opens.
Is there a way to properly sort the combo box without the user seeing this
lag? The form is a continuous form and the combo is the third field to
recieve the focus in case that helps.

Thanks
Dave
 
If the combo has many, many thousands of records in its RowSource, you might
consider loading it only after say 3 keystrokes, as described here:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

If the combo has just a few thousand records, you should be able to load it
without a lengthy delay by setting up its RowSource so it can use indexes.

For example, if the RowSource is:
SELECT ClientID, Surname & ", " + FirstName AS FullName
FROM tblClient
ORDER BY Surname & ", " + FirstName;
to fix this, you would:
1. Open tblClient in design view.

2. Select the Surname field, and in the lower pane set Indexed to "Yes,
Duplicates Ok".

3. Do the same ot index FirstName as well.

4. Change the ORDER BY clause of the combo's RowSource to:
ORDER BY Surname, FirstName;
so it can use the indexes instead of sorting on the concatenated result.

Post the SQL statement you are using to sort the combo if that doesn't make
sense.
 
Allen Browne said:
If the combo has many, many thousands of records in its RowSource, you might
consider loading it only after say 3 keystrokes, as described here:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

If the combo has just a few thousand records, you should be able to load it
without a lengthy delay by setting up its RowSource so it can use indexes.

For example, if the RowSource is:
SELECT ClientID, Surname & ", " + FirstName AS FullName
FROM tblClient
ORDER BY Surname & ", " + FirstName;
to fix this, you would:
1. Open tblClient in design view.

2. Select the Surname field, and in the lower pane set Indexed to "Yes,
Duplicates Ok".

3. Do the same ot index FirstName as well.

4. Change the ORDER BY clause of the combo's RowSource to:
ORDER BY Surname, FirstName;
so it can use the indexes instead of sorting on the concatenated result.

Post the SQL statement you are using to sort the combo if that doesn't make
sense.
Click to expand...

Thanks for the reply Allen,

Your response made sense, but I've included the SQL below anyway.
As you can see, there's no concatenation and there is no sort order
currently set. Once I include the sort order clause, the combo takes ages to
populate.

[Job#] is the PK for the tblJobs table and is also the bound column for the
combo box.
This query currently returns 8700 records.

SELECT [tblJobs].[Job#], [tblJobs].[Job Description]
FROM [tblJobStatus] INNER JOIN [tblJobs] ON [tblJobStatus].Status =
[tblJobs].Status
WHERE ((([tblJobStatus].[Open])=True));

Is there a way that I can trigger the population of this combo BEFORE it
recieves the focus? I was thinking something like

Private Sub Form_Load()
me!cboJob.requery
End Sub

but that didn't seem to help.

Thanks
Dave
 
Is tblJobStatus.Open an indexed field? Adding an index will help (even if it
is only a y/n field.)

Are the fields you wish to sort on indexed?

Haven't tried, but you could see if get the combo to load by reading its
ListCount property in Form_Open:
Debug.Print Me.Combo1.ListCount

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
Allen Browne said:
If the combo has many, many thousands of records in its RowSource, you
might
consider loading it only after say 3 keystrokes, as described here:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

If the combo has just a few thousand records, you should be able to load
it
without a lengthy delay by setting up its RowSource so it can use
indexes.

For example, if the RowSource is:
SELECT ClientID, Surname & ", " + FirstName AS FullName
FROM tblClient
ORDER BY Surname & ", " + FirstName;
to fix this, you would:
1. Open tblClient in design view.

2. Select the Surname field, and in the lower pane set Indexed to "Yes,
Duplicates Ok".

3. Do the same ot index FirstName as well.

4. Change the ORDER BY clause of the combo's RowSource to:
ORDER BY Surname, FirstName;
so it can use the indexes instead of sorting on the concatenated result.

Post the SQL statement you are using to sort the combo if that doesn't
make
sense.
Click to expand...

Thanks for the reply Allen,

Your response made sense, but I've included the SQL below anyway.
As you can see, there's no concatenation and there is no sort order
currently set. Once I include the sort order clause, the combo takes ages
to
populate.

[Job#] is the PK for the tblJobs table and is also the bound column for
the
combo box.
This query currently returns 8700 records.

SELECT [tblJobs].[Job#], [tblJobs].[Job Description]
FROM [tblJobStatus] INNER JOIN [tblJobs] ON [tblJobStatus].Status =
[tblJobs].Status
WHERE ((([tblJobStatus].[Open])=True));

Is there a way that I can trigger the population of this combo BEFORE it
recieves the focus? I was thinking something like

Private Sub Form_Load()
me!cboJob.requery
End Sub

but that didn't seem to help.

Thanks
Dave
Click to expand...
 
tblJobStatus.Open is a Yes/No field so it wasn't indexed based on that fact.
After indexing, there was no visible gain.

However, the Debug.Print Me.Combo1.ListCount line you suggested did make a
difference. There was a noticeable lag between form opening and becoming
functional, but the combo box was sorted and dropped down immediately after
clicking the down arrow.

I'll try and camouflage the initial lag with a 'Loading...' message or
something similar.

Thanks Allen,
Dave
(Melbourne)

Allen Browne said:
Is tblJobStatus.Open an indexed field? Adding an index will help (even if it
is only a y/n field.)

Are the fields you wish to sort on indexed?

Haven't tried, but you could see if get the combo to load by reading its
ListCount property in Form_Open:
Debug.Print Me.Combo1.ListCount

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
Allen Browne said:
If the combo has many, many thousands of records in its RowSource, you
might
consider loading it only after say 3 keystrokes, as described here:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

If the combo has just a few thousand records, you should be able to load
it
without a lengthy delay by setting up its RowSource so it can use
indexes.

For example, if the RowSource is:
SELECT ClientID, Surname & ", " + FirstName AS FullName
FROM tblClient
ORDER BY Surname & ", " + FirstName;
to fix this, you would:
1. Open tblClient in design view.

2. Select the Surname field, and in the lower pane set Indexed to "Yes,
Duplicates Ok".

3. Do the same ot index FirstName as well.

4. Change the ORDER BY clause of the combo's RowSource to:
ORDER BY Surname, FirstName;
so it can use the indexes instead of sorting on the concatenated result.

Post the SQL statement you are using to sort the combo if that doesn't
make
sense.
Click to expand...

Thanks for the reply Allen,

Your response made sense, but I've included the SQL below anyway.
As you can see, there's no concatenation and there is no sort order
currently set. Once I include the sort order clause, the combo takes ages
to
populate.

[Job#] is the PK for the tblJobs table and is also the bound column for
the
combo box.
This query currently returns 8700 records.

SELECT [tblJobs].[Job#], [tblJobs].[Job Description]
FROM [tblJobStatus] INNER JOIN [tblJobs] ON [tblJobStatus].Status =
[tblJobs].Status
WHERE ((([tblJobStatus].[Open])=True));

Is there a way that I can trigger the population of this combo BEFORE it
recieves the focus? I was thinking something like

Private Sub Form_Load()
me!cboJob.requery
End Sub

but that didn't seem to help.

Thanks
Dave
Click to expand...
Click to expand...
 
Back
Top