Hi all

several years ago I bumped up against the max no. of controls
permitted on a form (around 750 I seem to remember). Does anyone know
if this still applies in A2010? Can't find anything useful in Help.

I'm building a Time Sheet form - unbound with unbound controls - that
needs 15 minute slots from 6am to midnight for a full (7 day) week and
this equates to around 970 controls! I have just lost a couple of
hours graft because the form corrupted when saved ... I was doing
regular saves of the form but not copying the whole mdb. Big mistake!

JB

Don't know if the limit is still the same (754?), but you can use sub-forms
with multiple controls. A sub-form is one control on a form.

So you might try using 7 sub-forms (one for each weekday). Each sub-form can
have up to 754 controls. I would think that once you have one daily form
created, you could save it as another daily form.

Then add the daily forms as sub-forms to your main form.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 4/27/2011 11:10 AM, jbguernsey wrote:

On Apr 27, 4:56*pm, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Thanks John.

Subforms is probably a good idea. I was hoping to avoid the extra
'overhead' of having to refer to controls on subforms. False economy,
it looks like.

JB

I'm reasonably sure, from more years experience in application software
development that I like to admit, that once you had to maintain/enhance that
system, you would wish to High Heaven that you'd used something simpler than
a form with nearly a thousand controls, each of which has to be accessed via
VBA code.

I also suspect that having such a form likely indicates some database design
issues, or you'd be dealing with the database itself in a different, simpler
way... even if the users are accustomed to a paper form with many, many
entries, it will pay to simplify life for both them and you, if you create a
normalized database and deal with it in a simpler way.

Larry Linson
Microsoft Office Access MVP


"jbguernsey" <jeff (AT) angelsystems (DOT) co.uk> wrote

On Apr 27, 4:56 pm, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Thanks John.

Subforms is probably a good idea. I was hoping to avoid the extra
'overhead' of having to refer to controls on subforms. False economy,
it looks like.

JB

On Apr 27, 6:48*pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
Hi Larry.

I agree. Unfortunately I'm not able to think of any reasonable way to
allow the client to have the functionality he requires with the
existing structure ... at least, not with the budget that applies -
and I don't want to walk away, there's too strong a relationship
between us.

I've looked at much of the time sheet stuff that's around but it
doesn't fit well enough. Client needs various engineers to be able to
enter what they call Job Action data (a Job may have several different
activities in it) into an existing database using 15 minute time
slots. The existing structure was not set up with this kind of thing
in mind and it's too late to make major changes.

It's one of those 'I wouldn't start from here' situations that
occasionally develop when a system is required to be extended into
areas it was never intended to get into!

I can't blame the original designer - it was me - but the system was
originally meant to be a quick and crude, very low-cost, limited,
temporary solution to be used until the (new) business settled down
and time and money could be allocated to a properly designed system
(and not by me). After nearly 20 years developing using Access I
should have known better! I've lost count of the number of times I've
told myself not to listen to the Client about this sort of thing - and
yet I *still* find myself falling into the same old trap. I guess I'm
just a hopeless case!

I'll bite the bullet and use 7 subforms (John's suggestion) and put it
down to experience ... and *try* to learn from it (yet again).

Thanks for the input.

JB

Access Developer wrote:
If they are made the way controls are meant to be made - drawn by hand -
then yes.
But there is another way - figure out the coordinate and use
createcontrol. Some interesting coloring and shapes can be programmed.
The way to catch events is to conclude from coordinates from labels that
fills same screen array as many controls.

It is an alternative use of 'office access' - and isn't the work arounds
interesting too?

It was unbound controls - hasn't much with database to do - just using
office access to make som user interface.

"jbguernsey" <jeff (AT) angelsystems (DOT) co.uk> wrote

You could create a table structure that would allow you to use bound
controls. Then export the changes to the live data when the record is saved.

"ron paii" <none (AT) nospam (DOT) com> wrote

simple and fast. I can strongly recommend it.
But if I could add one tit bit of experience on my recent project.
In the smarts that map to a fro between the normalised SQL tables and the
bound temporary work table - my most recent project finds the live records
through a composite key of about 12 fields.(these values get scraped off
various fields on the input form)
This has caused me no end of grief getting the routines just right.
If I had the chance to do it all over again - for every input field in the
temporary table/input form, I would carry next to it, hidden, the unique id
field pointing back in to the real record.
Sure this is doubling up the number of fields - but now that you are using a
bound form instead of many many unbound cells, you are still miles ahead.

PS - synchronising back to the live table when the user performs multiple
line deletes against the work table is a pain in the butt - you have to
queue deletes up from every call to the "on delete" event and then process
them in the "delete confirmed" event

Tony Epton

"aceware" <aceware (AT) iinet (DOT) net.au> wrote


As for deletes; mark the record as deleted in the "on delete" event, then
cancel the event. Call a function to process the deleted records in the next
on-current event.

"jbguernsey" <jeff (AT) angelsystems (DOT) co.uk> wrote

I've used similar reasoning (well, "rationalization" is a better term)
before to avoid doing what I knew was necessary and the only case in which I
have NOT regretted it later was one where the client went out of business
(for completely unrelated reasons, though maybe "poor judgement" might have
applied) before it rose up to bite me in the tender spots.

I offer my best wishes that your experience with this one will differ from
my experiences.

But, just a thought -- once I determined that I just would not quietly go
along with doing something that I knew was going to later "play alligator",
and explained thoughtfully to my clients, I didn't lose any jobs or clients
though some of them did opt that they wanted to pursue the "band-aid
approach" even though they knew they'd have to "pay more later" to fix it.
After all, the client may not always be right, but they are the ones paying
the bill, and they always knew more about their own circumstances than I
did. But, for my part, I owed them a clear and cogent explanation of the
choices and potential consequences.

I have, I will admit, walked away from some "messes" before establishing a
contractual relationship with that potential client. Back in the 1990's, a
lot of recruiters came to me for referrals to Access developers because I
kept a list of people in my user group who were interested in contract work
or employment. When a recruiter described an "opportunity" as "90% done,
all you have to do is finish it and add a few reports, shouldn't take more
than a couple of weeks", I just would fax or e-mail them the list and let
them call others. And, I even made marketing calls on some potential
clients and found situations I didn't want to deal with, and I'd politely
decline.

Best of luck and best regards,

Larry Linson
Microsoft Office Access MVP