Avoiding duplicate data entry

Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

Hey Crystal, many thanks for all that. I'll get back here when I have tidied
the file up as per your suggestions and read through the link you have posted.
Great help, much appreciated.
Regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

you're welcome, Peter

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
Hey Crystal, many thanks for all that. I'll get back here when I have tidied
the file up as per your suggestions and read through the link you have posted.
Great help, much appreciated.
Regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
- StudentID, long, fk to Students

Something to do with Relationships? Link?

Great tutorial, now a permanent link in my favourites!
thanks, regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

Hi Peter

FK = Foreign Key
PK = Primary Key

"Great tutorial"

thank you, Peter, glad you are enjoying Access Basics

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
- StudentID, long, fk to Students

Something to do with Relationships? Link?

Great tutorial, now a permanent link in my favourites!
thanks, regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

yeah I managed to work out the FK bit just after I posted the question (now
how often does that happen!) Thanks anyway.
Right......... now I have 3 tables with these fields
T_Students - StudentID LastNameStudent FirstNameStudent
T_Subjects - SubjectID SubjectName (with the 4 subjects already entered in)
hmmm, do I need them entered here? Can I just enter the 4 subjects into a
combo box on the form and have them entered into the table that way?
T_StudentPrefs StudentPrefID StudentID SubjectID PrefNum
Now, I am starting to feel a little lost but have put this together with the
following relationships established,
T_StudentPrefs PK StudentID outerjoin to T_Students PK StudentID
T_StudentPrefs PK SubjectID outerjoin to T_Subjects PK SubjectID
Is this what you mean when talking about Foreign keys?
The teacher wants the students to enter their names and preferences into a
form
So, I imagined a form with blank fields for name, the list of 4 subjects,
each subject with a combo box off to the right to select their preference by
selecting 1 for Geography etc.
At the end of the day, the teacher wants a list with the number of students
for each subject and preferences., 1st, 2nd, 3rd, 4th.
Now this is very easy to do, and I had a trial database worked out to her
satisfaction, but the more I thought about it the more ideas I had about how
to go about it., There is still plenty of time before she needs this, so I
thought I would use it as an exercise for my own training and "do it properly"
Regards Peter.
"strive4peace" wrote:

Hi Peter

FK = Foreign Key
PK = Primary Key

"Great tutorial"

thank you, Peter, glad you are enjoying Access Basics

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
- StudentID, long, fk to Students

Something to do with Relationships? Link?

Great tutorial, now a permanent link in my favourites!
thanks, regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

Hi Peter,

by foreign key, I mean this:

studentID is the primary key in the Students table
studentID is a foreign key in the StudentPrefs table because it links a
related student preference record to a student record

read the section on mainform/subform in Access Basics

make a main form based on Students -- this will be your main form

make sure that StudentID is on this form

make another form based on StudentPrefs -- this will be used as a
subform. Meke the default view -- continuous form

on this form:

textbox control for PrefNum allowing 1-4 to be entered

combobox control for SubjectID
RowSource --
SELECT SubjectID, SubjectName
FROM Subjects
ORDER BY SubjectName

Columncount -- 2
ColumnWidths -- 0;2
ListWidth -- 2.2

textbox control for StudentID
Visible -- No

textbox control for StudentPrefID
TabStop -- No

on the BeforeUpdate event of PrefNum

'~~~~~~~~~~~~~~~~~~
if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Prefereance Number"
Cancel = true
exit sub
end if

if me.prefnum 1 or me.PrefNum 4 then
msgbox "You must enter 1-4 for the preference" _
,,"Data not valid"
Cancel = true
exit sub
end if

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND Prefnum = " & me.prefnum _
& " AND StudentPrefID " & me.StudentPrefID _
),0) 0 then
msgbox "You must enter 1-4 for the preference" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the BeforeUpdate event of SubjectID

'~~~~~~~~~~~~~~~~~~

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND SubjectID= " & me.SubjectID _
& " AND StudentPrefID " & me.StudentPrefID _
),0) 0 then
msgbox "You may not use the same subject twice" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the form BeforeUpdate event, make sure prefnum and subjectID are
filled out


'~~~~~~~~~~~~~~~~~~

