How to show the contents of a field (50 items) in a form not in vertical way but 5 rows 10 columns?

[Paul T. Rong]

Hi everybody,

This time a very very difficult case:

I have a table "tblStudent", there are 50 students, I also made a form
"frmStudent" based on "tblStudent", now if I don't want to show all the
students in a classical (vertically, from top to bottom) way, but make them
"sitting" in 50 text boxes, 5 rows 10 columns, how to do it?

Thanks in advance.

I myself tried many times, didn't find a solution, thinking maybe it is just
not realistic under Access.

Paul

[Jeff Smith]

What do you mean about a classical way? Viewing them vertically means that
you've designed a continuous form where each record is underneath each
other.

There are 3 types of forms: Single form, Continuous and Datasheet. Single
forms display only one record at a time and are good for showing lots of
fields at a time. Datasheet forms look like tables and are mainly used for
sub-forms but can be used as a normal form for data entry. Continuous forms
behave the same way as datasheet forms and display many records per page but
you have the advantage of moving the fields about into multiple rows per
record e.g. a row with 16 fields may not fit across the screen so you move
the last 8 fields under the first, close the height of the detail section
and in view mode the continuous form will have 2 rows per record.

What I think you mean about displaying all the students in 50 text boxes is
the name of student 1 in the first box, the name of student 2 in the second
on so on. This could be done with code but what if you get 65 students,
you'll have to add more text boxes, What if you get 120 students, the screen
will get a bit full. Doing it this way will mean that you will not be able
to edit any names.

Please explain in a bit more detail about what you're trying to do and maybe
also why you want to do it that way. Someone may come up with a better
solution.

Jeff
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:S6********************@news.chello.at...

Hi everybody,

This time a very very difficult case:

I have a table "tblStudent", there are 50 students, I also made a form
"frmStudent" based on "tblStudent", now if I don't want to show all the
students in a classical (vertically, from top to bottom) way, but make them "sitting" in 50 text boxes, 5 rows 10 columns, how to do it?

Thanks in advance.

I myself tried many times, didn't find a solution, thinking maybe it is just not realistic under Access.

Paul

[Eric Schittlipz]

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:S6********************@news.chello.at...

Hi everybody,

This time a very very difficult case:

I have a table "tblStudent", there are 50 students, I also made a form
"frmStudent" based on "tblStudent", now if I don't want to show all the
students in a classical (vertically, from top to bottom) way, but make them "sitting" in 50 text boxes, 5 rows 10 columns, how to do it?

Thanks in advance.

I myself tried many times, didn't find a solution, thinking maybe it is just not realistic under Access.

Paul

It's not that difficult - providing you are happy to use an unbound form.
This is quite an unusual requirement for a form although it wouldn't be so
weird with a report designed for display only. What purpose should this
form have? Read-only, edits, additions, deletions?

[David Schofield]

On Thu, 07 Oct 2004 10:45:06 GMT, "Paul T. Rong" <et***@hotmail.com>
wrote:

Hi everybody,

This time a very very difficult case:

I have a table "tblStudent", there are 50 students, I also made a form
"frmStudent" based on "tblStudent", now if I don't want to show all the
students in a classical (vertically, from top to bottom) way, but make them
"sitting" in 50 text boxes, 5 rows 10 columns, how to do it?

Thanks in advance.

I myself tried many times, didn't find a solution, thinking maybe it is just
not realistic under Access.

Paul

Hi
You can do this with five subforms and queries to show the right items
in each, requerying when items are added and deleted. Scrolling needs
careful thought if there are more records than show.
Or you could write everthing into a temporary table with the right
shape and write things back afterwards.This is easier if you want the
records in order
1 2 3 4 5
6 7 8 9 10
etc
Or you use Excel automation, but I wouldn't!

In a report you can use columns of course.
David

[Paul T. Rong]

For example, there are 15 students in a certain class (there are many
classes in a school but max. there are 50 students in a class).

The tblStudent may look like this:

StudentID StudentName Row Column
1 Eva 1 1
2 Tomas 1 2
3 Barbara 1 3
....
8 Johana 2 3
.....
15 Ludo 3 5
I would like the "frmStudent" look like what the students sit (their
position) in the classroom, that is

Eva Tomas Barbara Ivan Marek
Veronika Jan Johana Elena Pavel
Alex Julia Natalia Theresa Ludo

