Setting up a form

[David Gerstman]

Say I have a class and I want to keep track of their book reports and grades.

For every student I have a name, SSN and phone number.

For each book report I have a name, title and grade.

Does it make sense to create a form for entering the book report information
by doing a join query (on name) between the student and book report tables,
and creating a form based on that query? (So I'd select the name and the SSN
and phone would show up, then I'd type in the title and grade.)

Is there a better way to create a form for entering the book report info?

 

[Clifford Bass]

Hi David,

First off, do not store SSNs in Access. It is not secure enough.
Better to use student ID numbers, which hopefully are not their SSNs or based
off of their SSNs. If you must use the SSNs, use something like SQL Server
to store the data. And make sure you set up the security on it properly.
Also, if you must use the SSNs, do not display them on the form where someone
looking over you shoulder can see them. You might set the Input Mask of the
SSN field to "Password" so that it shows as all asterisks. Then use the SSN
field's On Got Focus and On Lost Focus events to switch between the use of
the mask and no mask so that when you click in it you can actually see the
numbers and when you click somewhere out of it, it will go back to asterisks.

That said.... I might do it this way: Create a main student form that
displays the student data. Create a book report subform that is linked to
the main student form. In that subform it will display the book report
information for the currently displayed student. Then, if I separate out the
task of reading and grading the reports from the task of entering the book
report information; and I wanted to enter new book report information
quickly, all at once; I would create a third form. This form would use just
the book report table and provide for the selection of the student, by name,
from a combo box, with the rest of the fields as text fields for entering the
book report information. It would either be a datasheet view form or a
continuous forms type form.

Hope this helps,

Clifford Bass

 

[John W. Vinson]

Say I have a class and I want to keep track of their book reports and grades.

For every student I have a name, SSN and phone number.

For each book report I have a name, title and grade.

Does it make sense to create a form for entering the book report information
by doing a join query (on name) between the student and book report tables,
and creating a form based on that query? (So I'd select the name and the SSN
and phone would show up, then I'd type in the title and grade.)

Is there a better way to create a form for entering the book report info?

Step back a bit. Starting your design with the Form is wrong way about. Tables
are fundamental; forms are just tools!

You need a table of students with the lastname, firstname, SSN and phone
number. Unless every student is reporting on a different book (and perhaps
even then!) you need a table of Books, with a BookID (perhaps its ISBN),
title, and other information about the book as a thing in itself.

The third table would be a table of reviews, with a StudentID (SSN), a BookID,
a field for the grade, and perhaps other fields (date turned in, comments,
etc.)

You would use a Form based on the Students table, with a Subform based on the
Reviews table; on this subform you could have a combo box to select the book,
perhaps with code in the combo's Not In List event to pop up a Books form to
allow entry of books that haven't been reviewed before.

 

[David Gerstman]

Clifford,

Don't worry. I'm not doing this exactly. I just needed a reasonable analog
to my actual task. So there will be no SSN's in this database.

David

 

[David Gerstman]

John,

Thank you. I probably didn't explain what I was doing exactly right. I am
starting with tables. But I was uncertain about how they should relate. You
clarified things a lot for me.

David

 

[Clifford Bass]

Hi David,

Glad to hear it about the SSNs. Hope my other information was of some
use.

Clifford Bass

 

[David Gerstman]

Yes and thank you.

Hi David,

Glad to hear it about the SSNs. Hope my other information was of some
use.

Clifford Bass

 

[Clifford Bass]

Hi David,

Glad to hear it. And you are welcome!

Clifford Bass