Update records in subform with value from main form

I have a form (frmPOReq_Header) with a field called ReqdDate. Then there
is a subform (frmPOReq_Detail) where we add line items. When entering
records on the subform the ReqdDate defaults to the value of ReqdDate on
the main form. I would like to be able to go back in later and change
the ReqdDate on the main form and then update all the line items on the
subform with that new date. I guess the best way to do this would be
click a button to "update" the ReqdDate field for records on the
subform. Need help with code.
Thanks,
Darrell

Just run an update query.
create new query with the underlying table which need to be updated and in
the criteria of the field to be updated link to the ReqDate in the main form.
Call the query by property after update or make a button as you suggest with
code on click Docmd.openqyery "name_of_query"
rgds

R.


"Darrell Childress" wrote:

I have a form (frmPOReq_Header) with a field called ReqdDate. Then there
is a subform (frmPOReq_Detail) where we add line items. When entering
records on the subform the ReqdDate defaults to the value of ReqdDate on
the main form. I would like to be able to go back in later and change
the ReqdDate on the main form and then update all the line items on the
subform with that new date. I guess the best way to do this would be
click a button to "update" the ReqdDate field for records on the
subform. Need help with code.
Thanks,
Darrell

On Thu, 17 Sep 2009 10:32:56 -0400, Darrell Childress
wrote:

I have a form (frmPOReq_Header) with a field called ReqdDate. Then there
is a subform (frmPOReq_Detail) where we add line items. When entering
records on the subform the ReqdDate defaults to the value of ReqdDate on
the main form. I would like to be able to go back in later and change
the ReqdDate on the main form and then update all the line items on the
subform with that new date. I guess the best way to do this would be
click a button to "update" the ReqdDate field for records on the
subform. Need help with code.
Thanks,
Darrell

Well... that would be very bad design, I fear. You're storing ReqdDate
redundantly, in two different tables, so there is no automatic way to ensure
that the values in the two tables agree.

I would suggest removing the field from the detail table altogether, and just
linking to the parent table to determine the value of the date.

If that doesn't meet your needs please explain why.
--

John W. Vinson [MVP]

Thanks John for the response. I do need to store the data in two
different tables and here's why. The user fills in the header info with
misc info, including the ReqdDate (Required Date). When they enter the
line items, I have the ReqdDate for the items default to whatever is on
the header, but the user can change that if necessary because one item
may be needed on 9/17 and another may not be needed until 9/19. In at
least 95% (probably 99%) of the cases, all items are needed the same date.
I suppose I could always not actually store the ReqdDate in the header
table and use an unbound field on the form merely to use as a default
for the detail items.

John W. Vinson wrote:
On Thu, 17 Sep 2009 10:32:56 -0400, Darrell Childress
wrote:

I have a form (frmPOReq_Header) with a field called ReqdDate. Then there
is a subform (frmPOReq_Detail) where we add line items. When entering
records on the subform the ReqdDate defaults to the value of ReqdDate on
the main form. I would like to be able to go back in later and change
the ReqdDate on the main form and then update all the line items on the
subform with that new date. I guess the best way to do this would be
click a button to "update" the ReqdDate field for records on the
subform. Need help with code.
Thanks,
Darrell

Well... that would be very bad design, I fear. You're storing ReqdDate
redundantly, in two different tables, so there is no automatic way to ensure
that the values in the two tables agree.

I would suggest removing the field from the detail table altogether, and just
linking to the parent table to determine the value of the date.

If that doesn't meet your needs please explain why.

On Thu, 17 Sep 2009 15:27:28 -0400, Darrell Childress
wrote:

Thanks John for the response. I do need to store the data in two
different tables and here's why. The user fills in the header info with
misc info, including the ReqdDate (Required Date). When they enter the
line items, I have the ReqdDate for the items default to whatever is on
the header, but the user can change that if necessary because one item
may be needed on 9/17 and another may not be needed until 9/19. In at
least 95% (probably 99%) of the cases, all items are needed the same date.
I suppose I could always not actually store the ReqdDate in the header
table and use an unbound field on the form merely to use as a default
for the detail items.

I think that would be the prudent approach. Having two different ReqdDate
fields in related tables will be confusing at some point!
--

John W. Vinson [MVP]