Maximum number of controls

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

Guest

My Access 2002 database has a data entry form that will accept only about 50
additional controls before a message appears stating that the maximum number
of controls for this form has been reached. Is there some way I can coax
Access to increase the maximum number of controls I can place on the form? I
have read that too many controls reduces speed, but the program is fast
enough at the moment and another 250 controls would make the database more
useful to me.
I could use two forms if I could get them to work in sync, displaying the
same records all the time, but I have not been able to accomplish this and I
am not sure it is possible.
 
My Access 2002 database has a data entry form that will accept only about 50
additional controls before a message appears stating that the maximum number
of controls for this form has been reached. Is there some way I can coax
Access to increase the maximum number of controls I can place on the form? I
have read that too many controls reduces speed, but the program is fast
enough at the moment and another 250 controls would make the database more
useful to me.
I could use two forms if I could get them to work in sync, displaying the
same records all the time, but I have not been able to accomplish this and I
am not sure it is possible.
Click to expand...

"ANOTHER" 250 controls!?

I'm sorry, but I cannot imagine a Form with 250 controls (not to
mention the Access limit of 754 controls) being anything other than
mind-numbing.

What's the structure of your Tables? Are you aware that there's a hard
limit of 2000 bytes actually occupied in any one record of a table?
How close are you to hitting that limit?

I *STRONGLY* suspect that this database needs normalization.

John W. Vinson[MVP]
 
Thank you for the information. I did not know that the maximum number of
controls for a form was 754, nor was I aware that the maximum number of bytes
per record was 2000. Most of my controls are combo boxes. I use the
database to record the details of patient visits, and the combo boxes reduce
the amount of typing necessary. In spite of all the controls, the form is
not cluttered at all, the reason being that they are spread over many pages.
I move easily to any page I want by means of command buttons that set the
focus using VBA code. I put text in only a limited number of fields for any
given encounter, and so I am not too worried about reaching the limit a
record will hold.
 
Thank you for the information. I did not know that the maximum number of
controls for a form was 754, nor was I aware that the maximum number of bytes
per record was 2000. Most of my controls are combo boxes. I use the
database to record the details of patient visits, and the combo boxes reduce
the amount of typing necessary. In spite of all the controls, the form is
not cluttered at all, the reason being that they are spread over many pages.
I move easily to any page I want by means of command buttons that set the
focus using VBA code. I put text in only a limited number of fields for any
given encounter, and so I am not too worried about reaching the limit a
record will hold.
Click to expand...

Ok...

You can recover the full 754 fields, but every time you change a
control on the form you "use up" one of the slots. I believe that you
can copy and paste the entire form to a new form - at worst, select
all the controls and paste them onto a new form.

HOWEVER... I'm still very queasy about your table design. Patient
visit details sounds like it should be several one to many
relationships, not a monstrously wide flat table. Maybe I'm worrying
unnecessarily but this still sounds like it could benefit from
rethinking the table design!

John W. Vinson[MVP]
 
You were asking about the table structure. There are a number of tables, but
most of the data is contained in two of them, the Patients table and the
Encounters table. The Patients table contains baseline information such as
first name, last name, date of birth, allergies, medications, past medical
history, etc. The Encounters table contains information related to each
visit, for example current symptoms, findings on examination, diagnosis, lab
tests ordered, and medications prescribed. The Data Entry form is based on a
query that draws fields from both the Patients table and the Encounters table.
 
You were asking about the table structure. There are a number of tables, but
most of the data is contained in two of them, the Patients table and the
Encounters table. The Patients table contains baseline information such as
first name, last name, date of birth, allergies, medications, past medical
history, etc. The Encounters table contains information related to each
visit, for example current symptoms, findings on examination, diagnosis, lab
tests ordered, and medications prescribed. The Data Entry form is based on a
query that draws fields from both the Patients table and the Encounters table.
Click to expand...

As I suspected.

You have several many to many relationships here, all embedded in
single non-normalized records of your tables:

Patients <--> Allergies
Patients <--> Medications
Patients <--> History
Encounters <--> Symptoms
Encounters <--> Findings
Encounters <--> Diagnoses
Encounters <--> LabTests
Encounters <--> Medications

There's sixteen tables right there - eight "lookup" tables of lists of
possible allergies, symptoms, lab tests, etc; and eight resolver
tables for the many to many relationships.

Your table structure IS WRONG. You're "committing spreadsheet upon a
database", a very common but none the less grievous error!

I would VERY strongly recommend that you *STOP*; fixing this form is
like arranging deck chairs on the Titanic. You will be much better off
stepping back and normalizing your table structures, and *then*
designing user-friendly forms with subforms to enter this data.


John W. Vinson[MVP]
 
