update values in a subform based on a value in a main form

[Emma]

Hello again!

I am trying to get the value entered in a field in a main form entered into a corresponding field in a subform. So, for example on my main form I have a field "Phase" and on my subform I also have a field "Phase2". When the user updates the "phase" field in the main form, this value should be copied in all the "phase2" field of related records on the AfterUpdate event. (The user can then change the Phase 2 info if they need to, as sometimes this will not always be the same as the Phase on the main form)

I can get it to work for the first record in the sub form, but the other records dont change. my code is (at the moment ..)

Private Sub subphase_AfterUpdate()
Me![SBF_FILL]!phase2 = Me!phase
End Sub

but I am confused as to how to get Access to update all the records linked to the record in the main form to update. I am sure this is possible, I just dont know how.
I am very slowly learning VBA, but, it is slow!!

Many thanks for any comments!

Emma

 

[simongallop]

What are the other records? Are you posting the same info from a text box to lots of textboxes or are you trying to copy the corresponding records across?

 

[chrismcbride]

Assuming that it is appropriate to change all the Detail record fields after they have been created (thereby perhaps overwriting values that the user intended to be different than the Form value), it is a simple thing to use the .edit and .update properties of the Recordset object.
1. In the AfterUpdate of the Form field "Phase", create a recordset containing all the Detail records that match you current Master Record.
2. set up a loop to go thru the Detail records
3. use a With structure to update the recordset
With rst
.edit
!Phase2 = me.Phase
.update
end with
4. refresh your subform with
There is lots of stuff in the Help files on the specifics of this kind of thing, however I will send an example if you want. Just send an e-mail.
Good Luck
Chris

 

[Richie]

How are the tables linked?

 

[Emma]

Thanks for both your replies.

Firstly, the tables are linked via a one to many relationship - so Table 1 (groups) with field Phase is the one side, with Table 2 (subgroups) with Phase2 field, as the many side. The two fields are linked on a common field "GROUP"

I dont want to copy the record from the Group table, only the value in the field Phase which is recorded in Table1 to the field Phase2 in Table 2 for every record. S

So for example, in Table 1 I have a GROUP "X" with Phase recorded as 1A, and there are three records in Table 2 which are part of GROUP "X", and I want the value 1A entered into the Phase2 field of those records.

However, I also need the user to be able to change Phases in any record that is part of GROUP X, as in some instances there are occassions when the Phase may differ to the main GROUP. So, to return to my example, one of my records in Table2, may actually be Phase 1B, so I need the user to be able to update the subgroups with the Group Phases and then alter if necessary.

Confused? I know this sounds completely mad, but it is the nature of the data (archaeological) which I am dealing with!! (to me it makes perfect sense!!). The data entry therefore needs to be flexible, but also I want to minimise the users data entry as much as possible. By updating the Phase information in this way enables the user to choose whether their subgroups are the same phase as the main group - or manually change those records that are different!

Many thanks for all your help and time with this!

Emma

 

[Talismanic]

Emma, I think I have the same problem as you (if I understand you correctly). Some one gave me this solution, I havent had time to try it yet but I thought I would pass it on to you just in case it is what you are looking for also.

Me!NameOfSubformControl.form.recordset.addnew

Hope that helps!

 

[Emma]

Thanks Talismanic, though (and this is probably going to be a really stupid question, and really show my ignorance!!), where do I put that line?!? Is it on the AfterUpdate property of the Phase in Table1 (my main group table). I am sorry to ask what is probably a really obvious question, but I would really like to see if it works!

Emma

 

[Talismanic]

It is not a stupid question, deciding where to put it really depends on your app. In my case I needed it for the On_Exit event of my last control in the main form. So that I could force the subform to start a new record when the main form did.