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

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

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

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" <etjsk@hotmail.com> wrote in message
news:S699d.354432$vG5.89840@news.chello.at...[color=blue]
> 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[/color]
them[color=blue]
> "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[/color]
just[color=blue]
> not realistic under Access.
>
> Paul
>
>[/color]

re: 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" <etjsk@hotmail.com> wrote in message
news:S699d.354432$vG5.89840@news.chello.at...[color=blue]
> 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[/color]
them[color=blue]
> "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[/color]
just[color=blue]
> not realistic under Access.
>
> Paul[/color]


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?

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

On Thu, 07 Oct 2004 10:45:06 GMT, "Paul T. Rong" <etjsk@hotmail.com>
wrote:
[color=blue]
>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
>
>[/color]
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

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

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" <etjsk@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:S699d.354432$vG5.89840@news.chello.at...[color=blue]
> 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[/color]
them[color=blue]
> "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[/color]
just[color=blue]
> not realistic under Access.
>
> Paul
>
>[/color]

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

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)

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

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.

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

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" <etjsk@hotmail.com> wrote in message
news:jZb9d.355361$vG5.152105@news.chello.at...[color=blue]
> 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[/color]
so[color=blue]
> 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:[/color]
"SubjectID"[color=blue]
> 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[/color]
"frmStudent".[color=blue]
>
> I am not sure if I should choose a unbound form, and use code (I prefer[/color]
not[color=blue]
> 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[/color]

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

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.
[color=blue]
> 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?[/color]

Sorry, I don't know.
[color=blue]
> Do you want to open
> up a different form which displays the student with a sub-form that show[/color]
all[color=blue]
> the exams that that student has sat?[/color]

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" <NoWay@Not.This.Address> дÈëÏûÏ¢ÐÂÎÅ
:ck5nk5$svh$1@lust.ihug.co.nz...[color=blue]
> 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[/color]
have[color=blue]
> to add 50 more text boxes, set their visible properties to No, name them[/color]
the[color=blue]
> same way as above but use something different than "txt" (in the code[/color]
below[color=blue]
> 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[/color]
view.[color=blue]
>
> 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[/color]
using[color=blue]
> 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[/color]
all[color=blue]
> 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" <etjsk@hotmail.com> wrote in message
> news:jZb9d.355361$vG5.152105@news.chello.at...[color=green]
> > 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[/color][/color]
student[color=blue]
> so[color=green]
> > that when I click on each of their names and a form can be opened with[/color][/color]
the[color=blue][color=green]
> > 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:[/color]
> "SubjectID"[color=green]
> > and "Subject"
> >
> > I would like to see the average mark from all subjects can be shown[/color][/color]
later[color=blue][color=green]
> > under each of the students names.
> >
> > above is just my plan, I was stopped at the first step at the[/color]
> "frmStudent".[color=green]
> >
> > I am not sure if I should choose a unbound form, and use code (I prefer[/color]
> not[color=green]
> > 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[/color]
>
>[/color]

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

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

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.

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

You are right. Thanks.

Paul


"Pieter Linden" <pietlinden@hotmail.com>
??????:bf31e41b.0410091647.5b99311d@posting.google .com...[color=blue][color=green][color=darkred]
> > > Questions: How do you know which exam you want to open when you click[/color][/color][/color]
on a[color=blue][color=green][color=darkred]
> > > student? What if that student sits more than one exam?[/color]
> >
> > Sorry, I don't know.
> >[color=darkred]
> > > Do you want to open
> > > up a different form which displays the student with a sub-form that[/color][/color][/color]
show[color=blue][color=green]
> > all[color=darkred]
> > > the exams that that student has sat?[/color]
> >
> > Yes, that is what I want. But in this way, I have to repair my tables[/color]
>
> 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.[/color]

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

"Pieter Linden" <pietlinden@hotmail.com> wrote in message
news:bf31e41b.0410091647.5b99311d@posting.google.c om...[color=blue][color=green][color=darkred]
> > > Questions: How do you know which exam you want to open when you click[/color][/color][/color]
on a[color=blue][color=green][color=darkred]
> > > student? What if that student sits more than one exam?[/color]
> >
> > Sorry, I don't know.
> >[color=darkred]
> > > Do you want to open
> > > up a different form which displays the student with a sub-form that[/color][/color][/color]
show[color=blue][color=green]
> > all[color=darkred]
> > > the exams that that student has sat?[/color]
> >
> > Yes, that is what I want. But in this way, I have to repair my tables[/color]
>
> 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.[/color]

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

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

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" <NoWay@Not.This.Address> дÈëÏûÏ¢ÐÂÎÅ
:ckb2o0$kgh$1@lust.ihug.co.nz...[color=blue]
>
> "Pieter Linden" <pietlinden@hotmail.com> wrote in message
> news:bf31e41b.0410091647.5b99311d@posting.google.c om...[color=green][color=darkred]
> > > > Questions: How do you know which exam you want to open when you[/color][/color][/color]
click[color=blue]
> on a[color=green][color=darkred]
> > > > 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[/color][/color]
> show[color=green][color=darkred]
> > > all
> > > > the exams that that student has sat?
> > >
> > > Yes, that is what I want. But in this way, I have to repair my[/color][/color][/color]
tables[color=blue][color=green]
> >
> > 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.[/color]
>
> I agree with Pieter here, you have to know exactly what the database has[/color]
to[color=blue]
> do before you even start. The code I posted the other day shows you what[/color]
can[color=blue]
> 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[/color]
you.[color=blue]
>
> 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[/color]
the[color=blue]
> 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[/color]
War[color=blue]
> 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
>
>[/color]

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

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

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

> now I need help to make this possible:[color=blue]
>
> 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[/color]

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.

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

Pieter,
[color=blue]
>To do this - use conditional formatting...[/color]


can you help to give an example?

Paul


"Pieter Linden" <pietlinden@hotmail.com>
??????:bf31e41b.0410111315.1e50c9ef@posting.google .com...[color=blue][color=green]
> > now I need help to make this possible:
> >
> > note that there is a field "Absence" in table "tblStudent". How to make[/color][/color]
the[color=blue][color=green]
> > txtbox back color in red in form frmStudent? For example, Eva is absent,[/color][/color]
so[color=blue][color=green]
> > the text box with her name is in red color.
> >
> > Thanks,
> >
> > Paul[/color]
>
> 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.[/color]