Solved Copy entry from (1 Viewer)

[mtagliaferri]

As part of a wider project I am trying to figure out how to copy the value from a field from a primary table into a field into a secondary table within a relationship one-to-many.

Basically on entering the date in the primary table as soon as one or more records are added in the secondary table the date from the primary table is copied in the date field in the related record, this should be done possibly with an after update when a value is entered in another in the TimeStart

the below example is for the user to enter the PlannedDate field, then once the TimeStart has been filled the OrderDate value is copied from the PlannedDate on the main form

 

[Ranman256]

int the subform properties
set LINK MASTER FIELD
and LINK CHILD FIELD to that field.

 

[theDBguy]

Can't look at your file now, but aren't you using Autonumber fields as primary keys?

Sent from phone...

 

[mtagliaferri]

Yes I do have the subform linked with autonumber and that works perfectly.

What I need is to avoid the user to type the date OrderDate field in the subform, the user types the time (or I might move the Room field order) once this is filled in via an After update I woul like to the OrderDate to be automatically populated with the value that has been entered in PlannedOrder on the main form.
In the below example once the value 12:00 and 13:00 is typed into the subform I would like the Order Date to be populated with 10/10/2024 the date on the main form.

I am aware that the record is anyway linked but I will use the copied value possibly to concatenate times and make specific time based calculations.

Hope this makes sense,

 

[MajP]

Basically on entering the date in the primary table as soon as one or more records are added in the secondary table the date from the primary table is copied in the date field in the related record, this should be done possibly with an after update when a value is entered in another in the TimeStart

That is not what you want.
I am guessing you want the Orderdate to default to the planned date, but still link by ID as you have it.

Private Sub Form_Current()
  SetDefault
End Sub

Private Sub PlannedDate_AfterUpdate()
  SetDefault
End Sub

Public Sub SetDefault()
  If IsDate(Me.PlannedDate) Then
    Me.tblSecondary_subform.Form.OrderDate.DefaultValue = "#" & (Me.PlannedDate) & "#"
  Else
     Me.tblSecondary_subform.Form.OrderDate.DefaultValue = ""
  End If
End Sub

FYI. Defaultvalue has to be a string. So I had to pass it with "#" surrounding it.
If you were linking by date, that would not make sense to me. That would say every planned date equals every order date for the child. If that is the case then there would never be a reason for an order date at all. The order date would simply be the parent's planned date.

 

[AccessBlaster]

If that is the case then there would never be a reason for an order date at all. The order date would simply be the parent's planned date.

yeah, it would be redundant information.

 

[theDBguy]

Yes I do have the subform linked with autonumber and that works perfectly.

What I need is to avoid the user to type the date OrderDate field in the subform, the user types the time (or I might move the Room field order) once this is filled in via an After update I woul like to the OrderDate to be automatically populated with the value that has been entered in PlannedOrder on the main form.
In the below example once the value 12:00 and 13:00 is typed into the subform I would like the Order Date to be populated with 10/10/2024 the date on the main form.

I am aware that the record is anyway linked but I will use the copied value possibly to concatenate times and make specific time based calculations.

Hope this makes sense,
View attachment 116662

Normally, I would say you don't need the OrderDate, because you already have the PlannedDate; unless, would it be possible to have a different OrderDate than a PlannedDate?

In any case, if you want to keep the OrderDate, then I would agree with @MajP to use the DefaultValue property.

Edit: Oops, I'm a little slow in typing my response. I am basically saying what @MajP already said. Cheers!

 

[arnelgp]

use the Link Master / Child Fields of the subform property so you don't need any code.

 

[mtagliaferri]

Thanks @arnelgp @theDBguy @MajP for the various solutions, moving on with the project and I am sure I will be back asking more advise!!!

 

[MajP]

IMO linking to the date makes zero sense. If you are considering that then simply delete orderdate from your table. Why have a planned and orderdate if they mean the same thing. By linking it you are saying they are one in the same.