populate a Main form control from a control in its sub-form

[WCStarks]

1. I need to update a field, [Joined] in the main form, from a row in its sub-form.
2. I assume that creating a Recordset of the continuous sub-form, will allow me to find the target row to update from.
3. I assume that once a row is found in a recordset, a reference to rst.[Status_Date] will be for the variable in that found row.

I have built the following code for this purpose, but when I refresh the Main form, which also refreshes the sub-form, it doesn’t copy the date from the sub-form [Status_Date] into the [Joined] control in the parent form.

Since the Parent form can have anyone of several sub-forms active, I placed the code in the sub-form properties, so it only processes when the Membership sub-form is active.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset
Set rst = Me.Form.Recordset
rst.FindFirst "[Status] = 'Joined'"


If NoMatch = False Then
Parent.[Joined] = rst.[Status_Date]
Parent.Update
End If
rst.Close
End Sub

Apparently, I am missing something in my understanding of how this is supposed to be coded. Below are the two form in design mode.

[June7]

Why? Saving calculated data, especially aggregate calc, is not usually necessary and can even be bad as data can get 'out of sync'.

If code is behind the form used as subform, then don't use Form:
Me.Recordset

Also:
If rst.NoMatch = False Then

[kd2017]

Also, you placed your code in the BeforeUpdate event of the subform, that doesn't execute on a form refresh.
https://msdn.microsoft.com/en-us/vba...e-event-access

Maybe you can make the Control Source of the Joined textbox something like this =DLookUp("[Status_Date]", "MEMBERSHIP TABLE NAME HERE", "[Status]=""Joined"" AND [MEMBERSHIP TABLE'S FOREIGN KEY FIELD NAME HERE]=" & ID)
This way you don't need to code it.

If you need to code it I'd still keep it in the parent form's code, probably in the Form_Load() event.

[WCStarks]

Why? Saving calculated data, especially aggregate calc, is not usually necessary and can even be bad as data can get 'out of sync'.


If code is behind the form used as subform, then don't use Form:
Me.Recordset

Also:
If rst.NoMatch = False Then

The sub-form is the primary method for tracking various membership statuses. I created a redundant Joined date field in the main table to simplify display of the Joined date in various reports. Trying to manually sync the main form's redundant [Joined] date control from the sub-form, is awkward. So, I thought I would attempt to automate the sync.

I made the two changes indicated above and ran it again. It still doesn't copy the relevant sub-form [Status_Date] control into the Main [Joined] date control.

[kd2017]

I created a redundant Joined date field in the main table to simplify display of the Joined date in various reports. Trying to manually sync the main form's redundant [Joined] date control from the sub-form, is awkward. So, I thought I would attempt to automate the sync.

Redundant and awkward.
I think what June7 is trying to point out is that your approach is bad practice that can lead to bad data and is trying to steer you away from it.

You can use subqueries or dlookup to fetch this information.

[kd2017]

Also, is it possible for a member to have more than one record with the "Joined" status? If so your code would need to account for that to fetch the appropriate date you're looking for. If you decide to go the DLookup route I would use Elookup instead which can take an order clause. http://allenbrowne.com/ser-42.html

[WCStarks]

I tried the DLookUp as you have it written, but it still doesn't copy the date from the child table into the main form, when I refresh the main form. I also tried it without the concatenation as: ... = Me.[Member_ID]"), and that did no better. I don't understand the logic of the concatenation. It seems to me it should all be part of the string.

Private Sub Joined_AfterUpdate()
Me.[Joined] = DLookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and Members.[ID] =" & Me.[Member_ID])
End Sub

[WCStarks]

I suppose it would be remotely possible for there to be two Joined dates, although I don't have that situation now in the database. If there were, the one I would want would be the most current one.

[WCStarks]

So, you are saying it would be safer to include an ELookUp in all the reports which need to show the member Joined status?

[kd2017]

I tried the DLookUp as you have it written, but it still doesn't copy the date from the child table into the main form, when I refresh the main form. I also tried it without the concatenation as: ... = Me.[Member_ID]"), and that did no better. I don't understand the logic of the concatenation. It seems to me it should all be part of the string.

Private Sub Joined_AfterUpdate()
Me.[Joined] = DLookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and Members.[ID] =" & Me.[Member_ID])
End Sub

The code in the Joined_AfterUpdate event will only be called after the text in the textbox is changed by the user. It won't even be called when you cycle through the different records or refresh your form.


I wonder if your Members.[ID] and Me.[Member_ID] are not reversed? Should it be

=DLookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and [Member_ID] =" & ID)

?

Anyway, just to clarify it should be DLookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and [Foreign Key ID field on "subtable"] =" & [Primary Key ID Field on "main table"]) when called from the "main table".

And I would try it in the control source property of a textbox rather than in code. IE in your form's design view Right click on your Joined textbox and click "Form Properties", go to the "Data" tab, change the "Control Source" property to the DLookUp function. Don't forget the equal sign at the beginning.

[kd2017]

So, you are saying it would be safer to include an ELookUp in all the reports which need to show the member Joined status?

Or DLookUp, Probably. But only you can answer that as we don't know all the facts.

[WCStarks]

I guess I did have the references backwards. The Main form is called "Scateam", and its underlying table is "Members", whose primary ID is [ID].

So, [Joined] will no longer be a Members table field, but rather a virtual calculated Scateam.[Joined] form control. And all reports that need to show the members joined status date, will need to do a similar lookup.

I did as suggested and it works. I also modified the Team Roster report that displays The Joined date, with the same unmodified expression and it works too.

So, this issue is resolved. And, I guess this is better, because the data cannot get out of sync. That perspective helps a lot. Thank you both very much.

As you can see from my design view, I also have a "Resigned" field with the same issue. I copied this expression and modified the search criteria from "Joined" to "Resigned", and now that is fixed. I should probably use ELookUp instead DLookup, just in case.

However, I still have not learned how to use a form.Recordset to manipulate a sub-form. I have another situation where I need to update all occurrences of a control in a sub-form with a value in a control in the main form. I presume this is the proper use of a form.Recordset. Perhaps, I should revisit this issue in light of this new perspective. Perhaps, I can resolve it the same way, without needing to process the sub-form to duplicate a control value with what is in the main form.

I would still like to learn how to process a sub-form, for the sake of learning.

[kd2017]

Remember when you use the elookup function for sorting that the function will need a 4th argument.

You may want to be more specific with your questions about processing subforms.

[WCStarks]

I went in and ordered the Membership table by [Status_date] Descending. I am thinking that that will allow DLookUp to find the most recent Joined status, or the most recent of whatever status, without resorting to ELookUp, which, I understand is not a native function of Access.

[kd2017]

I don't believe a table sort order will have any effect on dlookup.

You are correct, elookup is not native. But it's still a useful function.