-----------------------------------------------------------

I would like to use text box or other controls to represent every student so
that when I click on each of their names and a form can be opened with the
clicked student name on it.

This form is called "frmExam", is based on a table "tblExam".

tblExam includes following fields:

ExamID (autonumber) PK
StudentID FK
ExamDate

"frmExam" has a subform "sbfrmExam" which is based on a table
"tblExam_detail".

tblExam_detail includes follwoing fields:

ExamID PK
subjectID PK
marks

tblExam_detail is connected with tblSubject which has two field: "SubjectID"
and "Subject"

I would like to see the average mark from all subjects can be shown later
under each of the students names.

above is just my plan, I was stopped at the first step at the "frmStudent".

I am not sure if I should choose a unbound form, and use code (I prefer not
with lots of codes).

I try to figure out David's idea with "subforms and queries".

Thank you for your patience, I hope I made myself understood.

Paul

from
Bratislava
Slovakia


"Paul T. Rong" <et***@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:S6********************@news.chello.at...

Hi everybody,

This time a very very difficult case:

I have a table "tblStudent", there are 50 students, I also made a form
"frmStudent" based on "tblStudent", now if I don't want to show all the
students in a classical (vertically, from top to bottom) way, but make them "sitting" in 50 text boxes, 5 rows 10 columns, how to do it?

Thanks in advance.

I myself tried many times, didn't find a solution, thinking maybe it is just not realistic under Access.

Paul

[Pieter Linden]

Create a two-dimensional array from your data and pass it to Excel and
be done with it. Then you can just do something like

Cell(iRow+iRowOffset,iColumn+iColumnOffset)=Array( iRow,iColumn)

[Pieter Linden]

Doing this in VB is one thing, doing it in Access VBA is quite
another. In VB, you can have control arrays and then reference the
subscripts, so you could have a two-dimensional array of textboxes,
something like

Dim Student(4,4) as String
which could hold 25 values. then you could loop through the array...

Dim intRow as integer
Dim intColumn as Integer

for intRow=0 to 4
for intColumn=0 to 4
me.lblSeat(intRow,intColumn).Caption = rs!StudentName
rs.MoveNext
next intColumn
Next intRow

....

But there are NO control arrays in Access VBA. Key difference.

[Jeff Smith]

First create a query to order the students by rows then columns. Name it
qryStudentsSorted
**qryStudentsSorted**
SELECT StudentID, StudentName, YourRow, YourColumn
FROM tblStudents
ORDER BY YourRow, YourColumn;

With your form add your 50 text boxes with names of txt1, txt2, txt3 etc
with text boxes txt1 to txt5 on the top and the next row txt6 to txt10 and
so on.

Paste the following code behind the form:
' ***Code Start***
Option Compare Database
Option Explicit

' Constant for the maximum number of
' text boxes on the form.
Const txtMax As Byte = 42

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intI As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qryStudentsSorted")
' Call the routine to clear all fields
' before loading the data.
ClearFields
' Add the StudentName to each text box.
With rst
.MoveFirst
Do Until .EOF Or intI = txtMax
intI = intI + 1
Me("txt" & intI) = !StudentName
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Private Sub ClearFields()
Dim intI As Integer
For intI = 1 To txtMax
Me("txt" & intI) = Null
Next
End Sub
' *** Code End***

This will display all your students into every text box on your form.

To open up another form to by double clicking the student's name you'll have
to add 50 more text boxes, set their visible properties to No, name them the
same way as above but use something different than "txt" (in the code below
I used "i") and place them on top of the corresponding text box name. e.g.
txt1 and i1, txt2 and i2. Note: Make the size of these text boxes smaller
and have a different back colour so it makes it easier when in design view.

In the above code add the following line of code above the .MoveNext line:
Me("i" & intI) = !StudentID

Paste the following code below into your form:
' ***Code Start***
Private Sub OpenExam(strFieldName As String)
Dim intI As Integer
Dim intLen As Integer
Dim strTemp As String
' Remove the "txt" and prefix the remainder
' with an "i".
intLen = Len(strFieldName)
intI = CInt(Mid(strFieldName, 4, intLen - 3))
strTemp = "i" & intI
' Open the form.
DoCmd.OpenForm "frmExam", , , "[StudentID]=" & Me(strTemp) & " And
[ExamID]=" & ExamID
End Sub
' ***Code End***

