473,382 Members | 1,717 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Moving between records in a Form is slow in certain Combo Boxes

112 100+
Hi All
I am using Access 2010. My Access Database is slow when moving between records on a form when my cursor is in certain Combo Boxes. When the cursor is in any other field, weather it is a Text, List or another Combo Box, the records move at normal speed. When I put the cursor in one of 3 Combo Box fields on my form, the records slow down to a good 5 sec or more between records as you advance through them. This only occurs when I have been using the database for a while and not when I first open it. If I close the database and reopen it, it runs at normal speed again even when in those three Combo Box fields. It seems random as to when the slow down start and it is really annoying to have to close the database and reopen to fix it. The Combo Boxes in question pull from a field in my main table and use “Select Distinct” in the “Row Source” property. My database now has over 27,000 records and was not having this issue before it hit over 26,000 records. I am not sure where the issue is, so I am not sure how to find a solution?

Another few notes about the fields: This slow down occurs in Form view and List view. Each field that the Combo Boxes pull from has data in every record, no blanks. One of the Fields has 8 distinct choices, one has about 150 and the other has about 500. The fields are not limited to existing choices so new ones can be added.
Any help appreciated.

Thanks
Oct 21 '15 #1

✓ answered by zmbd

oooohhhhhkayyy....

Let me see if I get this right... you have a table with 27,000+ records and you are going over one field in that table to pull the unique entries?

Every time you advance thru the controls, the PC is re-running that query over and over again tying up memory, eventually forcing the cache to flush and fill for every record used as a rowsource for the control... such a usage I doubt was ever envisioned.

From what little information we have, the only long term solution I see here is to normalize that data post-haste. This way, Description.[Permanent Location Site] should be a foreign key to a related table that has all of your location sites as a single entry not the same value over and over and over and over...

So your table name could be
[tbl_Permanent_Location_Site]
where you have the entry one time for say "666 Mockingbird Ln" with a primary key = 10. Then in [Description]![Permanent Location Site]=10 for each record where he item is at "666 Mockingbird Ln" and your control would use a query from [tbl_Permanent_Location_Site] with what I suspect would be only a few 100 entries.

11 2269
zmbd
5,501 Expert Mod 4TB
what exactly is the record source for the comboboxes?
Oct 29 '15 #2
Redbeard
112 100+
Hi zmbd

Sorry for my late reply, when someone answers my questions I used to get a response via email, which I am now not getting? Not sure if it is a spam filter on my end but I never received notification that you replied. I did check back on my question for the first 5 day and then gave up checking. Again sorry for the late reply.
Back to your question, the record source is pulled directly from my main table onto my main form. In all cases the Data Type is Text.
Dec 9 '15 #3
zmbd
5,501 Expert Mod 4TB
Can you post the SQL you are using for the Control's with issues. Please remember to use the [CODE/] formatting around each block of script.
Dec 9 '15 #4
Redbeard
112 100+
Here you go
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 
  2.    Description.[Permanent Location Site] 
  3. FROM Description 
  4. WHERE (
  5.    (Not (Description.[Permanent Location Site]) 
  6.       Is Null)) 
  7. ORDER BY Description.[Permanent Location Site]; 
Dec 9 '15 #5
zmbd
5,501 Expert Mod 4TB
oooohhhhhkayyy....

Let me see if I get this right... you have a table with 27,000+ records and you are going over one field in that table to pull the unique entries?

Every time you advance thru the controls, the PC is re-running that query over and over again tying up memory, eventually forcing the cache to flush and fill for every record used as a rowsource for the control... such a usage I doubt was ever envisioned.

From what little information we have, the only long term solution I see here is to normalize that data post-haste. This way, Description.[Permanent Location Site] should be a foreign key to a related table that has all of your location sites as a single entry not the same value over and over and over and over...

So your table name could be
[tbl_Permanent_Location_Site]
where you have the entry one time for say "666 Mockingbird Ln" with a primary key = 10. Then in [Description]![Permanent Location Site]=10 for each record where he item is at "666 Mockingbird Ln" and your control would use a query from [tbl_Permanent_Location_Site] with what I suspect would be only a few 100 entries.
Dec 12 '15 #6
Redbeard
112 100+
Ok, so what you are saying is that I create a separate table that has all my Permanent Location Sites in it, so let’s say 100 entries. Then link that to my main table’s Primary Key to my Permanent Location Sites table, so each site is a numeric value in my main table. Now I change my Combo Box field on my main form to read the 100 values from my Permanent Location Sites table, so that it is not running a query every time. However, when I select a value from that field, it stores it as the numeric value in my main table. Is that correct?
Dec 15 '15 #7
zmbd
5,501 Expert Mod 4TB
Yes I believe you have it right.

So you would have something like:

[tbl_PermanentLocation]
[tbl_PermanentLocation]![PK_PermanentLocation] (autonumber)
[tbl_PermanentLocation]![PermanentLocation_name] (text(25))
Expand|Select|Wrap|Line Numbers
  1. [PK_PermanentLocation][PermanentLocation_name]
  2. [         1          ][Mercury               ]
  3. [         2          ][Venus                 ]
  4. [         3          ][Earth                 ]
  5.  
