Subforms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been building a schoolrecords database for primary school. I have
tables for each area eg; spellingmarks table has week1, week2, etc. Linked to
a student table. It's really taking shape but the subform I have made,
happily sends marks off to Week1, Week2, etc in the SpellingMarks Table.
After Week 8 which is the last field for data the subform in datasheet view
provides another line and accepts more entries. I need it to stop at the
number of fields nominated in each curriculum area. I found that setting the
SpellingMarks table Week # indexes to (Yes-no duplictes) stops entries but
there must be a better way, it seems clumsy. Thanks for the great information
on your site have been problem solving all week but have not been able to
solve this one.

Peter Norton
 
Is the subform based on SpellingMarks table? Is the main form based on the
Student table? What do you mean when you say that you "need it to stop at
the number of fields nominated in each curriculum area"? What happens after
eight weeks? Does another eight week "term" start? Is so, perhaps you need
a Term table, with the curriculum table linked to that. There are probably a
number of possibilities, depending on the details.
 
Hi Bruce,
Thanks for the response. The subform is based on the SpellingMarks table
which has Eight number fields to collect 8 weeks worth of spelling marks. Yes
the main form is based on the student table with Student ID, FName, SName and
a combo box to pull up the ids by names and take the form to that record.
When the subform fills up with wk1 – 20, wk2 – 30, etc I want it to know that
there are only 8 fields for this data. Instead the datasheet view of the
subform generates another row of cells which the spellingmarks table accepts
as a week # mark it does this by generating a new autonumber SpellingMarkID
(PK) and listing the StudentID(FK) as say the second Student(n)ID.

I have just build the database and was expecting to base the life of each
table around a semester or term. I already have a curriculum table linking
subjects to other data. Once I solve this dilemma all other subject tables
should allow the specified week number entries to respective tables in
Number, Measurement, etc.

Many thanks and Happy New Year.

Peter N
 
You may be stuck in spreadsheet thought processes. There is no need to have
a separate table for each semester. Not only is there no need, it is not a
good idea. A table may resemble a spreadsheet, but the similarity ends
there. If you need to limit the recordset then you should use a query based
on the table. If the table gets too large you can move some of the data to
an archive table.
I may be on the wrong track here, but I wonder if it would serve your
purposes to set up a one-to-one relationship between the Student table and
the SpellingMarks table. I don't have time to set up a sample and test it,
so I can only speculate. If your method for preventing a new row from
appearing works, you could add code to the On Current event for the subform,
comparing the StudentID foreign key with StudentID in the previous record, or
something like that, and generating your own error message (something
helpful, for instance) if duplication occurs. By the way, are you absolutely
certain you will never need more than eight weeks?
What does this mean: "When the subform fills up with wk1 – 20, wk2 – 30,
etc I want it to know that there are only 8 fields for this data."? I know
that databases can be difficult to describe, but right now I am unsure of
some of the details of what you need to accomplish. Perhaps if you posted
some sample data (for one student), and describe just what needs to happen
when Week 8 is filled in it would be clearer.
I think you can get what you need, but you would do well to get your
database structure and table relationships established properly right from
the start. I fear you are building unneeded future complexity into your
current design.
 
Hi Bruce
I have cleaned up the table structure with help from John Vinson, see the
thread above "subform entry limited to n fields" so its still a work in
progress. I'm starting to get the idea of what I want and how to get it,

PeterN
 
Back
Top