In the Double Click event of every text box call the above sub routine using
the text box control's name e.g. using txt42
Private Sub txt42_DblClick(Cancel As Integer)
' Call the OpenExam sub
OpenExam Me!txt42.Name
End Sub

WARNING: No error trapping here. That's up to you.
Questions: How do you know which exam you want to open when you click on a
student? What if that student sits more than one exam? Do you want to open
up a different form which displays the student with a sub-form that show all
the exams that that student has sat?

With your table structure I'll go this way:
tbl_Exams
ExamID PK (Autonumber)
SubjectID FK
ExamDate

tbl_ExamStudents
ExamID PK (Long)
StudentID PK (Long)
Marks

This way you create a date for the exam and set the exam's subject. The
tbl_ExamStudents then stores all the students that sat (or would sit) that
exam. This would be a sub-form on the exam form.

Hope all this helps
Jeff

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:jZ*********************@news.chello.at...

For example, there are 15 students in a certain class (there are many
classes in a school but max. there are 50 students in a class).

The tblStudent may look like this:

StudentID StudentName Row Column
1 Eva 1 1
2 Tomas 1 2
3 Barbara 1 3
...
8 Johana 2 3
....
15 Ludo 3 5
I would like the "frmStudent" look like what the students sit (their
position) in the classroom, that is

Eva Tomas Barbara Ivan Marek
Veronika Jan Johana Elena Pavel
Alex Julia Natalia Theresa Ludo

-----------------------------------------------------------

I would like to use text box or other controls to represent every student so that when I click on each of their names and a form can be opened with the
clicked student name on it.

This form is called "frmExam", is based on a table "tblExam".

tblExam includes following fields:

ExamID (autonumber) PK
StudentID FK
ExamDate

"frmExam" has a subform "sbfrmExam" which is based on a table
"tblExam_detail".

tblExam_detail includes follwoing fields:

ExamID PK
subjectID PK
marks

tblExam_detail is connected with tblSubject which has two field: "SubjectID" and "Subject"

I would like to see the average mark from all subjects can be shown later
under each of the students names.

above is just my plan, I was stopped at the first step at the "frmStudent".
I am not sure if I should choose a unbound form, and use code (I prefer not with lots of codes).

I try to figure out David's idea with "subforms and queries".

Thank you for your patience, I hope I made myself understood.

Paul

from
Bratislava
Slovakia

[Paul T. Rong]

Hi Jeff,

I never expected such a great reply to my post. That is a big help. I was
touched. Thank you very much.

Because my knowledge on vba is very poor, it will take some time till I
fully understand the meaning of the codes. That will be a very good lesson
for me and also for other beginners on this newsgroup to get close to vba.

Questions: How do you know which exam you want to open when you click on a
student? What if that student sits more than one exam?
Sorry, I don't know.
Do you want to open
up a different form which displays the student with a sub-form that show all the exams that that student has sat?
Yes, that is what I want. But in this way, I have to repair my tables

tblExamInfo (better than tblExam)

ExamInfoID (autonumber) PK
StudentID FK

tblExam_detail

ExamInfoID PK
subjectID PK
marks
ExamDate
greetings from Bratislava

Paul
"Jeff Smith" <No***@Not.This.Address> дÈëÏûÏ¢ÐÂÎÅ
:ck**********@lust.ihug.co.nz... First create a query to order the students by rows then columns. Name it
qryStudentsSorted
**qryStudentsSorted**
SELECT StudentID, StudentName, YourRow, YourColumn
FROM tblStudents
ORDER BY YourRow, YourColumn;

With your form add your 50 text boxes with names of txt1, txt2, txt3 etc
with text boxes txt1 to txt5 on the top and the next row txt6 to txt10 and
so on.

Paste the following code behind the form:
' ***Code Start***
Option Compare Database
Option Explicit

' Constant for the maximum number of
' text boxes on the form.
Const txtMax As Byte = 42

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intI As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qryStudentsSorted")
' Call the routine to clear all fields
' before loading the data.
ClearFields
' Add the StudentName to each text box.
With rst
.MoveFirst
Do Until .EOF Or intI = txtMax
intI = intI + 1
Me("txt" & intI) = !StudentName
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Private Sub ClearFields()
Dim intI As Integer
For intI = 1 To txtMax
Me("txt" & intI) = Null
Next
End Sub
' *** Code End***