[tbl_Description]
[tbl_Description]![PK_Description] (autonumber)
[tbl_Description]![FK_PermanentLocation] (Numeric(long)
[tbl_Description]![other fields]
Expand|Select|Wrap|Line Numbers
  1. [PK_Description][FK_PermanentLocation][other fields]
  2. [       1       ][           1        ][other fields]
  3. [      213      ][           1        ][other fields]
  4. [      500      ][           3        ][other fields]
Instead of having "Mercury" entered twice you have only the value "1".
This also will help prevent typos:
Mercury vs. Mercery vs. Mercurry vs. Mercury
and should reduce the data physical file size on disk/memory.


+ Your CBOBX would have a control source property = [tbl_Description]![FK_PermanentLocation]

+ The row source property could point directly to the [tbl_PermanentLocation], a stored query, or you can enter the SQL for the query directly into the row source property. I prefer a query (either as Stored or directly as I can order the entries ie:
Expand|Select|Wrap|Line Numbers
  1. SELECT [PK_PermanentLocation]
  2.    , [PermanentLocation_name]
  3. FROM [tbl_PermanentLocation]
  4. ORDER BY [PermanentLocation_name];
+ Using the [tbl_PermanentLocation] or the above query table as the row source, set the bound column to 1 on the data tab. I usually also set limit to list to yes and allow edits to no.
++ on the format tab
Column widths 0";1"
(the zero hides the bound column, set second wide enough for the user to understand the selection. If the first entry isn't 0, then "1" will display instead of "Mercury" after the update.)
++ Column heads, up to you, I usually set to no
++ List Rows, I usually set to at least 10, more or less depending on data and how far the user needs to scroll; however, I rarely set higher than 20. As I use the auto-fill the selection narrows down quickly as the user types.
++ List Width = I add all of the column widths and then add at least 1/2inch (0.5") to allow for the scrollbar.

+ The normalization will add a little bit to your queries if you need names; however, once you get used to the concept this becomes second nature.
Dec 15 '15 #8
Redbeard
112 100+
Thanks zmdb this is great! I guess I have a lot of work to do as this will affect at lest 6 fields on my main form.
Dec 16 '15 #9
zmbd
5,501 Expert Mod 4TB
I would be great if you'll give us an update as thing progress.

Make-table queries can help you in such a large project. You have the basis in your CBO now with the "SELECT DISTINCT" row-source :)

Once you have the normalized dataset, you can then go back into your table With an UPDATE query... initially leave the field as text data type and use the UPDATE against your new tables. Then go back in to design and change the field to numeric(long). Finally establish your table relationships. I personally avoid the "cascade delete" as it is too easy to accidently delete records; however, the "cascade update" I always set.

Remember the Programmer and IT Mantra:
Never work on the production database.
Never have just one development copy...
BACK UP, BACK UP, BACK UP, BACK UP,

:)

BOL
Dec 16 '15 #10
Redbeard
112 100+
I will update when I get a chance to resolve this problem as it is a big task and will take some time. Thanks again.
Dec 16 '15 #11
zmbd
5,501 Expert Mod 4TB
Split a related question to:
https://bytes.com/topic/access/answe...et#post3800534
Jan 18 '16 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: MrPaulC | last post by:
Hey, I wonder if anyone can possibly help me with this problem. I'm really struggling with it. Ok, I have a form with a childform inside it which is in datasheet view, this form has two combo...
2
by: ddog | last post by:
I have a form with 3 text fields (one of which is a zip code) and 5 combo boxes. The combo boxes are all set with the first value as 'selected' when the page is first displayed. The 3 text fields...
1
by: Chrish1987 | last post by:
Dear All, I have the following Tables: Dealer: DealerID - AutoNumber DealerName - Text Business: Business ID - AutoNumber
6
by: Dave | last post by:
I want to put the information that the user selects in my combo boxes into a subform that lies on the same form as the combo boxes. Thanks for your help already, Dave
1
by: Dave | last post by:
Hello all, First I'd like to apologize...This post was meant to be put in my previous post, but I tried many times without success to reply within my previous post. Now here goes... I have a...
11
by: jgoodnight | last post by:
Hi, I have a form with three combo boxes: Area, Sub-Area, and Factor. I've set up the Sub-Area combo box to list only those areas that are part of the selected area. I've set up the Factor combo...
12
by: rhapsodysolutions | last post by:
I have a form with 7 combo boxes to to search various fields in a table in Access 2007. I am trying to construct "dynamic" or conditional SQL for my subroutine, that would only use the values from...
8
by: wirejp | last post by:
I am using Microsoft Access 2010.I am trying to perform a search in a form (this form is a continuous form) using two combo boxes: a combo box called ClientID and another combo box called...
1
by: wgreen | last post by:
I have not done any access work in a lot of years and I cannot figure out my current problem. 1. I have a single table database that has 30 columns. 2. I am creating reports to extract certain...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.