too manny controls on a form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a limit? I think my form must me too complex because now it is
almost too slow to be useful. I have over 15 combo boxes that "drill's-down"
or qureries the results into 3 subforms. The main table has over 30,000
records. help? Is there a better way to "Drill-Down" data based on a users
choice? User can choose from 1-to-15 options.
 
That is going to be v e r y s l o w !

It is not the number of controls, it is that each is having to pull a lot of
data. I had a similar problem with 6 choices that had to be multiselect and
cascading, that is number 2 had to pull data based on number 1. Number 3 had
to pull data based on number 2 unless not choices were made in number to,
then it had to base on Number 1.

Of course, since they wanted multi select, I had to use list boxes.

The first problem I hit was it took forever to load. The main table has
about 38000 and with only 6 choices, it took too long. So, here is what I
did.

I did not assign a row source for any of the list boxes. In fact, I hid
them and put a command button for them to make them visible (what I really
did was set the height to 0, then changed the height when they click the
button. Then the code behind the button determines the row source bases on
what was chosen in the previous list. I have it working so that they always
have to work from 1 to six, but they don't have to choose any or they can
just do 6 or what ever.

The behaviour is that if they say have 4 selected, and go click on 2, 3 and
4 disappear and their row sources are set to "".

Sorry to be so long winded, but I am hoping this will give you some ideas.
The main thing this all accomplishes is giving the appearance of speed.
Because the form is loading not data when opened, it opens very fast. Then
when he selects one list, it doesn't take that long for one list to load.
One of the tricks I do, and again it is for visual effect, is I don't make
the list visible until the rowsource query has been constructed and
executled. That way, a user is not looking at an empty box. It opens with
the data.

Hope this will help. Be glad to give more advice on this if you need it.
 
Is there a limit?

Yes, it's around 700. Or that is 700 added over time. So if you add and
delete a control that's one spent.
But you're nowhere near it off course.

Jesper Fjølner
 
Thank you Klatuu! More advice would be most appreciated! Wow...I'm not sure
where to begin? I am a new user with Access, but most comfortable in Excel.
I've enhanced several of my spreadsheets over the years with a little VB
coding. Mostly through "trial & error", but they seem to work. As you might
have guess, the spreadsheets are getting pretty large now and are almost to S
L O W to be useful. That's why I've decided it's time to move everything to
Access. Access with some VB coding is certainly the way to go, but I have
limited knowledge of both.

My goal is to create a form that has a view of combo boxes (filter options)
at the top with to 2 or 3 subforms that look like spreadsheets below but
still on the main form. Hopefully the user can select any of the 15 combo
boxes and then the results will populate the subforms and the other 14 combo
boxes.

Basically, I am trying to get the same results one can get within a
spreadsheet. I often use the "Auto filters" across the top row. Then I can
filter by any column that applies. Each column that is filtered reduces the
number of rows and also the number of choices for the other columns with
filters (Drop-down list).

How do I do this? Where do you suggest I start? Currently, I am pretty
happy with the view of my main form. Surprisingly, my subforms and 15 combo
boxes appear to populate correctly. Thanks to the help of a few MVPs I
synchronized 2 combo boxes and built on from there. I don't think the MVPs
had any clue I was going to go nuts and keep adding on or I'm sure someone
would have come up with a more efficient plan.

What do I do now?
 
If you allow the user only one selecton per field, then combo boxes rather
than list boxes is the way to go. If you can already do cascading combos,
then you are on the right track. The only difference here is that you need
to check each combo to see if something is selected and if it is, included it
in the filtering. The part that will be more difficult, if you are not
familiar with SQL, is to construct a query based on the selections the user
had made. You might be able to use a stored query and set a criteria on each
of the fields the combos represent For each, you could use something like
this (untested air code)

IIf(IsNull(Me.Combo1), "Like *", Me.Combo1)
 
Back
Top