none
On Form, saving calculated field (max date) from subform to underlying table on main form RRS feed

  • Question

  • I have a form titled MainFrm with a subform titled SubFrm.  On Subfrm, there are multiple rows of data, each row with a field titled DueDate.

    On MainFrm, I have a field titled MaxDueDate, from which I want the db to find the maximum date from the rows of data in the underlying subform (Subfrm).  I would then like the MaxDueDate field to be "pushed" to the underlying table (tblMain) after update.

    I am struggling with developing the VBA code to do this.  Any help would be greatly appreciate.  Thank you.

    Wednesday, November 1, 2017 2:05 PM

All replies

  • Hi,

    I would usually advice against saving the max due date because it is a "calculated" value. To grab the max due date value in the subform, you could try adding an unbound control to the footer of the subform with a Control Source of something like: =Max([DueDate])

    To display this value on the main form, you can add an unbound textbox with a Control Source of something like: =[SubFrm].[Form]![TextboxNameWithMaxControlSourceHere]

    Hope this helps get you started...

    Wednesday, November 1, 2017 3:28 PM
  • Thank you very much for the reply.  That does work... the max date is shown on the main form.  However, it does not update the field in the underlying table.  Admittedly I am new to this and have read that queries are recommended rather than the method I am trying to perform now.  In the future I will try to redesign the form to use a query but am trying to develop a quick resolution.

    Do I need to have to write code so the field is updated in the table?  Again, thanks!

    Wednesday, November 1, 2017 3:36 PM
  • Hi,

    Yes, you'll have to add code to save the value to the table. But as I have tried to say it before, it is not really recommended. Where do you need to see this value again? Wherever it is, you can always just look it up or recalculate it again.

    Just my 2 cents...

    If you must save it to the table, you can use code such as:

    Me.TableField = Me.TextboxName

    Just figure out which event you want to use to execute it.

    Hope it helps...

    Wednesday, November 1, 2017 4:09 PM
  • Hi Spartan Rage,

    in your last post you had mentioned that suggestion given by .theDBguy worked for you.

    I suggest you to mark the suggestion given by the .theDBguy as an answer.

    so that we can close this thread.

    this thread is still open and it will remains open until you mark the answer.

    so try to help us to close this thread by marking the answer.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 9, 2017 9:09 AM
    Moderator