if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Preferance Number"
Cancel = true
exit sub
end if

if isnull(me.subjectID) then
msgbox "You must specify a Subject" _
"Missing subject"
Cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~

now, add the subform to the mainform
use a subform control and set these properties:

SourceObject -- the name of your subform

LinkMasterFields -- StudentID
LinkChildFields -- StudentID

there are still things you will need to do ... but this is a start smile


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
yeah I managed to work out the FK bit just after I posted the question (now
how often does that happen!) Thanks anyway.
Right......... now I have 3 tables with these fields
T_Students - StudentID LastNameStudent FirstNameStudent
T_Subjects - SubjectID SubjectName (with the 4 subjects already entered in)
hmmm, do I need them entered here? Can I just enter the 4 subjects into a
combo box on the form and have them entered into the table that way?
T_StudentPrefs StudentPrefID StudentID SubjectID PrefNum
Now, I am starting to feel a little lost but have put this together with the
following relationships established,
T_StudentPrefs PK StudentID outerjoin to T_Students PK StudentID
T_StudentPrefs PK SubjectID outerjoin to T_Subjects PK SubjectID
Is this what you mean when talking about Foreign keys?
The teacher wants the students to enter their names and preferences into a
form
So, I imagined a form with blank fields for name, the list of 4 subjects,
each subject with a combo box off to the right to select their preference by
selecting 1 for Geography etc.
At the end of the day, the teacher wants a list with the number of students
for each subject and preferences., 1st, 2nd, 3rd, 4th.
Now this is very easy to do, and I had a trial database worked out to her
satisfaction, but the more I thought about it the more ideas I had about how
to go about it., There is still plenty of time before she needs this, so I
thought I would use it as an exercise for my own training and "do it properly"
Regards Peter.
"strive4peace" wrote:

Hi Peter

FK = Foreign Key
PK = Primary Key

"Great tutorial"

thank you, Peter, glad you are enjoying Access Basics

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
- StudentID, long, fk to Students

Something to do with Relationships? Link?

Great tutorial, now a permanent link in my favourites!
thanks, regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

Hmm.... there are still things you will need to do ... but this is a start
smile

Indeedsmile something to keep the brain ticking over for a while. I will
let you know when I have found all the syntax "errors" etc and it is all
working as it should.
You have been a great help, I enjoy this style of help, it makes you
actually think and learn.
regards Peter.

"strive4peace" wrote:

Hi Peter,

by foreign key, I mean this:

studentID is the primary key in the Students table
studentID is a foreign key in the StudentPrefs table because it links a
related student preference record to a student record

read the section on mainform/subform in Access Basics

make a main form based on Students -- this will be your main form

make sure that StudentID is on this form

make another form based on StudentPrefs -- this will be used as a
subform. Meke the default view -- continuous form

on this form:

textbox control for PrefNum allowing 1-4 to be entered

combobox control for SubjectID
RowSource --
SELECT SubjectID, SubjectName
FROM Subjects
ORDER BY SubjectName

Columncount -- 2
ColumnWidths -- 0;2
ListWidth -- 2.2

textbox control for StudentID
Visible -- No

textbox control for StudentPrefID
TabStop -- No

on the BeforeUpdate event of PrefNum

'~~~~~~~~~~~~~~~~~~
if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Prefereance Number"
Cancel = true
exit sub
end if

if me.prefnum 1 or me.PrefNum 4 then
msgbox "You must enter 1-4 for the preference" _
,,"Data not valid"
Cancel = true
exit sub
end if

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND Prefnum = " & me.prefnum _
& " AND StudentPrefID " & me.StudentPrefID _
),0) 0 then
msgbox "You must enter 1-4 for the preference" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the BeforeUpdate event of SubjectID

'~~~~~~~~~~~~~~~~~~

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND SubjectID= " & me.SubjectID _
& " AND StudentPrefID " & me.StudentPrefID _
),0) 0 then
msgbox "You may not use the same subject twice" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the form BeforeUpdate event, make sure prefnum and subjectID are
filled out


'~~~~~~~~~~~~~~~~~~

if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Preferance Number"
Cancel = true
exit sub
end if

