link field main form and subform

I have a subform that links to the main form by a field, say, studentID. In
the subform, besides the studentID textbox, there is another text boxe to
refer to the main forms, say [TestDate]. In the [TestDate]'s Control Source,
I bound it to a field, i.e., [TestDate] in the table. In its Default Value
property, I bound it to the main form, as following,

Defaul Value=[Forms]![frmMain]![testdate].

Do you know which one, the one bounds to the control source or the one in
the default value property, will show up every time a user open this
main/subform?
Thank you,

You can link additional fields between a form and a sub-form, but why? You
are duplicating data that doesn't need to be duplicated. If there is only
one test date, store it in the main table. If there are multiple test dates,
which I suspect is the case, then you need to store them in the table of the
sub form.

I would use three tables. One table with the student information (fairly
static) including StudentID PrimaryKey, another with the test information
including TestDate and TestID (AutoNumber) PK, and a third with test results
including TestScore, TestID, and StudentID.
You can then use a form to enter the test information (TestDate, etc.) and a
subform (continuous) that you enter StudentID, via a combo box where you
choose the student's name, and the TestScore. Link this subform to the main
via the TestID fields in both. You have now stored the TestDate only once
and test results table remains very small.

Tim wrote:
I have a subform that links to the main form by a field, say, studentID. In
the subform, besides the studentID textbox, there is another text boxe to
refer to the main forms, say [TestDate]. In the [TestDate]'s Control Source,
I bound it to a field, i.e., [TestDate] in the table. In its Default Value
property, I bound it to the main form, as following,

Defaul Value=[Forms]![frmMain]![testdate].

Do you know which one, the one bounds to the control source or the one in
the default value property, will show up every time a user open this
main/subform?
Thank you,

--
Message posted via http://www.accessmonster.com

Hi,
Thank you for spending time to help me. Sorry because I did not state
clearly in my previous post. Actually, the subform has the control source
bounded to a table that have three (primary) keys, studentID, testdate, and
test#. StudentID is linked to the main form. The testdate is referred to the
test date in the main form, and the test# is a constant value assigned in the
Default Value property of that field.

About the testdate, there are two values this textbox may get. The first one
is from the main form by referring to it in the Defaul Value property, the
second one is from its record source. When I put both of them in the property
sheet of testdate, I don't know which one will show up when one opens the
form.
Thank you,












"jahoobob via AccessMonster.com" wrote:

You can link additional fields between a form and a sub-form, but why? You
are duplicating data that doesn't need to be duplicated. If there is only
one test date, store it in the main table. If there are multiple test dates,
which I suspect is the case, then you need to store them in the table of the
sub form.

I would use three tables. One table with the student information (fairly
static) including StudentID PrimaryKey, another with the test information
including TestDate and TestID (AutoNumber) PK, and a third with test results
including TestScore, TestID, and StudentID.
You can then use a form to enter the test information (TestDate, etc.) and a
subform (continuous) that you enter StudentID, via a combo box where you
choose the student's name, and the TestScore. Link this subform to the main
via the TestID fields in both. You have now stored the TestDate only once
and test results table remains very small.

Tim wrote:
I have a subform that links to the main form by a field, say, studentID. In
the subform, besides the studentID textbox, there is another text boxe to
refer to the main forms, say [TestDate]. In the [TestDate]'s Control Source,
I bound it to a field, i.e., [TestDate] in the table. In its Default Value
property, I bound it to the main form, as following,

Defaul Value=[Forms]![frmMain]![testdate].

Do you know which one, the one bounds to the control source or the one in
the default value property, will show up every time a user open this
main/subform?
Thank you,

--
Message posted via http://www.accessmonster.com

--------------------
Thank you for spending time to help me. Sorry, because I did not state
clearly in my previous post. Actually, the subform has the control source
bounded to a table that have three (primary) keys, studentID, testdate, and
test#. StudentID is linked to the main form. The testdate is referred to the
test date in the main form, and the test# is a constant value assigned in the
Default Value property of that field.

About the testdate, there are two values this textbox may get. The first one
is from the main form by referring to it in the Defaul Value property, the
second one is from its record source. When I put both of them in the property
sheet of testdate, I don't know which one will show up when one opens the
form.
--------------------
One more question, even though I already link the textbox [testdate] in the
subform to the main form, Defaul Value=[Forms]![frmMain]![testdate], the
[testdate] in the subform still shows the old value (old date) not the
current date. Do you know why?

Thank you,