This will display all your students into every text box on your form.

To open up another form to by double clicking the student's name you'll have to add 50 more text boxes, set their visible properties to No, name them the same way as above but use something different than "txt" (in the code below I used "i") and place them on top of the corresponding text box name. e.g.
txt1 and i1, txt2 and i2. Note: Make the size of these text boxes smaller
and have a different back colour so it makes it easier when in design view.
In the above code add the following line of code above the .MoveNext line:
Me("i" & intI) = !StudentID

Paste the following code below into your form:
' ***Code Start***
Private Sub OpenExam(strFieldName As String)
Dim intI As Integer
Dim intLen As Integer
Dim strTemp As String
' Remove the "txt" and prefix the remainder
' with an "i".
intLen = Len(strFieldName)
intI = CInt(Mid(strFieldName, 4, intLen - 3))
strTemp = "i" & intI
' Open the form.
DoCmd.OpenForm "frmExam", , , "[StudentID]=" & Me(strTemp) & " And
[ExamID]=" & ExamID
End Sub
' ***Code End***

In the Double Click event of every text box call the above sub routine using the text box control's name e.g. using txt42
Private Sub txt42_DblClick(Cancel As Integer)
' Call the OpenExam sub
OpenExam Me!txt42.Name
End Sub

WARNING: No error trapping here. That's up to you.
Questions: How do you know which exam you want to open when you click on a
student? What if that student sits more than one exam? Do you want to open
up a different form which displays the student with a sub-form that show all the exams that that student has sat?

With your table structure I'll go this way:
tbl_Exams
ExamID PK (Autonumber)
SubjectID FK
ExamDate

tbl_ExamStudents
ExamID PK (Long)
StudentID PK (Long)
Marks

This way you create a date for the exam and set the exam's subject. The
tbl_ExamStudents then stores all the students that sat (or would sit) that
exam. This would be a sub-form on the exam form.

Hope all this helps
Jeff

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:jZ*********************@news.chello.at...

For example, there are 15 students in a certain class (there are many
classes in a school but max. there are 50 students in a class).

The tblStudent may look like this:

StudentID StudentName Row Column
1 Eva 1 1
2 Tomas 1 2
3 Barbara 1 3
...
8 Johana 2 3
....
15 Ludo 3 5
I would like the "frmStudent" look like what the students sit (their
position) in the classroom, that is

Eva Tomas Barbara Ivan Marek
Veronika Jan Johana Elena Pavel
Alex Julia Natalia Theresa Ludo

-----------------------------------------------------------

I would like to use text box or other controls to represent every student

so

that when I click on each of their names and a form can be opened with

the clicked student name on it.

This form is called "frmExam", is based on a table "tblExam".

tblExam includes following fields:

ExamID (autonumber) PK
StudentID FK
ExamDate

"frmExam" has a subform "sbfrmExam" which is based on a table
"tblExam_detail".

tblExam_detail includes follwoing fields:

ExamID PK
subjectID PK
marks

tblExam_detail is connected with tblSubject which has two field:

"SubjectID"

and "Subject"

I would like to see the average mark from all subjects can be shown later under each of the students names.

above is just my plan, I was stopped at the first step at the

"frmStudent".

I am not sure if I should choose a unbound form, and use code (I prefer

not

with lots of codes).

I try to figure out David's idea with "subforms and queries".

Thank you for your patience, I hope I made myself understood.

Paul

from
Bratislava
Slovakia

[Pieter Linden]

> > Questions: How do you know which exam you want to open when you click on a

student? What if that student sits more than one exam?

Sorry, I don't know.

Do you want to open
up a different form which displays the student with a sub-form that show

all

the exams that that student has sat?

Yes, that is what I want. But in this way, I have to repair my tables

You'd be better off figuring out EXACTLY what this database has to do
BEFORE you build, or you're going to be repairing more than just a few
tables. But don't take my word for it - have a read through the
introduction to the
Access Developer Handbook. Otherwise, heaven help to poor person that
inherits your stuff. Normalize ON PAPER and then when you have an
acceptable normalized design, start building your database. Don't
know about you, but I would no sooner build a critical database
without doing all the initial "spadework" than I would build a house
without having a foundation poured.

