• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

Release Access 2010 Combo Box Row Source after user selects a value in the Combo Box

I have a front end access 2010 form with a combo box that has a row source to a SQLServer database table that has ~30000 records.

When a user clicks the dropdown, it creates a ton of Processes in SQL - Activity Monitor in a Suspended State.

I want to know if there is a way that once a user selects something from the drop down that i can release the row source so i won't have hundreds of "Suspended" Processes
0
BFanguy
Asked:
BFanguy
3 Solutions
 
BFanguyAuthor Commented:
Also, If the user clicks the combo box and picks nothing, it will not release all the suspended records until they close the form.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> ...access 2010 form with a combo box that has a row source to a SQLServer database table that has ~30000 records.
That's the equivalent of trying to shove a watermelon through a garden hose.  Are users *really* expected to choose from 30,000 records in an Access combo box?

A vastly better idea is to write a query that reduces the number of rows to something much more manageable, say a hundred or less, and select only the columns that are absolutely needed.  

Or maybe design two combo boxes, one that feeds off the other, to limit the number of rows.  For example, there are 3,100+ counties in the United States, and rather than have a combo box that has all 3,100 create a combo box of the 55ish states and territories, then a second combo box that uses the first one as a parameter and only displays the hundred or less counties in that state.
0
 
Rey Obrero (Capricorn1)Commented:
see how it is done from http://allenbrowne.com/ser-32.html
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
BFanguyAuthor Commented:
this particular combo box is for Part Numbers in the database.  they start typing the number and it brings them to the number needed.   As they type it narrows it down very quickly, but if they don't type anything right away, access starts building the entire recordset and it won't release the suspended records until they clear out the field or close the form.
0
 
BFanguyAuthor Commented:
Rey,  this is very interesting and I will give this a try.  thanks.
0
 
BFanguyAuthor Commented:
Rey,  go this to work - set it to where they don't get anything until they key in 9 letters.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
probably you could use a textbox where the user types what info he/she has and then this act as the criteria for the combobox.....
Also if you need to have these 30000+ records then an alternative is to use a temp local  table which you could update periodically....
0
 
BFanguyAuthor Commented:
thanks guys
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now