STOP.
I thought I understood the structure. The screen shots confirmed it.
You
absolutely should not store informatioin in field names, which is what
you
are doing with Semester1 and the like. To take Courses as an example,
this
should be your table structure:
tblCourses
CoursesID (autonumber PK)
DemoUniqueID (link to tblDemo)
Semester
CourseNumber
CourseName
Grade
That's all. Let's say DemoUniqueID from tblDemo is 50. If you are
recording ten Semesters there will be ten records in tblCourse with 50 as
the DemoUniqueID value. These records are related to the tblDemo record
with DemoUniqueID 50. I don't quite understand why there is just one
course
per semester, or I don't remember from your explanation, but that doesn't
matter for now.
No other way of structuring this is really worth considering.
A control, just to be clear, is just about anything you can place on a
form:
text box, combo box, line, label, and so forth. One type of control is a
tab control, which can have quite a few individual pages (I don't recall
if
there is a limitation, but if so you are not likely to reach it). You
can
place your main form text boxes, labels, and so forth on one page of the
tab
control. Another type of control is a subform control, which is a
"container" for a subform. Place a subform control for Courses on one
tab,
Tuition on another, and so forth. Design your subforms separately, as
individual forms, then use them as the Source Objects for the various
subform controls.
This is one way. Another is to use OpenArgs to open pop-up forms, as Ken
suggested. The code he posted would be in the Click event of the command
button that opens the form.
You can give people what they want, but you absolutely must control the
structure. You cannot have satisfactory results if you start with the
forms
and try to build a structure to support them. You need to start with the
structure. It is more than I can get into here, but you will be doing
yourself a favor if you do some investigating into design principles and
other structural stuff. Once good source is here:
http://allenbrowne.com/casu-22.html
I have had people tell me they need data copied from one place to another
within the database, and I just agree. What they see gives them what
they
want, but the underlying structure is sound, which it would not be if I
literally followed their instructions. For displaying ten (or whatever)
semesters worth of courses using the structure I have described, use a
continuous form as the default view for either the subform or the pop-up
form, depending on your approach.
I don't think I was clear on the record/size thing. I meant the actual
number
of fields per table, and therefore the actual image size of each
related
table is large (mostly comparable in size to the current size of the
Demographics form), so I don't think visible subforms would be user
friendly
in this case, even though I know that probably would be the easiest way
to
get the DemoUniqueIDs to cooperate.
Maybe this will help as far as understanding the structure goes:
http://img.photobucket.com/albums/v43/caninecg/frmDemographics.jpg
http://img.photobucket.com/albums/v43/caninecg/frmCourses.jpg
http://img.photobucket.com/albums/v43/caninecg/frmEmploymentVerification.jpg
http://img.photobucket.com/albums/v43/caninecg/frmStipendExpense.jpg
http://img.photobucket.com/albums/v43/caninecg/frmTuition.jpg
The query I was referring to (sorry, that was vague) came from the
Query
Builder for the control source when we started out with tabs, so yes,
that
did include ALL the tables and all the fields from the tables that
would
be
visible in the forms, using left joins on each of the tables.
I'm a little confused on the subform control bit--I've been Googling
and
scavenging like mad and found a similar suggestion on another message
board,
but I can't actually find where the subform control is. Is it the
control
source for the subform itself, or is it something in the properties of
the
parent form?
I keep looking at Ken's suggestion(I do like the idea of "pushing" the
ID
data onto the other tables), but I don't really comprehend it yet. I
don't
know where I would put that code. Is that something that would go in
the
box
that opens up when I click the ellipses where it says Event Procedure?
Right now the only records in the database are test records, so I'll be
resetting the autonumber once Fiscal gives their approval on the final
design, which hopefully, if they do want another ID system, they'll let
me
know before the database goes live, but I'll still probably put another
auto
increment in the Demographics table if that happens, just in case
something
goes wrong with the Demo ID.
When I initially built the tables, they only had one field of each type
per
record, and so did the forms, but my coworker who will be using the
database
the most said that he and the other directors who will view it would
prefer
for all the information to be on one screen, so he doesn't keep
scrolling
through records for each person in the other tables. At this point I
pretty
much have to give them what they want in terms of appearance; I doubt
they'll
know or care about the underlying data structure, as long as it looks
pretty.
I won't be using spreadsheet view in this database; for the other
databases
where I do use it, those tables are just recording course attendance
records.
:
I'm not sure I quite understand the structure, but the number of
records
in
a subform's record source table does not preclude the form/subform
structure. The subform shows only records related to the main form's
current record.
Generally speaking, each subform would have its own query. Rather
than
trying all five at once, try one. If that works, add another. When
you
speak of "the query" I wonder if you were trying to make a single
query
work
for all of the subforms.
Hide a subform by hiding the subform control (the "box" containing the
subform) on the main form.
An expression as a Control Source will not be stored. A text box is a
control. A control's source is either stored data from a field in the
form's Record Source, or an expression. Maybe there are some other
nuances,
but that is the general idea.
If you are using pop-up forms unassociated with the main form, see
Ken's
suggestion for using OpenArgs when you open the form. I suggested
using
the
[Forms!]![frmDemographics]![DemoUniqueID] expression as the Default
Value
property of the control bound to DemoUniqueID. This would be an
approach
for adding a new record in a form that is not a subform, as
DefaultValue
applies only to new records. In a subform DemoUniqueID is the linking
field, so it is populated automatically.
Do not attempt to use autonumber for DemoUniqueID in the related
tables.
If
it is autonumber in the main table, it *must* be Long Integer in the
related
tables.
Even if they come up with new numbering, I suggest staying with
autonumber
rather than trying to update all of the records (while hoping they
don't
change the numbering system).
If you are storing "up to ten courses" etc. you should be storing each
record separately rather than creating a table with ten fields that
may
or
may not be used, again while hoping the number doesn't change in the
future.
You can have ten records in the courses table, all of which are
related
to
the main form's record. Same with semesters, etc. I strongly advise
against the spreadsheet approach. It is likely to cripple you sooner
rather
than later. If nothing else it makes searches much more difficult.