[Paul T. Rong]

You are right. Thanks.

Paul
"Pieter Linden" <pi********@hotmail.com>
??????:bf**************************@posting.google .com...

Questions: How do you know which exam you want to open when you click on a student? What if that student sits more than one exam?

Sorry, I don't know.

Do you want to open
up a different form which displays the student with a sub-form that

show all

the exams that that student has sat?

Yes, that is what I want. But in this way, I have to repair my tables

You'd be better off figuring out EXACTLY what this database has to do
BEFORE you build, or you're going to be repairing more than just a few
tables. But don't take my word for it - have a read through the
introduction to the
Access Developer Handbook. Otherwise, heaven help to poor person that
inherits your stuff. Normalize ON PAPER and then when you have an
acceptable normalized design, start building your database. Don't
know about you, but I would no sooner build a critical database
without doing all the initial "spadework" than I would build a house
without having a foundation poured.

[Jeff Smith]

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

Questions: How do you know which exam you want to open when you click on a student? What if that student sits more than one exam?

Sorry, I don't know.

Do you want to open
up a different form which displays the student with a sub-form that

show all

the exams that that student has sat?

Yes, that is what I want. But in this way, I have to repair my tables

You'd be better off figuring out EXACTLY what this database has to do
BEFORE you build, or you're going to be repairing more than just a few
tables. But don't take my word for it - have a read through the
introduction to the
Access Developer Handbook. Otherwise, heaven help to poor person that
inherits your stuff. Normalize ON PAPER and then when you have an
acceptable normalized design, start building your database. Don't
know about you, but I would no sooner build a critical database
without doing all the initial "spadework" than I would build a house
without having a foundation poured.

I agree with Pieter here, you have to know exactly what the database has to
do before you even start. The code I posted the other day shows you what can
be done with VBA. The questions I asked were there to make you think about
the database's design and also the same with the table structure I gave you.

My table structure was:
***tbl_Exams***
ExamID PK (Autonumber)
SubjectID FK
ExamDate

***tbl_ExamStudents***
ExamID PK (Long)
StudentID PK (Long)
Marks

With this structure, many students can sit the one exam.
The tbl_Exams is the exam paper itself with its unique ID, the subject
(topic) of the exam and its date. e.g. ID:3, Subject:World War 2 History,
ExamDate:14 Nov 2003
The tbl_ExamStudents links all the students that sit that exam and also the
marks they get.
When also linked to the tbl_Students table you will get a many to many
relationship; each student can sit many exams and each exam can have many
students. If a student has to re-sit an exam, you create a new exam with a
new date and link the student to the new exam. e.g. ID:5, Subject:World War
2 History, ExamDate:30 Nov 2003

Here's a link that will help you
http://www.databaseanswers.org/data_...xams/index.htm
The site's main index is
http://www.databaseanswers.org/data_models/index.htm

This is all to help you think about your design and help you become a good
developer.
Jeff

[Paul T. Rong]

Thank you.

I didn't build anything yet. It takes some time for me to digest all the
materials I got before I set up to build. Your comments helped me a lot.
May I ask you one question concerning your code?
------------------------
your code:

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intI As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qryStudentsSorted")
' Call the routine to clear all fields
' before loading the data.
ClearFields
' Add the StudentName to each text box.
With rst
.MoveFirst
Do Until .EOF Or intI = txtMax
intI = intI + 1
Me("txt" & intI) = !StudentName
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

-----------------------------------------------

Can you tell me what do they do these three lines at the end of the code:
"
rst.Close
Set rst = Nothing
Set db = Nothing
"
and why need to clear all fields before loading the data?

Thanks,

Paul



"Jeff Smith" <No***@Not.This.Address> дÈëÏûÏ¢ÐÂÎÅ
:ck**********@lust.ihug.co.nz...

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

> Questions: How do you know which exam you want to open when you

click

on a > student? What if that student sits more than one exam?

Sorry, I don't know.

> Do you want to open
> up a different form which displays the student with a sub-form that show all
> the exams that that student has sat?

Yes, that is what I want. But in this way, I have to repair my

