combo box with 6000 entries

[Guest]

I am trying to use a query against a table with 6000 records in a combo box.
It takes a long time for the combo box to load, and if I select a record not
in the first one fifth of the file, the name doesn't appear in the combo box.
Is there a better way that I should be doing this?

My query is as follows: SELECT Vendor.[#VEND], Vendor.VENDOR,
Vendor.[1ADDR], Vendor.[2ADDR], Vendor.[3ADDR], Vendor.[4ADDR]
FROM Vendor;

 

[John Nurick]

Hi Sandy,

If you're using the SQL below as the RowSource of a combobox, you'd do
better to omit the address fields and just use

SELECT Vendor.[#VEND], Vendor.VENDOR FROM Vendor

to reduce the amount of data needed to load the combobox. Then when the
user selects a vendor, use the #VEND value from the combo to retrieve
the other fields from the table.

For a way to handle very long lists with a combobox, see
http://allenbrowne.com/ser-32.html

I am trying to use a query against a table with 6000 records in a combo box.
It takes a long time for the combo box to load, and if I select a record not
in the first one fifth of the file, the name doesn't appear in the combo box.
Is there a better way that I should be doing this?

My query is as follows: SELECT Vendor.[#VEND], Vendor.VENDOR,
Vendor.[1ADDR], Vendor.[2ADDR], Vendor.[3ADDR], Vendor.[4ADDR]
FROM Vendor;

 

[Guest]

I took all but one of the address fields out, and it did speed up the
loading, but it still won't put the vendor name if it is not in the first
1600 records of the table

Hi Sandy,

If you're using the SQL below as the RowSource of a combobox, you'd do
better to omit the address fields and just use

SELECT Vendor.[#VEND], Vendor.VENDOR FROM Vendor

to reduce the amount of data needed to load the combobox. Then when the
user selects a vendor, use the #VEND value from the combo to retrieve
the other fields from the table.

For a way to handle very long lists with a combobox, see
http://allenbrowne.com/ser-32.html

I am trying to use a query against a table with 6000 records in a combo box.
It takes a long time for the combo box to load, and if I select a record not
in the first one fifth of the file, the name doesn't appear in the combo box.
Is there a better way that I should be doing this?

My query is as follows: SELECT Vendor.[#VEND], Vendor.VENDOR,
Vendor.[1ADDR], Vendor.[2ADDR], Vendor.[3ADDR], Vendor.[4ADDR]
FROM Vendor;

 

[John Spencer]

When you say it won't put the vendor name in, do you mean
-- you don't see it in the drop down list or
-- that even if you type it in the combobox it is not displayed after you
leave the combobox
-- or that you are trying to get the value for the column displaying Vendor
and put that into another control and you get an error

Some things to try to help diagnose the problem.
-- Add an order by clause to the SQL statement so the records are in order.
Try typing in the selection value associated with the last record in the
comboboxes query. If that works and displays the value, then the combobox
is not fully loading all the records.
-- Try dragging the selection box to the bottom of the scroll bar, let loose
and drag it again. Does that increase the number of vendors displayed? If
so then the combobox is not fully loading all the records.

If one of the above is true, you should be able to fix this problem with a
bit of code to force the combobox to fully load. Basically, you will need
to a statement like the following in the form's module. You could place the
code in the combobox's GotFocus event or in the Form's current event or ???

Dim LRowCount as Long
LRowCount = Me.ComboboxName.ListCount

That usually forces the combobox to load fully.

I took all but one of the address fields out, and it did speed up the
loading, but it still won't put the vendor name if it is not in the first
1600 records of the table

Hi Sandy,

If you're using the SQL below as the RowSource of a combobox, you'd do
better to omit the address fields and just use

SELECT Vendor.[#VEND], Vendor.VENDOR FROM Vendor

to reduce the amount of data needed to load the combobox. Then when the
user selects a vendor, use the #VEND value from the combo to retrieve
the other fields from the table.

For a way to handle very long lists with a combobox, see
http://allenbrowne.com/ser-32.html

I am trying to use a query against a table with 6000 records in a combo
box.
It takes a long time for the combo box to load, and if I select a record
not
in the first one fifth of the file, the name doesn't appear in the combo
box.
Is there a better way that I should be doing this?

My query is as follows: SELECT Vendor.[#VEND], Vendor.VENDOR,
Vendor.[1ADDR], Vendor.[2ADDR], Vendor.[3ADDR], Vendor.[4ADDR]
FROM Vendor;

 

[Guest]

Thank you for your response!

My problem is that even if you type the value into the combobox, if it is
not in the first 1260 records (in the order of the order by clause) it is not
displaying after I leave the combobox. The actual code is being inserted
into the record, but the vendor name is not displayed.

I tried your suggestions, and determined that the combobox is fully loaded.
I added the rowcount code that suggested and in debug mode determined that
the count is set to the right number of records. Also, the scroll box shows
all the records the first time it is dragged to the bottom.

I am at a loss as to what to do next.

When you say it won't put the vendor name in, do you mean
-- you don't see it in the drop down list or
-- that even if you type it in the combobox it is not displayed after you
leave the combobox
-- or that you are trying to get the value for the column displaying Vendor
and put that into another control and you get an error

Some things to try to help diagnose the problem.
-- Add an order by clause to the SQL statement so the records are in order.
Try typing in the selection value associated with the last record in the
comboboxes query. If that works and displays the value, then the combobox
is not fully loading all the records.
-- Try dragging the selection box to the bottom of the scroll bar, let loose
and drag it again. Does that increase the number of vendors displayed? If
so then the combobox is not fully loading all the records.

If one of the above is true, you should be able to fix this problem with a
bit of code to force the combobox to fully load. Basically, you will need
to a statement like the following in the form's module. You could place the
code in the combobox's GotFocus event or in the Form's current event or ???

Dim LRowCount as Long
LRowCount = Me.ComboboxName.ListCount

That usually forces the combobox to load fully.

I took all but one of the address fields out, and it did speed up the
loading, but it still won't put the vendor name if it is not in the first
1600 records of the table

Hi Sandy,

If you're using the SQL below as the RowSource of a combobox, you'd do
better to omit the address fields and just use

SELECT Vendor.[#VEND], Vendor.VENDOR FROM Vendor

to reduce the amount of data needed to load the combobox. Then when the
user selects a vendor, use the #VEND value from the combo to retrieve
the other fields from the table.

For a way to handle very long lists with a combobox, see
http://allenbrowne.com/ser-32.html

On Tue, 9 May 2006 11:19:03 -0700, SandyR

I am trying to use a query against a table with 6000 records in a combo
box.
It takes a long time for the combo box to load, and if I select a record
not
in the first one fifth of the file, the name doesn't appear in the combo
box.
Is there a better way that I should be doing this?

My query is as follows: SELECT Vendor.[#VEND], Vendor.VENDOR,
Vendor.[1ADDR], Vendor.[2ADDR], Vendor.[3ADDR], Vendor.[4ADDR]
FROM Vendor;

 

[Guest]

Strange. A combo box should be able to handle 64000 rows....

In another reply you mention an Order By but your SQL below doesn't show it.
If there is an order by, is that field indexed in the table?

Is the Vendor.[#VEND] field a primary key?

Is the combo box bound or unbound?

Is the database on a network? Is so how many other users and is it split?

 

[Guest]

Thanks for responding. Here are the answers to your questions:

The field is indexed in the table. The Order By clause was not in the
original query. I added it to the SQL because of the earlier suggestion.

vendor.[#vend] is the primary key to the vendor table.

the combo box is bound and that piece of it works properly, regardless of
where the record is located in the vendor table.

the database is not on the network - I am trying to develop it, so no one is
using it but me.

Strange. A combo box should be able to handle 64000 rows....

In another reply you mention an Order By but your SQL below doesn't show it.
If there is an order by, is that field indexed in the table?

Is the Vendor.[#VEND] field a primary key?

Is the combo box bound or unbound?

Is the database on a network? Is so how many other users and is it split?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to use a query against a table with 6000 records in a combo box.
It takes a long time for the combo box to load, and if I select a record not
in the first one fifth of the file, the name doesn't appear in the combo box.
Is there a better way that I should be doing this?

My query is as follows: SELECT Vendor.[#VEND], Vendor.VENDOR,
Vendor.[1ADDR], Vendor.[2ADDR], Vendor.[3ADDR], Vendor.[4ADDR]
FROM Vendor;

 

[Guest]

I have solved the problem. It was a type mismatch between the type of the
vendor number field in the table and the type of the vendor number field in
the database.

Thanks to all who tried to help.

Thanks for responding. Here are the answers to your questions:

The field is indexed in the table. The Order By clause was not in the
original query. I added it to the SQL because of the earlier suggestion.

vendor.[#vend] is the primary key to the vendor table.

the combo box is bound and that piece of it works properly, regardless of
where the record is located in the vendor table.

the database is not on the network - I am trying to develop it, so no one is
using it but me.

Strange. A combo box should be able to handle 64000 rows....

In another reply you mention an Order By but your SQL below doesn't show it.
If there is an order by, is that field indexed in the table?

Is the Vendor.[#VEND] field a primary key?

Is the combo box bound or unbound?

Is the database on a network? Is so how many other users and is it split?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to use a query against a table with 6000 records in a combo box.
It takes a long time for the combo box to load, and if I select a record not
in the first one fifth of the file, the name doesn't appear in the combo box.
Is there a better way that I should be doing this?

My query is as follows: SELECT Vendor.[#VEND], Vendor.VENDOR,
Vendor.[1ADDR], Vendor.[2ADDR], Vendor.[3ADDR], Vendor.[4ADDR]
FROM Vendor;

 

[dbahooker]

uh there is a setting 'dont display lists with more than 1000 items'

tools, options better luck next time