Updates in Form Fields

[Novice2000]

Hi,

I have created a form that draws its data from a query to join three tables.
The fields do not allow me to update information. How can I change these to
accept changes? Thanks

 

[John W. Vinson]

Hi,

I have created a form that draws its data from a query to join three tables.
The fields do not allow me to update information. How can I change these to
accept changes? Thanks

Many multitable queries are not updateable, and it's rarely necessary to
create a Grand Master Query for updating table data. You may want to consider
using a Form based on the "one" side table of the relationship, and Subforms
for the "many" side tables. If your tables don't have relationships.... well,
your tables should have relationships and primary keys!

If you have a good reason to use a three-table query as the recordsource,
please post an explanation and the SQL of the query.

John W. Vinson [MVP]

 

[Novice2000]

Since I posted this something has changed. There was an expression in the
query, which when removed allows the updates to the tables.

The reason for the query was that there were just too many fields in the
combined tables to create a form. Every time I tried to create a form it was
rejected. The query seems to work OK. Is there a reason not to use it?

 

[John W. Vinson]

Since I posted this something has changed. There was an expression in the
query, which when removed allows the updates to the tables.

The reason for the query was that there were just too many fields in the
combined tables to create a form. Every time I tried to create a form it was
rejected. The query seems to work OK. Is there a reason not to use it?

If there are too many fields in your table to fit on a form... *your table
design is almost certainly WRONG*. Thirty fields is an enormously wide table.

Care to discuss your table design, before you get too far down a wrong route?

John W. Vinson [MVP]

 

[Novice2000]

Actually, we have discussed this before briefly.

There really isn't much anyone can do about it. Table One was 245 fields
when I came to work here and was the do-all end-all database, and yes I am
aware that that is not what these tables are for. They made all changes
directly in that table.

It took me a year to convince management to pare down this table. With the
new columns for 2008, it is still 195 columns. I sat them down and said which
fields do you no longer need in the table and this is the best we could do.

You see, they never used forms. They thought forms were for "forms". I have
created a form that brings all of this information forward, demographics on
top and tabs below with the various types of information and they are just
now starting to use it - are unsure of it. The old data in Table 2 also still
had to be made readily available, and that was why I did a query on both
tables to build the form upon. I dream of the day when I can divide this
garbage up into managable bites.

Since so much of the data is archived, I thought to start dividing it up
without telling them... and storing the new database on my Crive for now.
Is there any limit to the number of tables that can be queired upon?

 

[John W. Vinson]

Actually, we have discussed this before briefly.

There really isn't much anyone can do about it. Table One was 245 fields
when I came to work here and was the do-all end-all database, and yes I am
aware that that is not what these tables are for. They made all changes
directly in that table.
:-{(

It took me a year to convince management to pare down this table. With the
new columns for 2008, it is still 195 columns. I sat them down and said which
fields do you no longer need in the table and this is the best we could do.

Well... removing unneeded fields is a good thing, but it's NOT normalization
and it's not the solution to the problem.

You see, they never used forms. They thought forms were for "forms".

As in printed sheets of paper? One of the problems with the name, maybe. Some
other software refers to "screens" but that's ambiguous too!

I have
created a form that brings all of this information forward, demographics on
top and tabs below with the various types of information and they are just
now starting to use it - are unsure of it. The old data in Table 2 also still
had to be made readily available, and that was why I did a query on both
tables to build the form upon. I dream of the day when I can divide this
garbage up into managable bites.

Good luck.

Since so much of the data is archived, I thought to start dividing it up
without telling them... and storing the new database on my Crive for now.
Is there any limit to the number of tables that can be queired upon?

Well, you won't WANT to make One Single Giant Master Query. It certainly won't
be updateable and it's not the way to go anyway. The limit is that a compiled
Query cannot exceed 64KBytes; I'm not aware of any specific limit on the
number of tables in a query, but dozens is certainly possible.

John W. Vinson [MVP]

 

[Novice2000]

Thanks for the ideas.

Well... removing unneeded fields is a good thing, but it's NOT normalization
and it's not the solution to the problem.


As in printed sheets of paper? One of the problems with the name, maybe. Some
other software refers to "screens" but that's ambiguous too!


Good luck.


Well, you won't WANT to make One Single Giant Master Query. It certainly won't
be updateable and it's not the way to go anyway. The limit is that a compiled
Query cannot exceed 64KBytes; I'm not aware of any specific limit on the
number of tables in a query, but dozens is certainly possible.

John W. Vinson [MVP]

 

[missinglinq via AccessMonster.com]

What a mare's nest you've been thrust into, my friend!

"With the new columns for 2008, it is still 195 columns."

Most experienced developers will tell you that more than 25 or so fields is
almost always a sign of an ill-designed table. The fact you're actually
adding new, almost assuredly redundant fields, for the next year, supports
this supposition. It sounds suspiciously, to quote John Vinson himself, like
you're "committing spreadsheet upon a database!" Continuing to add new fields
for each ensuing year is sure to be a formula for disaster, probably sooner
rather than later!

"The reason for the query was that there were just too many fields in the
combined tables to create a form. Every time I tried to create a form it was
rejected."

Obviously you're using the Forms Wizard for form creation, and it is, indeed,
limited in the number of controls (fields) it can incorporate into a form. Be
aware that just because the wizard can't create a form with X number of
fields doesn't mean that the form can't be created; it just has to be done in
Design View.

John's idea about using a main form and subforms is right on the mark, but as
he's indicated, other work, some of it "heavy lifting," remains to be done,
normalization being at the top of the list! If it was me, I'd let them
continue to use their old app while working at warp speed to get a new db
designed properly, from scratch, then import the old data.

Good Luck!