Copy data from one field into many fields (1 Viewer)

[Rats]

I have a project with multiple tables with varying data but common to them all is a date field. The date field value can vary but generally is the same.
At times i run a process to create a duplicate of the entire record so that I can produce another scenario for the customer. In most cases this scenario is identical, initially to the original except that the date needs to be changed. At the moment we have to work through the various sub forms that comprise the client scenario and change all the dates to the new one where necessary. This is time consuming and I would like to be able to replace the manual activity with a command button that after inserting the new date in the [StartMonth] will change the dates in all records.

Private Sub Command451_Click()
On Error GoTo Err_Command451_Click

 [IncomeTbl Query].Form![StartDate] = [StartMonth]
 [Assets Tabbed Form].Form![Residences Query].Form![StartMonth] = [StartMonth]
[Assets Tabbed Form].Form![Cash/Super Investments Query].Form![StartMonth] = [StartMonth]
[Assets Tabbed Form].Form![Investments Portfolio].Form![StartMonth] = [StartMonth]
[Assets Tabbed Form].Form![Properties Query].Form![StartMonth] = [StartMonth]
[Assets Tabbed Form].Form![assetTblOtherAssets].Form![StartMonth] = [StartMonth]

Exit_Command451_Click:
    Exit Sub

Err_Command451_Click:
    MsgBox Err.Description
    Resume Exit_Command451_Click
    
End Sub

The above code works OK when the change is to a single date field on a form but I have instances where there is a subform with multiple records ie the loans table contains many loans for the one client and I need to change the date on each loan.

I tried the line shown below in the above process and it changed the first record only.

Forms![NewLoansInputFRm]![StartDate] = [StartMonth]

Can anyone advise me how to modify the code to pick up each record in an individual table or query and change the date.

Thanks for your help.

 

[neileg]

If all the records are related, why on earth do you want to store the date more than once?

 

[Mike375]

Peter

I have what might be similar.

One table holds the records for policy holders. Another table holds the records for policy benefits. Each month I receive from the insurance company a list of all the policy benefits, as lots of them change due to premium changing with age etc and I need to insert the client ID into each policy record.

I have a field made for each table/query where first name, last name and date of birth are joined and I use that for a link up.

With a macro it opens a tablular form where lastname etc match the client record. Then a SetValue/Next Record macro runs down the list. It then moves the client form to the next record and repeats. The macro that runs down the list of policy benefits simply stops when last name is null.

 

[ajetrumpet]

If all the records are related, why on earth do you want to store the date more than once?

He wants to do it on Mars, Neil. That's a completely different task.

 

[neileg]

He wants to do it on Mars, Neil. That's a completely different task.

Oh, how I laughed!