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

[Redbeard]

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

[zmbd]

what exactly is the record source for the comboboxes?

[Redbeard]

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.

[zmbd]

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.

[Redbeard]

Here you go

SELECT DISTINCT 
   Description.[Permanent Location Site] 
FROM Description 
WHERE (
   (Not (Description.[Permanent Location Site]) 
      Is Null)) 
ORDER BY Description.[Permanent Location Site]; 

[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.

[Redbeard]

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?

[zmbd]

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))

[PK_PermanentLocation][PermanentLocation_name]
[         1          ][Mercury               ]
[         2          ][Venus                 ]
[         3          ][Earth                 ]
 

[tbl_Description]
[tbl_Description]![PK_Description] (autonumber)
[tbl_Description]![FK_PermanentLocation] (Numeric(long)
[tbl_Description]![other fields]

[PK_Description][FK_PermanentLocation][other fields]
[       1       ][           1        ][other fields]
[      213      ][           1        ][other fields]
[      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:

SELECT [PK_PermanentLocation]
   , [PermanentLocation_name]
FROM [tbl_PermanentLocation]
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.

[Redbeard]

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.

[zmbd]

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

[Redbeard]

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.

[zmbd]

Split a related question to:
https://bytes.com/topic/access/answe...et#post3800534