Can I create a new control on a form which will reflect the new data entered
in a field on a sub form? Can that be bound to a parent table so that when a
new date is entered on the subform it replaces the old date in the control
and table?
Yes. Main form based on Parent Table one to many Children. Subform based on
Child Table.....Child table has a Date field and three descriptor fields.
When I update test results on each parent the subform records the new data
and displays the test result history on the main form. Specifically when a
new record is entered on the subform I would like to have the new entry for
the date field also either displayed in a control on the main form and/or
moved into a field in the Parent table. This way I can preserve a large
number of reports and macros for sending the reports that I have made.
John Vinson said:Yes. Main form based on Parent Table one to many Children. Subform based on
Child Table.....Child table has a Date field and three descriptor fields.
When I update test results on each parent the subform records the new data
and displays the test result history on the main form. Specifically when a
new record is entered on the subform I would like to have the new entry for
the date field also either displayed in a control on the main form and/or
moved into a field in the Parent table. This way I can preserve a large
number of reports and macros for sending the reports that I have made.
I guess I don't understand. You want to store (I presume) multiple
records of history in one record on the mainform? Or you want to store
(redundantly) the latest date on the "many" side table in the main
table?
You can *display* the most recent date in the related table on the
mainform using a textbox with a recordsource such as
=DMax("[datefield]", "[child-table-name]", "[ID] = " & [txtID])
where txtID contains the main table's linking field and ID is the
foreign key in the child table.
John W. Vinson[MVP]
Yes it is redundant.....but notifications are based on the most recent date
from the history record in the child table. Lacking advanced programming
knowledge I have used expressions as filters to produce reports needed and
thus I am able to send notices to 40 different departments. Rather than
rewrite everything, being able to redundantly move that date to the main form
may save that. I am open to trying to accomplish things in a better way...I
am just still learning this program. Thank you....I will try this
expression:
=DMax("[datefield]", "[child-table-name]", "[ID] = " & [txtID])
datefield is the field on the child table then child-table-name
correct...
then ID is the field on the main form I am copying the date to.....
John Vinson said:Yes it is redundant.....but notifications are based on the most recent date
from the history record in the child table. Lacking advanced programming
knowledge I have used expressions as filters to produce reports needed and
thus I am able to send notices to 40 different departments. Rather than
rewrite everything, being able to redundantly move that date to the main form
may save that. I am open to trying to accomplish things in a better way...I
am just still learning this program. Thank you....I will try this
expression:
=DMax("[datefield]", "[child-table-name]", "[ID] = " & [txtID])
datefield is the field on the child table then child-table-name
correct...
then ID is the field on the main form I am copying the date to.....
ID is the name of the field in the child table which you are using as
the foreign key (it will be the Child Link Field property of the
Subform control); txtID is the name of a textbox on the main form
which contains the Master Link Field = the primary key of the main
form's table.
John W. Vinson[MVP]
John Vinson said:Yes it is redundant.....but notifications are based on the most recent date
from the history record in the child table. Lacking advanced programming
knowledge I have used expressions as filters to produce reports needed and
thus I am able to send notices to 40 different departments. Rather than
rewrite everything, being able to redundantly move that date to the main form
may save that. I am open to trying to accomplish things in a better way...I
am just still learning this program. Thank you....I will try this
expression:
=DMax("[datefield]", "[child-table-name]", "[ID] = " & [txtID])
datefield is the field on the child table then child-table-name
correct...
then ID is the field on the main form I am copying the date to.....
ID is the name of the field in the child table which you are using as
the foreign key (it will be the Child Link Field property of the
Subform control); txtID is the name of a textbox on the main form
which contains the Master Link Field = the primary key of the main
form's table.
John W. Vinson[MVP]
John Vinson said:Yes it is redundant.....but notifications are based on the most recent date
from the history record in the child table. Lacking advanced programming
knowledge I have used expressions as filters to produce reports needed and
thus I am able to send notices to 40 different departments. Rather than
rewrite everything, being able to redundantly move that date to the main form
may save that. I am open to trying to accomplish things in a better way...I
am just still learning this program. Thank you....I will try this
expression:
=DMax("[datefield]", "[child-table-name]", "[ID] = " & [txtID])
datefield is the field on the child table then child-table-name
correct...
then ID is the field on the main form I am copying the date to.....
ID is the name of the field in the child table which you are using as
the foreign key (it will be the Child Link Field property of the
Subform control); txtID is the name of a textbox on the main form
which contains the Master Link Field = the primary key of the main
form's table.
John W. Vinson[MVP]