By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,710 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,710 IT Pros & Developers. It's quick & easy.

Beginner: Can a form edit identical data from more than one table?

P: n/a
I need some help refining an MS 2000 relational databse.

I have created a simple relational database using two tables, 'Student
Details', 'Exam Details' and two forms, 'Input/Edit Exam Details',
'Input/Edit Student Details'.

'Student Details' has a field called 'Log Book No' (no duplicates
allowed) and this is the Primary Key. 'Exam Details' also has a field
called 'Log Book No' (duplicates allowed) and has no Primary Key, (as
each student can have many exam records in the 'Exam Details' table,
which all include the unique 'Log Book No').

The idea is that each student can take a number of exams and the form
displays minimal details (from 'Student Details'), at the top of the
form, and minimal details of all exams taken by the student,(from
'Exam Details'), at the bottom.

I have created a one to many relationship between the two tables and
created two forms:

(Input/Edit Exam Details). This form displays minimal data from both
tables, and is designed only to edit Exam Details, so the Student
Details fields are locked. This works fine.

(Input/Edit Student Details). This form displays all data from
'Student Details' and is designed to input new students details and
also ammend data entry errors. This works fine but........

I need the facillity to change data in the 'Log Book No' field (in
both 'Student Details' and 'Exam Details' tables, at the same time).
However, the problem is that the 'Log Book No' field in the
'Input/Edit Student Details' form only changes data in the 'Student
Details' table.

At the present time some students have taken three exams and hence the
'Exam Details' table has three records for each of these students,
each one with a 'Log Book No' field.

I have tried adding a Text Box to this form and made the Data Control
Source: [Exam Details]![Log Book No], but when I view it in form view
the box displays: #Name?

Is it possible to arrange it so that the Log Book No field can be
edited, in both tables, at the same time, from the Student Details
form?

Terry
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Terry:

Define a "Relationship" between the two tables on the Log Book No
field and set the "Cascade Updates" (or whatever) property of that
relationship to True. Then, when you edit the field in the Student
Details table, the Exam Details table will get updated automatically.

Unsolicted Comments:

1) You should have a PK on the Exam Details table.

2) What exactly is "Log Book No"? I would expect the PK in a Student
table to be something like "StudentID". (It also sounds a little odd
that you're having to edit it.)

3) Even though Access allows it, I think you'll find yourself better
off in the long run if you don't use spaces in field names (or any
other names).

4) Are you using a tutorial of any kind? It sounds like you're just
winging it. A good tutorial can save you a lot of hassle. Check the
Google Groups archives for some recommendations from people.

Hope some of this helps. :-)

-Matt

On 13 Dec 2003 08:24:51 -0800, tc*****@merseymail.com (Terry) wrote:
Is it possible to arrange it so that the Log Book No field can be
edited, in both tables, at the same time, from the Student Details
form?


Nov 12 '05 #2

P: n/a
Thanks Matthew

The "LogBookNo" is a unique number assigned to each student so I
thought it was a good idea to use this as the first field in each
table. It is now the PK in the "Student details" table and the PK in
the "Exam details" table is "StudentID".

I need to edit these forms to take into account Data Entry Errors e.g.
misspelled names, wrong dates of birth etc.

I've done as you suggested with the "Cascade Updates". The
relationship on the two tables is now defined on the "LogBookNo"
(field names changed as suggested)and it works fine. Up to a point!

One of the fields I renamed in the "Student details" table is causing
me a problem.

The field was originally called "Last Name" and I changed it to
"LastName". Now every time I open the "Input Exams and Results" Form I
get an "Enter Parameter Value" message box saying "Student
details.Last Name".

(Don't really know what this means but I guess it means that this
control is listed somewhere in the "Input Exams and Results" Form and
cannot find a field called "Last Name". Although maybe not because the
form still opens and the all the surnames are displayed correctly.)

All the other fields I renamed where OK once I sorted out the Data
Source but this one is stubborn and I can't find a refference to it
anywhere in it's control. There is a reference to "LastName" and I
have tried using this as a Data Source but the message still persists.

Any ideas?

Once I get this last problem sorted I intend to get on to a tutorial.
I've found one called www.profsr.com/access and it looks OK to me.
Have you heard of it?
Matthew Sullivan <Ma**@NoSpam.com> wrote in message news:<tf********************************@4ax.com>. ..
Terry:

Define a "Relationship" between the two tables on the Log Book No
field and set the "Cascade Updates" (or whatever) property of that
relationship to True. Then, when you edit the field in the Student
Details table, the Exam Details table will get updated automatically.
-Matt

On 13 Dec 2003 08:24:51 -0800, tc*****@merseymail.com (Terry) wrote:
Is it possible to arrange it so that the Log Book No field can be
edited, in both tables, at the same time, from the Student Details
form?

Nov 12 '05 #3

P: n/a
On 14 Dec 2003 09:32:41 -0800, tc*****@merseymail.com (Terry) wrote:
Thanks Matthew

The "LogBookNo" is a unique number assigned to each student so I
thought it was a good idea to use this as the first field in each
table. It is now the PK in the "Student details" table and the PK in
the "Exam details" table is "StudentID".

I need to edit these forms to take into account Data Entry Errors e.g.
misspelled names, wrong dates of birth etc.


It is never a good idea to use a field that might need to be changed as a
primary key, though it mak make sense to give it a unique index, so duplicates
are not allowed. You should, instead, create an auto-number field to use as
the primary key, and have other tables use that key as their foreign key link.
The LogBookNo field, then, is located inonly one table, and its value can be
found by joining to that table in a query.
Nov 12 '05 #4

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<4v********************************@4ax.com>. ..
On 14 Dec 2003 09:32:41 -0800, tc*****@merseymail.com (Terry) wrote:
Thanks Matthew and Steve
Your help has been invaluable and because of this all the issues I
raised have now been resolved. I even resolved the 'Enter Parameter
Value' one by:

Looking for filters that where still trying to use the old field names
(mentioned in the popup)and either deleting or ammending them.

Clicking My Right Hand Mouse on every part of the offending form (in
design view) and being surprised by the old name still being in thigs
like 'Child Form Link' or whatever.

I even found an old field name by selecting debug in a macro that
wouldn't work.

So after all this trial and error I have been convinced by Steve that
trying to 'wing it' is actually hard work and have found an excellent
website: www.profsr.com so I can now concentrate on getting ot right
first time.

Thanks for all your help :-)

Terry

It is never a good idea to use a field that might need to be changed as a
primary key, though it mak make sense to give it a unique index, so duplicates
are not allowed.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.