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
4  2134 
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?
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?
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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...
|
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
|
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...
|
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...
|
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.
| | |
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...
|
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...
|
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
|
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...
|
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...
|
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,...
| | |
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...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