tables
You'd be better off figuring out EXACTLY what this database has to do
BEFORE you build, or you're going to be repairing more than just a few
tables. But don't take my word for it - have a read through the
introduction to the
Access Developer Handbook. Otherwise, heaven help to poor person that
inherits your stuff. Normalize ON PAPER and then when you have an
acceptable normalized design, start building your database. Don't
know about you, but I would no sooner build a critical database
without doing all the initial "spadework" than I would build a house
without having a foundation poured.
I agree with Pieter here, you have to know exactly what the database has

to do before you even start. The code I posted the other day shows you what can be done with VBA. The questions I asked were there to make you think about
the database's design and also the same with the table structure I gave you.
My table structure was:
***tbl_Exams***
ExamID PK (Autonumber)
SubjectID FK
ExamDate

***tbl_ExamStudents***
ExamID PK (Long)
StudentID PK (Long)
Marks

With this structure, many students can sit the one exam.
The tbl_Exams is the exam paper itself with its unique ID, the subject
(topic) of the exam and its date. e.g. ID:3, Subject:World War 2 History,
ExamDate:14 Nov 2003
The tbl_ExamStudents links all the students that sit that exam and also the marks they get.
When also linked to the tbl_Students table you will get a many to many
relationship; each student can sit many exams and each exam can have many
students. If a student has to re-sit an exam, you create a new exam with a
new date and link the student to the new exam. e.g. ID:5, Subject:World War 2 History, ExamDate:30 Nov 2003

Here's a link that will help you
http://www.databaseanswers.org/data_...xams/index.htm
The site's main index is
http://www.databaseanswers.org/data_models/index.htm

This is all to help you think about your design and help you become a good
developer.
Jeff

[Paul T. Rong]

This is just one alternative:

The table "tblStudent":

StudentID StudentName Row Column Absence
1 Eva 1 1 Yes
2 Tomas 1 2 No
3 Barbara 1 3 No
....
8 Johana 2 3 No
.....
15 Ludo 3 5 Yes
make a cross query naming it "qryStudent" with [tblStudent]!Row as Row,
[tblStudent]!Column as Column, [tblStudent]!StudentName as Value (last),
then it is very easy to get something like:

Row 1 2 3 4 5
1 Eva Tomas Barbara Ivan Marek
2 Veronika Jan Johana Elena Pavel
3 Alex Julia Natalia Theresa Ludo
make a form frmStudent based on "qryStudent", and you can show the above
infomation beautifully in a form, without any code.

now I need help to make this possible:

note that there is a field "Absence" in table "tblStudent". How to make the
txtbox back color in red in form frmStudent? For example, Eva is absent, so
the text box with her name is in red color.

Thanks,

Paul

[Pieter Linden]

> now I need help to make this possible:

note that there is a field "Absence" in table "tblStudent". How to make the
txtbox back color in red in form frmStudent? For example, Eva is absent, so
the text box with her name is in red color.

Thanks,

Paul

To do this - use conditional formatting...

Also, this model of attendance is odd. It should be something like:

CREATE TABLE Student(
StudentID INTEGER PRIMARY KEY,
FirstName TEXT(25) NOT NULL,
LastName TEXT(25) NOT NULL,
....);

CREATE TABLE Attendance(
StudentID INTEGER,
SchoolDate DATE,
Attended BOOLEAN,
PRIMARY KEY (StudentID, SchoolDate));

If you do not put the attendance records in a separate table, you'll
lose the history of when a student was absent. You'll have only one
record instead of one per student per day.

[Paul T. Rong]

Pieter,

To do this - use conditional formatting...

can you help to give an example?

Paul
"Pieter Linden" <pi********@hotmail.com>
??????:bf**************************@posting.google .com...

now I need help to make this possible:

note that there is a field "Absence" in table "tblStudent". How to make the txtbox back color in red in form frmStudent? For example, Eva is absent, so the text box with her name is in red color.

Thanks,

Paul

To do this - use conditional formatting...

Also, this model of attendance is odd. It should be something like:

CREATE TABLE Student(
StudentID INTEGER PRIMARY KEY,
FirstName TEXT(25) NOT NULL,
LastName TEXT(25) NOT NULL,
...);

CREATE TABLE Attendance(
StudentID INTEGER,
SchoolDate DATE,
Attended BOOLEAN,
PRIMARY KEY (StudentID, SchoolDate));

If you do not put the attendance records in a separate table, you'll
lose the history of when a student was absent. You'll have only one
record instead of one per student per day.