473,714 Members | 2,365 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
4 2140
Terry:

Define a "Relationsh ip" 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*****@merseym ail.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
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.co m> wrote in message news:<tf******* *************** **********@4ax. com>...
Terry:

Define a "Relationsh ip" 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*****@merseym ail.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
On 14 Dec 2003 09:32:41 -0800, tc*****@merseym ail.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
Steve Jorgensen <no****@nospam. nospam> wrote in message news:<4v******* *************** **********@4ax. com>...
On 14 Dec 2003 09:32:41 -0800, tc*****@merseym ail.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
4294
by: Eric | last post by:
Hey Everyone.. I have a form that has approximately 7 text fields and 1 checkbox. Generally when this form is submitted(to itself BTW) it works fine, however, when the checkbox is only field that has been modified/clicked the form doesn't always submit. When it does work, a Stored procedure is passed form variables and updates to the db are made. When it doesn't, its as if the form wasn't submitted, it reloads and resets the page, but...
2
2625
by: ColinWard | last post by:
Hi. I have a form which has as its recordsource an SQL string. The SQL String is as follows: SELECT * from CONTACTS where false. this ensures that there is no data loaded in the form when the form is opened. After the user selects the contact from an unbound combobox I want the form to be rebound to its recordsource. I tried using form.recordsource = "Contacts" in the afterupdate event of
6
2481
by: skgolden | last post by:
My husband and I own a small temporary labor company and deal with 4 major clients (A,B,C & D), each of which has about 2 dozen units in our tristate area that we deal with (ie, Company A, units a,b,c,d,e etc). We send temps possessing various skills (cashier, cook, hostess, etc) to the individual units as needed. What I'm trying to do is create a schedule we can use each day, for example: Monday, June 1, 2004 Company A, unit e Alice...
11
1679
by: Johnny M | last post by:
I have several databases where I have one form to add a new record and one form to edit an existing record. I use unbound forms and class modules most of the time. What I would like to do is have one form that handles both adding a new record to the database and editing the same type of record. The form would be altered in the load event to change whatever needs to be changed to show the form in an "Add" mode vs. and "Edit" mode (I...
6
2142
by: allyn44 | last post by:
HI--what I am trying to do is 2 things: 1. Open a form in either data entry mode or edit mode depending on what task the user is performing 2. Cancel events tied to fields on the form if I am in edit mode. The reason I want to do this is becasue when entering a new record the form is entered in data entry mode and I have lots of stuff happening upon entering and leaving fields. In edit mode I do not want the events to fire.
13
6779
by: ricky.agrawal | last post by:
I'm really not sure how to go about this in Access. What I've created is a table for each location. Those tables are identical in format but different in information. The tables are named after key points such as the store number and the store ID. The fields of those tables are generic fields such as sales per day, bank deposit and what not. The first field for each store table is the date and I've set that as the primary key as one...
8
14785
by: fonzie | last post by:
Is it possible to have a data entry form where the information is stored in several different tables (5 or 6)? I have an inventory database where Table1 stores all of the data common to all entries, Table2 stores only the extra info relevant to items that are TYPEA, Table3 stores only the extra info relevant to items that are TYPEB, and so on. All relationships are setup (one-to-one) between the main table (Table1) and all the other...
22
18148
by: ddg_linux | last post by:
I have been reading about and doing a lot of php code examples from books but now I find myself wanting to do something practical with some of the skills that I have learned. I am a beginner php programmer and looking for a starting point in regards to practical projects to work on. What are some projects that beginner programmers usually start with? Please list a few that would be good for a beginner PHP programmer to
1
3600
by: troy_lee | last post by:
I have a table (Table A). It has one field, a PK. It is in a 1:M with another table (Table B). I am having trouble with a form/subform setup to view the 1:M records. On the parent form, there is a combo box with a Select query to select one of the records in Table A. (The parent form is bound to Table A.) I want to be able to first, view all of the corresponding records from Table B and then second, give an option for editing these...
0
8808
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8712
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9316
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9177
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7954
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6637
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4465
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3159
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2524
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.