if isnull(me.subjectID) then
msgbox "You must specify a Subject" _
"Missing subject"
Cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~

now, add the subform to the mainform
use a subform control and set these properties:

SourceObject -- the name of your subform

LinkMasterFields -- StudentID
LinkChildFields -- StudentID

there are still things you will need to do ... but this is a start smile


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
yeah I managed to work out the FK bit just after I posted the question (now
how often does that happen!) Thanks anyway.
Right......... now I have 3 tables with these fields
T_Students - StudentID LastNameStudent FirstNameStudent
T_Subjects - SubjectID SubjectName (with the 4 subjects already entered in)
hmmm, do I need them entered here? Can I just enter the 4 subjects into a
combo box on the form and have them entered into the table that way?
T_StudentPrefs StudentPrefID StudentID SubjectID PrefNum
Now, I am starting to feel a little lost but have put this together with the
following relationships established,
T_StudentPrefs PK StudentID outerjoin to T_Students PK StudentID
T_StudentPrefs PK SubjectID outerjoin to T_Subjects PK SubjectID
Is this what you mean when talking about Foreign keys?
The teacher wants the students to enter their names and preferences into a
form
So, I imagined a form with blank fields for name, the list of 4 subjects,
each subject with a combo box off to the right to select their preference by
selecting 1 for Geography etc.
At the end of the day, the teacher wants a list with the number of students
for each subject and preferences., 1st, 2nd, 3rd, 4th.
Now this is very easy to do, and I had a trial database worked out to her
satisfaction, but the more I thought about it the more ideas I had about how
to go about it., There is still plenty of time before she needs this, so I
thought I would use it as an exercise for my own training and "do it properly"
Regards Peter.
"strive4peace" wrote:

Hi Peter

FK = Foreign Key
PK = Primary Key

"Great tutorial"

thank you, Peter, glad you are enjoying Access Basics

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
- StudentID, long, fk to Students

Something to do with Relationships? Link?

Great tutorial, now a permanent link in my favourites!
thanks, regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.

you're welcome, Peter

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
Hmm.... there are still things you will need to do ... but this is a start
smile

Indeedsmile something to keep the brain ticking over for a while. I will
let you know when I have found all the syntax "errors" etc and it is all
working as it should.
You have been a great help, I enjoy this style of help, it makes you
actually think and learn.
regards Peter.

"strive4peace" wrote:

Hi Peter,

by foreign key, I mean this:

studentID is the primary key in the Students table
studentID is a foreign key in the StudentPrefs table because it links a
related student preference record to a student record

read the section on mainform/subform in Access Basics

make a main form based on Students -- this will be your main form

make sure that StudentID is on this form

make another form based on StudentPrefs -- this will be used as a
subform. Meke the default view -- continuous form

on this form:

textbox control for PrefNum allowing 1-4 to be entered

combobox control for SubjectID
RowSource --
SELECT SubjectID, SubjectName
FROM Subjects
ORDER BY SubjectName

Columncount -- 2
ColumnWidths -- 0;2
ListWidth -- 2.2

textbox control for StudentID
Visible -- No

textbox control for StudentPrefID
TabStop -- No

on the BeforeUpdate event of PrefNum

'~~~~~~~~~~~~~~~~~~
if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Prefereance Number"
Cancel = true
exit sub
end if

if me.prefnum 1 or me.PrefNum 4 then
msgbox "You must enter 1-4 for the preference" _
,,"Data not valid"
Cancel = true
exit sub
end if

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND Prefnum = " & me.prefnum _
& " AND StudentPrefID " & me.StudentPrefID _
),0) 0 then
msgbox "You must enter 1-4 for the preference" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the BeforeUpdate event of SubjectID

'~~~~~~~~~~~~~~~~~~

'check to make sure prefnum has not been used
'in another record for this student
if nz(dLookup("StudentID" _
,"StudentPrefs" _
,"StudentID=" & me.studentid _
& " AND SubjectID= " & me.SubjectID _
& " AND StudentPrefID " & me.StudentPrefID _
),0) 0 then
msgbox "You may not use the same subject twice" _
"Data not valid"
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~

on the form BeforeUpdate event, make sure prefnum and subjectID are
filled out


