Solved Calculated field on form doesn't update (1 Viewer)

mib1019

Member
Local time
Today, 08:51
Joined
Jun 19, 2020
Messages
88
Hello all,
I need to revisit a previous thread that mostly resolved my problem.

I have this popup form with subform that user enters dates into.
1595881029613.png

I have entered a series of dates in the subform. The Dates box on the main form is calculated ... =DatesToTextBox(), code as follows:

Code:
Public Function DatesToTextBox() As String 'returns string value of dates in recordset, comma separated, formatted 'm/d'
    Dim v As Variant
    Dim s As String
    '
    With Me!subfrmIODates.Form.RecordsetClone
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            v = .Fields("Air_Date").value
            If Not IsNull(v) Then
                v = CDate(v)
                s = s & Format(v, "m/d") & ", "
            End If
            .MoveNext
        Wend
    End With
    If Len(s) > 0 Then s = Left(s, Len(s) - 2)
    DatesToTextBox = s
   
End Function

The subform's AfterUPdate property calls for a requery of the textbox on the main form, i.e. calculate the dat

The problem is that the textbox doesn't update. I put the CalcDates button on the main form, temporarily, to force the update, but it doesn't happen.

If I close and reopen the form, the textbox is updated to reflect the dates. I can also delete lines of dates and the textbox updates for what was removed.

What am I doing wrong here?

Thanks in advance for any help with this.
MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:51
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure I follow. Which Textboxes are you trying to update? The image you posted doesn't seem to show where the result of the code you posted is supposed to go. Can you please explain more? Thanks.
 

mib1019

Member
Local time
Today, 08:51
Joined
Jun 19, 2020
Messages
88
Hi. Not sure I follow. Which Textboxes are you trying to update? The image you posted doesn't seem to show where the result of the code you posted is supposed to go. Can you please explain more? Thanks.

The box under the Label 'Dates' updates to the values in the subform records. Its control source is =DatesToTextbox. The box would display 11/30, 12/2, 12/4, 12/7, 12/9, 12/11, returned from the function.

Thanks!
MIB1019
 

moke123

AWF VIP
Local time
Today, 10:51
Joined
Jan 11, 2013
Messages
3,849
in the afterupdate of subform try - Parent.yourtextboxname.recalc
 

mib1019

Member
Local time
Today, 08:51
Joined
Jun 19, 2020
Messages
88
Recalc works, but applies to all calculated controls on the form... i.e. me.recalc. It can be direct it to parent form though, so it accomplished what I needed. Had to add a command to move the cursor to the next record in the subform.

Thanks for the advice! Still wondering why requery didn't work.

MIB1019
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:51
Joined
May 7, 2009
Messages
19,169
Use the subform's Current event to set the parents textbox. But first remove the Control source of the textbox.
Code:
Private form_current()
On error resume next
Me.parent!textboxname=DatesToTextbox()
End sub
 

mib1019

Member
Local time
Today, 08:51
Joined
Jun 19, 2020
Messages
88
arnelgp, for some reason, I get a function not defined error on calling it from the current event on the child form. The public function is in the code for the main (parent) form.

Doing a recalc on the parent form in the afterupdate event of the air_date field on the subform works well though.

MIB1019
 

mib1019

Member
Local time
Today, 08:51
Joined
Jun 19, 2020
Messages
88
Probably because the subform doesnt fire afterupdate until record is saved. Try explicitly saving - me.dirty = false


there is no underlying query to re-query. Your using a function.

Duh, that makes sense :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:51
Joined
May 7, 2009
Messages
19,169
I change the function a little bit and put it on the Subform.
I called it on the subform's Current and AfterDelConfirm events.
 

Attachments

  • ioDate.zip
    59.8 KB · Views: 140

Users who are viewing this thread

Top Bottom