Bill Feder
asked on
Access 2010
We had been using a combo box to look up records. For 15 years This combo box
sorted all the records alphabetically on customer name. All of a sudden
we can only view up to customers beginning with " y ". I could not scroll down
any further. Although I had thought something happen to all the record starting
with " z " I was reassured when I looked in the table and varified that all the
records beginning with "z" were in fact there. This look up has just about 65,900 records.
Are there limitation I am not aware about ?
Thanks
sorted all the records alphabetically on customer name. All of a sudden
we can only view up to customers beginning with " y ". I could not scroll down
any further. Although I had thought something happen to all the record starting
with " z " I was reassured when I looked in the table and varified that all the
records beginning with "z" were in fact there. This look up has just about 65,900 records.
Are there limitation I am not aware about ?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 250 points for PatHartman's comment #a41258806
Assisted answer: 250 points for JDettman's comment #a41259026
Assisted answer: 0 points for Billxxxx's comment #a41259068
for the following reason:
I made a mistake in accepting mine. I was just wanted to send a comment on
how i fixed it.
Accepted answer: 250 points for PatHartman's comment #a41258806
Assisted answer: 250 points for JDettman's comment #a41259026
Assisted answer: 0 points for Billxxxx's comment #a41259068
for the following reason:
I made a mistake in accepting mine. I was just wanted to send a comment on
how i fixed it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All of the responses were good and very helpful. The note about speed interests
me because i never saw this as a problem. The combo Box I am talking about
Looks for customers work orders. Which is why it reached it's limitation. the combo
box includes Customer, Work order number, date, and comments. This is also sorted
alphabetically by customer. When looking up a particular work order there are
many ways to view the info before selecting it. I never felt as though it was slow.
However, I do use a lot of columns in many other Combo boxes. Should I anticipate
some problems? I fixed my current problem by putting a limitation based on date. I then added
another combo box called "Old Work orders" which would bring up everything prior to the date
set in the previous combo box. This of course will be a problem again some time in the near future Unless
i make the date a variable based on current date. I know it will require some code. Any thoughts?
Thanks
me because i never saw this as a problem. The combo Box I am talking about
Looks for customers work orders. Which is why it reached it's limitation. the combo
box includes Customer, Work order number, date, and comments. This is also sorted
alphabetically by customer. When looking up a particular work order there are
many ways to view the info before selecting it. I never felt as though it was slow.
However, I do use a lot of columns in many other Combo boxes. Should I anticipate
some problems? I fixed my current problem by putting a limitation based on date. I then added
another combo box called "Old Work orders" which would bring up everything prior to the date
set in the previous combo box. This of course will be a problem again some time in the near future Unless
i make the date a variable based on current date. I know it will require some code. Any thoughts?
Thanks
I'd say two combo's cascaded; one for customers, then one for work orders, with the second being filtered by a "back to: mm/dd/yy" date to keep the speed up.
Jim.
Jim.
Decide how many years back you need to go on a regular basis. For example, 5 years
Where SomeDate > DateAdd("yyyy", -5, Date())
Or, use a date range by adding two new controls to the form. In this case, you'll need to verify that the date range is not longer than what you will allow.
If DateDiff("yyyy", Me.txtFromDT, Me.txtThruDT)) > 5 Then
Msgbox "The date range must be no more than 5 years",vbokOnly
Exit Sub
Endif
And the Where clause
WHERE SomeDate Between Forms!yourform!txtFromDT AND Forms!yourform!txtThruDT
Where SomeDate > DateAdd("yyyy", -5, Date())
Or, use a date range by adding two new controls to the form. In this case, you'll need to verify that the date range is not longer than what you will allow.
If DateDiff("yyyy", Me.txtFromDT, Me.txtThruDT)) > 5 Then
Msgbox "The date range must be no more than 5 years",vbokOnly
Exit Sub
Endif
And the Where clause
WHERE SomeDate Between Forms!yourform!txtFromDT AND Forms!yourform!txtThruDT
With long combo lists, I use a technique similar to Jim's comment above, but instead create a combo box which I had behind the label associated with your clients. First, cut that label and then paste it back above the clients combo. Then create a new combo, send it to back, behind the label. Then set the RowSourceType to Value List and add in some options as you can see in the attached file. Then modify the AfterUpdate of that 2nd combo box so that it sets the appropriate criteria and assigns a new RowSource for the clients combo box, sets the focus to the clients combo, and then drops it down. This saves on the screen real estate needed for the filter.
LongCombos.accdb
LongCombos.accdb
I have a combobox that would ostensibly hold ~6000 rows if I permitted that.
But the point of a combo box is to permit point-and-click, and to not require either scrolling or typing.
For that, you have to keep things down to around 30 rows.
So, I have a series of three comboboxes.
The first is a general categorization of the 6000 items
The second is a sub-categorization of each general category
The third is the item
The user selects one of ~30 general categories
The second box is then cascaded updated with its data and has around 30 items
The user chooses something from it
The third box is then cascade updated and has 20-50 items.
Three clicks, but little or no scrolling and typing.
30 x 30 x 30 = 27000 items cut down to three click.
But the point of a combo box is to permit point-and-click, and to not require either scrolling or typing.
For that, you have to keep things down to around 30 rows.
So, I have a series of three comboboxes.
The first is a general categorization of the 6000 items
The second is a sub-categorization of each general category
The third is the item
The user selects one of ~30 general categories
The second box is then cascaded updated with its data and has around 30 items
The user chooses something from it
The third box is then cascade updated and has 20-50 items.
Three clicks, but little or no scrolling and typing.
30 x 30 x 30 = 27000 items cut down to three click.
ASKER
we now only only can't view a years worth of old records.
Is the limit 65,000 approx? I have read a lot of books on access but never read
that.
Thanks