'~~~~~~~~~~~~~~~~~~

if isnull(me.prefnum) then
msgbox "You must specify a preferance number" _
"Missing Preferance Number"
Cancel = true
exit sub
end if

if isnull(me.subjectID) then
msgbox "You must specify a Subject" _
"Missing subject"
Cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~

now, add the subform to the mainform
use a subform control and set these properties:

SourceObject -- the name of your subform

LinkMasterFields -- StudentID
LinkChildFields -- StudentID

there are still things you will need to do ... but this is a start smile


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
yeah I managed to work out the FK bit just after I posted the question (now
how often does that happen!) Thanks anyway.
Right......... now I have 3 tables with these fields
T_Students - StudentID LastNameStudent FirstNameStudent
T_Subjects - SubjectID SubjectName (with the 4 subjects already entered in)
hmmm, do I need them entered here? Can I just enter the 4 subjects into a
combo box on the form and have them entered into the table that way?
T_StudentPrefs StudentPrefID StudentID SubjectID PrefNum
Now, I am starting to feel a little lost but have put this together with the
following relationships established,
T_StudentPrefs PK StudentID outerjoin to T_Students PK StudentID
T_StudentPrefs PK SubjectID outerjoin to T_Subjects PK SubjectID
Is this what you mean when talking about Foreign keys?
The teacher wants the students to enter their names and preferences into a
form
So, I imagined a form with blank fields for name, the list of 4 subjects,
each subject with a combo box off to the right to select their preference by
selecting 1 for Geography etc.
At the end of the day, the teacher wants a list with the number of students
for each subject and preferences., 1st, 2nd, 3rd, 4th.
Now this is very easy to do, and I had a trial database worked out to her
satisfaction, but the more I thought about it the more ideas I had about how
to go about it., There is still plenty of time before she needs this, so I
thought I would use it as an exercise for my own training and "do it properly"
Regards Peter.
"strive4peace" wrote:

Hi Peter

FK = Foreign Key
PK = Primary Key

"Great tutorial"

thank you, Peter, glad you are enjoying Access Basics

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



pmccrackan wrote:
Hi again, I'm reading and redisgning. One thing is puzzling me, whats fk?
- StudentID, long, fk to Students

Something to do with Relationships? Link?

Great tutorial, now a permanent link in my favourites!
thanks, regards Peter.

"strive4peace" wrote:

Hi Peter,

firstly, instead of 4 fields for the subject preference, use a related
table.

Instead of T_Data, name your table more specifically

T_Students
- StudentID, atuonumber
- LastnameStud, text
- FirstnameStud, text
- etc

StudPerferences
- StudPrefID, autonumber
- StudentID, long, fk to Students
- PrefNum, integer -- fill with 1,2,3, 4 ... using code behind the form
- SubjectID, long, fk to Subjects

then, it is MUCH easier to eliminate duplicates -- and is a better way
to structure the data. If you want to limit the preferance records to
4, you can use a form BeforeInsert event to check
me.recordset.recordcount and, if = 4, Cancel the Insert

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

and, once you have read this and have your data restructured, post back
and we will help you with code -- would be no use to give code to you
now, before you give yourself some foundation to understand it.

Warm Regards,
Crystal



*
(: have an awesome day
*



pmccrackan wrote:
Hi, I have a very simple DataBase I am putting together that will be used for
students to nominate their choice in order of preference of subjects for
studying later on this year.
I have a subjects table (T_Subjects) with 2 fields, Subject_ID and
Subject_Name
I have put 4 subjects into the table.
I also have a data table (T_Data) this has the Record_ID field, a field for
the Student name and then 4 fields for the subjects named 1st Preference, 2nd
Preference, 3rd Preference and finally 4th Preference. Each preference has a
look up linked to the T_Subject table to selct the subject from a drop down
list.
I want to avoid the students entering the same subject into different
preference fileds.
Presumably I would do this by adding some code into the before update
section in the form design for each field where the code would check which
Subject_ID had already been selected on that form. But not being a programmer
I am stuck.
I wonder if someone could be of assistance for me here. Or perhaps there is
a much simpler way to do this?
Be glad of any assistance, so thanks in advance!
Regards Peter.