Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-11-02
8
Medium Priority
?
58 Views
Last Modified: 2016-11-03
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
Comment
Question by:BFanguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:BFanguy
ID: 41871182
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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 41871183
> ...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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 41871188
see how it is done from http://allenbrowne.com/ser-32.html
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:BFanguy
ID: 41871190
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
 

Author Comment

by:BFanguy
ID: 41871198
Rey,  this is very interesting and I will give this a try.  thanks.
0
 

Author Comment

by:BFanguy
ID: 41871248
Rey,  go this to work - set it to where they don't get anything until they key in 9 letters.
0
 
LVL 19

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 500 total points
ID: 41873017
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
 

Author Closing Comment

by:BFanguy
ID: 41873020
thanks guys
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question