enterabel form (data going into multiple tables)

[Graeme]

Hello
I am currently designing a database for my company, and I keep on running into problems when I want to create an enterable form using multiple tables. This means that when i create the form, I will be using fields from different tables, and when it is entered into the form is should go to the tables. However, when I create this, there seems to be problems, It works if I input information into every field. However, if this is not done than the record is not remembered. I would like to know how to fix this problem.

Regard
Graeme

 

[George Hepworth]

As a general rule, queries with joins to multiple tables are more likely NOT
to be updatable. Therefore, when you use such a query as the recordsource
for your forms, it is quite likely the form will not be updatable.

The solution is to go to a better design.

We can not see your tables (we don't even know what the subject of the
database might be), so this will have to be pretty generic.

Usually, you will have tables which are in a one-to-many
relationship--assuming you have a properly designed database. In those
cases, the common approach is to base data entry on a main form/subform
design, in which the main form is bound to a single table, that table being
the one on the "One" side of the one-to-many relationship. The subform,
which is inserted into a subform control on the main form, is bound to the
table on the "Many" side of the one-to-many relationship. You tell Access
about the relationship between the two forms by using the Master/Child
fields property. the Primary key for the table bound to the main form is the
"Master" linking field, and its corresponding Foreign key in the table bound
to the subform is the "Child" linking field.

With such a design, Access is able to manage the relationship between
records entered.

By limiting your bound forms to a single table (or a query which is based on
a single table) you are able to design updatable forms. It is possible to
create multi-table queries which are updatable, but that requires pretty
good understanding of normalization.

George

 

[John W. Vinson]

Hello
I am currently designing a database for my company, and I keep on running into problems when I want to create an enterable form using multiple tables. This means that when i create the form, I will be using fields from different tables, and when it is entered into the form is should go to the tables. However, when I create this, there seems to be problems, It works if I input information into every field. However, if this is not done than the record is not remembered. I would like to know how to fix this problem.

It sounds like you're starting your database design with the Form. That's not
going to work very well!

You first need to start with the tables - properly normalized and related
tables. THEN you design a form to fit the tables. Rather than building one
Great Master Query with all the tables, you would use the tools that Access
provides - Forms with Subforms, listboxes and combo boxes, etc; it's rather
rare that you'll need more than one table in a Form's Recordsource query.

What are your tables?
How are they related?
Are you in fact trying to throw all of the tables onto the form at once, or
are you using Subforms?

You might want to check out some of the resources and form examples below, or
of course you're welcome to post back with more details.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Graeme]

Thankyou bot
John W. Vinson [MVP] and Georg
I will try what was said and if I have anymore problems I will not hesistate to use this form

Regard
Graem

 

[Graeme]

Hello
To answer the above question my tables are Problem and Results. I would like to have one form, that would combine the fields from these tables. They are linked by an autonumber field called ID in the Problem table and a number field called ID in the Result field. What I would like to happen is every time autnoumber ID field increases the corresponding ID field for results also increases. I am infact trying to throw all the fields in one form. Could you tell me if this is a good Idea or if I should, hault what i am doing and find a different approach. I do not understand the purpose of subforms, however both fields have a primary Key field called ID (one to one relationship), so the one-to-many relationship you had discussed may not be valid.
Regards
Graeme

 

[George Hepworth]

There are two tables, not "multiple tables"? Then, the classic main form
subform design is very much appropriate for your situation.

However, let's just be absolutely clear. For each Problem, there is ONE and
ONLY ONE possible result? If so, then you do indeed have a one-to-one
relationship. Let's assume that's valid. However, one of your statements
raises another issue that we need to clarify first. "...however both fields
have a primary Key field called ID (one to one relationship)..." I assume
you meant to write that "..however both [tables] have a primary Key field
called ID (one to one relationship)..." and that you have defined your
relationship between Problems and Results on those fields. Is that correct?

George

 

[Graeme]

Hello Georg

You are correct, both tables have a one to one relationship, and that relationship is do to the primary key field ID in both tables
Regard
Graeme

 

[George Hepworth]

Well, that's not how I would do it, but you can make it work if you are
willing to tolerate the restrictions.

First, it is critical that the Primary Key field in the Results table is
defined as a Number of Long Integer datatype, not an Autonumber.
Second, the Primary Key field in the Problem table needs to be the
Autonumber.
Third, you'll need to add values for all of the fields in the Problem table
first.

Typically, you'd want to do this with a main form/sub form design so that
the record in the Problem table is entered and saved first.

George

PS: Just a comment on terminology and the apparent logic behind it.

"that relationship is do to the primary key field ID " is not factually
accurate.

Relationships between things (which we call entities in the world of
database design) exist with or without our assistance or even our knowledge
in many cases.

If you have a problem, you may or may not have a "result", but that
relationship between problem and result does not depend on whether you track
it in a database.

In fact, when you create Primary and Foreign Key fields in tables in a
relational database and define the relationship between them by dragging the
line in the relationship window, what you are doing is simply making that
relationship EXPLICIT to the database. That's all; you are, in a sense,
"telling" Access about that relationship.

So, the reality is that the relationship is NOT due to the Primary Key
fields. The Primary Key fields (and the relationship you have defined
between them) are due to the need for your database to track the existing
relationship.

 

[Graeme]

Hello Georg

You said above, "thats not how I would do it". So I guess the question is how would you do it. How can I make this database, stronger and less restrictive

Regard
Graeme

 

[George Hepworth]

I would use the main form/subform design.

 

[De Jager]

Hello
I am currently designing a database for my company, and I keep on running
into problems when I want to create an enterable form using multiple
tables. This means that when i create the form, I will be using fields
from different tables, and when it is entered into the form is should go
to the tables. However, when I create this, there seems to be problems, It
works if I input information into every field. However, if this is not
done than the record is not remembered. I would like to know how to fix
this problem.

Regards
Graeme

 

[joelgeraldine]

hgxn,nnnn

 

[Graeme]

Dear George and Jon W Vinso
I thankyou both for helping me solve the problem

Regard
Graeme

 

[George Hepworth]

We're always pleased to get confirmation that a solution was found. Best of
luck with the rest of your project.