I went to my large Encounters table and started adding fields. It was not
long before I reached the maximum, just as you predicted, and so I deleted
some of the new fields to make the database function again.

Then I created a new table called Shoulders and set up a number of fields
related to the various parts of the shoulder examination, eg
PalpationRightShoulder, PalpationLeftShoulder, AbductionRightShoulder,
AbductionLeftShoulder etc. I named the primary key ShoulderID, set the data
type for the primary key as number, and joined the Encounters table and the
Shoulders table by creating a one-to-one relationship between EncounterID and
ShoulderID. I populated the ShoulderID field with the existing EncounterID
data. I added the fields in the Shoulders table to the Data Entry query, and
then to the DataEntry form, which is based on that query. This allowed me to
add shoulder data to all existing records (over 4000 of them). In order to
get Access to accept shoulder data in a new record, I set the default for the
Shoulder ID text box to =EncounterID.

I was finally successful in getting a second data entry form, DataEntry2, to
work in sync with the Data Entry form, so that both forms displayed the same
record. The key here was to create a macro triggered by the On Current event
in the Data Entry form. This macro contains the following WHERE condition:
[EncounterID] = [Forms]![Data Entry]![EncounterID]

I believe I can now add as many as 754 additional controls (minus the ones I
used to set up DataEntry2), and that the only limitation I face is a
reduction in speed as the number of controls increases.

I really appreciate your help in all of this. I did not realize before I
posted my question that the discussion group was such a marvellous service.
 
I went to my large Encounters table and started adding fields. It was not
long before I reached the maximum, just as you predicted, and so I deleted
some of the new fields to make the database function again.
Click to expand...

Well... that's starting 300 yards down the track.

When you're first setting up a database, start by...

*turning off the computer*. Go into a different room. Take a pad of
paper and a #2 pencil.

Identify the real-life *things*, *people*, and *events* - called
Entities - relevant to your application.
Then I created a new table called Shoulders and set up a number of fields
related to the various parts of the shoulder examination, eg
PalpationRightShoulder, PalpationLeftShoulder, AbductionRightShoulder,
AbductionLeftShoulder etc.
Click to expand...

A Shoulder (if it's an entity at all which I very much doubt) has
properties: some shoulders are left shoulders, some shoulders are
right shoulders. I'd say that if you have a table of Shoulders at all,
you'll want a "side" property, which will have the value Left or
Right.

If you're treating a shoulder, you'll have a many to many relationship
between Shoulders and Treatments. To model a many to many relationship
you *don't* add new fields; you add a new *table* -
ShoulderTreatments. One row might have ShoulderID (referring to
patiend 3122's left shoulder), and TreatmentID 8 (palpation); the next
record might have ShoulderID 3122, TreatmentID 31 (manual reduction of
dislocation). "Fields are expensive, records are cheap" - tables
should grow down, not across.

I named the primary key ShoulderID, set the data
type for the primary key as number, and joined the Encounters table and the
Shoulders table by creating a one-to-one relationship between EncounterID and
ShoulderID. I populated the ShoulderID field with the existing EncounterID
data. I added the fields in the Shoulders table to the Data Entry query, and
then to the DataEntry form, which is based on that query. This allowed me to
add shoulder data to all existing records (over 4000 of them). In order to
get Access to accept shoulder data in a new record, I set the default for the
Shoulder ID text box to =EncounterID.
Click to expand...

Again - you're storing data in fieldnames and tablenames. Neither
fieldnames nor tablenames are appropriate places to store data. You
store data IN FIELDS.

Rather than a table of Shoulders, you should have a table of
Treatments - with a field for the body part being treated, and a field
for the type of treatment. Again - GROW DOWN, not across.

Just as a side note, it is *NOT* necessary - nor even appropriate - to
link tables by using the defaultvalue property. Instead, use a Form
with a Subform; if an Encounter involves multiple Treatments, you
would have the mainform based on the Encounters table, and the subform
based on the Treatments table, with EncounterID as the master/child
link field. This will automatically keep the tables in synch.
I was finally successful in getting a second data entry form, DataEntry2, to
work in sync with the Data Entry form, so that both forms displayed the same
record. The key here was to create a macro triggered by the On Current event
in the Data Entry form. This macro contains the following WHERE condition:
[EncounterID] = [Forms]![Data Entry]![EncounterID]
Click to expand...
I believe I can now add as many as 754 additional controls (minus the ones I
used to set up DataEntry2), and that the only limitation I face is a
reduction in speed as the number of controls increases.
Click to expand...

I'm sure you can. And I'm afraid that by doing so you'll be digging
yourself deeper and deeper into a flawed design, unnecessarily.


John W. Vinson[MVP]
 
Back
Top