Access combo box limitation

Hi Experts,

I have a combobox that is bound to field EmployeeID and has the following 3 Columns,
 0;ID;2";EmployeeName;.5";EmployeeTitle

Now suddenly users are getting "invalid operation" when right clicking on field and selecting filter by selection, or when trying to sort by that column.

I made some experiments and realized that the count of records that exists in the employee’s table is the culprit..

Did someone experience this kind of error?

And what is the workaround/solution for it?
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
How many records are you trying to load into the Combo-box?

More than 65,536?
More than 10,000?
More than 1,000?

I would say anything over 500, or 1,000 at the very most, is probably too much for a user to scroll through anyway.

Perhaps you should consider other methods of selecting a desired value from those available.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>Now suddenly users are getting "invalid operation" when right clicking on field and selecting filter by selection, or when trying to sort by that column.
when you referring to the "field", is that the field in your form?

how is your form looks like? can you post a screenshot or a sample db here?
bfuchsAuthor Commented:
@Fanpages,

Its something above 20K, I realized that when having only 10K it works, not sure exactly when it gets broken..

Perhaps you should consider other methods of selecting a desired value from those available.

The problem is that the screen in question is used for dual purposes, data entry and also reporting, therefore I cant just have two tables joined and bound the employee field to the name of employee, as data of form would not be updatable.

Now another option would be to have the column source set as =Dlookup("Name","Employeestbl","ID = [EmployeesID]), however besides of slowing performance, this would also not allow the sorting and filtering by selection actions..

@Ryan,
See screenshot,
I am referring to employee drop down of my continuous form.

Thanks,
Ben
untitled.bmp
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Consider:

1. Using a popup form for selecting an employee

or

2. Using a popup form for adding/editing a record (thing this would be a better user experience.

 but possibly for a short fix, you have three columns in that combo, can you combine columns 2 & 3?

Concatenate the name and the title and reduce the combo to two columns.  That may get you by for now.

But over 20K entries for a combo is too much.

Jim.
bfuchsAuthor Commented:
Hi Jim,

At the moment I am having trouble opening that screen...see link below

http://www.experts-exchange.com/questions/28712250/Access-form-suddenly-started-giving-duplicate-field-error-message.html

will let you know when that get fixed so I can test this combined fields option.

1. Using a popup form for selecting an employee
How will this help me use the sorting and filtering by selection?

2. Using a popup form for adding/editing a record (thing this would be a better user experience.

The problem is that users got used to the easy way, and I will have a hard time introducing something less efficient..(in their eyes).

Thanks,
Ben
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ben,

   On your form, you can have text boxes to hold  the employee ID and name rather than a combo with the list.  

  You would still be able to sort/filter on these fields.   The pop-up form could use a listbox or a for in  continuous view to allow selection of an employee.

  You can also carry it farther by having filters for department/title by default, or a column of buttons like an old style Rolodex to limit the number of choices.  


<<The problem is that users got used to the easy way, and I will have a hard time introducing something less efficient..(in their eyes).>>

  I understand, but they also need an app that works.  I've found that most don't object to looking at a list as "a summary view", and learning to work with a pop-up  as an add/edit interface.  

 This is a very common technique as more often than not, what can/should be presented as a summary list has far less detail than what may be needed for the record.

You may also see this implemented on a tab control; one tab for the list, and one or more tabs for the detail.

Jim.
bfuchsAuthor Commented:
Hi Jim,

On your form, you can have text boxes to hold  the employee ID and name rather than a combo with the list.  
Not sure what you mean by that, I should save them as part of the record, for example every time an EmployeeID gets selected, the name of employee should be saved in an extra field, or perhaps you are referring to a different method?

Re modify the screen, this is a schedule screen where they like to have a persons full week/month schedule in front and just go with the mouse directly to the row/column they need to edit, I would only try to change if there is absolutely no other choice..

Concatenate the name and the title and reduce the combo to two columns.  That may get you by for now.
This while enabling to do the sort/filter, is somehow causing other issues, as I posted in following link http://www.experts-exchange.com/questions/28712250/Access-form-suddenly-started-giving-duplicate-field-error-message.html, (basically a strange error message that duplicate ID was found in data source..), and the cause of it is the order by property of the form that after sorting by combo Employee, the order by is "Lookup_EmployeeID.Name", although that is the expected and worked till now, however now that we ran over the limit as stated in original post, this started acting up..

Thanks,
Ben
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Not sure what you mean by that, I should save them as part of the record, for example every time an EmployeeID gets selected, the name of employee should be saved in an extra field, or perhaps you are referring to a different method?>>

Few different methods:

1. Take your base query and join it to the employee table.   You'd then have the name, title, etc available.

2. A temp table with the additional fields required.   Join this to your main table and base the form on that.  When the form opens, you fill the temp table.   Upon display, you have all your field data.

3. Along the same lines; a temp table the form is based on.   Fill the table at the start, then base the form on that.

I've used all., but usually the last only when a user is editing a single record or performing a specific task.

#2 I've used a lot, and it works pretty well, although you need to be careful in order to keep it updateable, and of course refreshing the data which might be change by other users becomes an issue.   In your case, an employee name and title would change infrequently, so this is probably a good approach for you.  #1 would be the simplest.

 as far as the concatenation, I can't think of what issue that might cause. ..you should be fine unless your trying to refer to the title in some way.

Jim.
bfuchsAuthor Commented:
Hi Jim,

Sorry for the delay, however due to personal reasons, I was away for the week & just returned today, meanwhile we had a consultant looking into this matter and this is what he came up with..

We should change the SQL of the combo rowsource for the following.

SELECT Employeestbl.ID [b][u]AS EmpID[/u][/b], [LastName] & " " & [firstname] AS EmpName, Employeestbl.Title
FROM Employeestbl
ORDER BY [LastName] & " " & [firstname];

Open in new window


Again, this is only necessary for large quantity of records, as our other combo boxes work fine without any modification.

Thanks,
Ben

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks to all participants!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.