"jahoobob via AccessMonster.com" wrote:

You can link additional fields between a form and a sub-form, but why? You
are duplicating data that doesn't need to be duplicated. If there is only
one test date, store it in the main table. If there are multiple test dates,
which I suspect is the case, then you need to store them in the table of the
sub form.

I would use three tables. One table with the student information (fairly
static) including StudentID PrimaryKey, another with the test information
including TestDate and TestID (AutoNumber) PK, and a third with test results
including TestScore, TestID, and StudentID.
You can then use a form to enter the test information (TestDate, etc.) and a
subform (continuous) that you enter StudentID, via a combo box where you
choose the student's name, and the TestScore. Link this subform to the main
via the TestID fields in both. You have now stored the TestDate only once
and test results table remains very small.

Do you have a main form with student information and a subform all that
student's tests in it? Or, do you have a main form with the student info and
a subform with one test on it and you set the test date and cycle through the
various students while keeping that test date on the main form to transfer to
each student's test in the subform.
Unless you adminster the same test to various students on different dates
then you are storing too much duplicate information. If each test has a
number as you indicate then you should store the test date with that number
once in one table and ONLY store the test number in a table with the test
results and studentID.
If you do administer the same test on different dates, then the subform needs
a trigger to "know" which test date to use. You could use the on lost focus
of the test date in the subform to check to see if the test date field is
Null and if it is use the main form test date. If there is already a date
there then the main form test date won't be transferred.
I hope this helps.
Bob
Tim wrote:
--------------------
Thank you for spending time to help me. Sorry, because I did not state
clearly in my previous post. Actually, the subform has the control source
bounded to a table that have three (primary) keys, studentID, testdate, and
test#. StudentID is linked to the main form. The testdate is referred to the
test date in the main form, and the test# is a constant value assigned in the
Default Value property of that field.

About the testdate, there are two values this textbox may get. The first one
is from the main form by referring to it in the Defaul Value property, the
second one is from its record source. When I put both of them in the property
sheet of testdate, I don't know which one will show up when one opens the
form.
--------------------
One more question, even though I already link the textbox [testdate] in the
subform to the main form, Defaul Value=[Forms]![frmMain]![testdate], the
[testdate] in the subform still shows the old value (old date) not the
current date. Do you know why?

Thank you,



You can link additional fields between a form and a sub-form, but why? You
are duplicating data that doesn't need to be duplicated. If there is only
[quoted text clipped - 11 lines]
via the TestID fields in both. You have now stored the TestDate only once
and test results table remains very small.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200607/1

Thank you!!! It has shown that you know "stuff". I will study your advice and
will let you know ...
Thank you again,


"jahoobob via AccessMonster.com" wrote:

Do you have a main form with student information and a subform all that
student's tests in it? Or, do you have a main form with the student info and
a subform with one test on it and you set the test date and cycle through the
various students while keeping that test date on the main form to transfer to
each student's test in the subform.
Unless you adminster the same test to various students on different dates
then you are storing too much duplicate information. If each test has a
number as you indicate then you should store the test date with that number
once in one table and ONLY store the test number in a table with the test
results and studentID.
If you do administer the same test on different dates, then the subform needs
a trigger to "know" which test date to use. You could use the on lost focus
of the test date in the subform to check to see if the test date field is
Null and if it is use the main form test date. If there is already a date
there then the main form test date won't be transferred.
I hope this helps.
Bob
Tim wrote:
--------------------
Thank you for spending time to help me. Sorry, because I did not state
clearly in my previous post. Actually, the subform has the control source
bounded to a table that have three (primary) keys, studentID, testdate, and
test#. StudentID is linked to the main form. The testdate is referred to the
test date in the main form, and the test# is a constant value assigned in the
Default Value property of that field.

About the testdate, there are two values this textbox may get. The first one
is from the main form by referring to it in the Defaul Value property, the
second one is from its record source. When I put both of them in the property
sheet of testdate, I don't know which one will show up when one opens the
form.
--------------------
One more question, even though I already link the textbox [testdate] in the
subform to the main form, Defaul Value=[Forms]![frmMain]![testdate], the
[testdate] in the subform still shows the old value (old date) not the
current date. Do you know why?

Thank you,



You can link additional fields between a form and a sub-form, but why? You
are duplicating data that doesn't need to be duplicated. If there is only
[quoted text clipped - 11 lines]
via the TestID fields in both. You have now stored the TestDate only once
and test results table remains very small.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200607/1