Updating Field Related to a Subform

[smdemo]

Hi everyone, I'm a little stuck here and hope someone out there can help me out on this one.

I have a subform that has dates when an item is closed. Currently the main form has an overall case closed date as well that does not allow a date to be entered until all dates on the subform have been entered. What I am trying to do instead is make the main case closed date autopopulate with the max close date in the subform but remain null if any of the dates in the subform are null.

Thanks everyone, you have all been such a great help in the past.

 

[PHV]

an overall case closed date as well that does not allow a date to be entered until all dates on the subform have been entered
How did you that ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smdemo]

I'll be as clear as I can so we don't have to go back and forth so this is probably overkill, but here goes.

To start, the subform is viewed in datasheet view on the main form. In the form footer of subform, I count the records in the subform with =Count([Policy]) and count the records with a close date with =Count([FlagClsDt]).

I then bring those values to the main form in text boxes that are not visible and have a third text box that is not visibile to test if those two are equal. If equal, it returns a value of "True" and "False" if not.

Lastly, on the overall closed date I have the following code on the OnChange event.
If Me.ClsDtTest.Value = "False" Then
MsgBox "Not all flags have been closed.", vbInformation
Me.Case_Close_Date.Value = Me.Case_Close_Date.OldValue
End If

If not all the dates in the subform have been entered, it won't allow a date in the main form.

I am looking for this to free up time on data entry, not much I know. The main reason for it though is because I had a case where someone entered all the dates in the subform, then entered the close date for the overall case, then removed a date on the subform and this of course leaves the overall case closed date. With the automation, this would solve that, I'm just not sure how do this part of it.

Thanks

 

[PHV]

You may use the Max aggregate function in the footer.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smdemo]

PHV,

Thanks for the help. I actually tried that before posting and ran into the problem of I didn't want the maximum if not all the dates were completed in the subform. I got around that by using the T/F test to only return a date to the field if the test was true that all dates were completed.

Now my next problem...The field that I passed to the form has the control source of =IIf([ClsDtTest]="True",[ResolutionGrid subform].Form!MaxFlagClsDt,"") and it updates as dates change within the subform before the subform loses focus. I can't figure out how to get the field linked to the table to update at the same time though. I do have it updating from the OnExit of the subform but I need it to update as fields are changed in the subform as well. This is probably the most basic part of it all and I just can't get it. Any ideas here?

Thanks again for your help.

 

[TheAceMan1]

How are ya smdemo . . .

[blue]What I am trying to do instead is make the main case closed date [purple]autopopulate with the max close date in the subform but remain null if any of the dates in the subform are null.[/purple][/blue]

This is [purple]ambiguous![/purple] . . .
You can leave date updates in the subform as required, or, update them all with max date! As you've presented things, its one or the other anyway!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[smdemo]

Hi Aceman, doing ok, just puzzled by this.

I'm not sure I follow what you are saying. I'll try to clarify what I am doing. I have a closed date that currently needs to be entered on the main form. I then have a subform with a varying amount of records and a field there is also a closed date but those dates will vary and not all be closed at once. However, when all those dates are closed I would like the date closed in the main form to populate with the max close date in the subform, but remain null until all closed in the subform.

I do have this working on the OnExit event of the subform. My problem is I need it to update as the focus is changed inside the subform. I know users will be closing the record before leaving the subform and that will leave me open for inaccurate data.

If you need other info let me know.

Thanks for the help!

 

[smdemo]

I'm almost there now. I've set an AfterUpdate macro to set the value of the max close date that I have calculated in the footer of the subreport.

Now the problem is that the macro is running before the fields in the footer are calculated and its bringing the max value of the previous update. I'm not able to figure out a way to correct this now.

Any suggestions here? I am getting closer anyway.

 

[TheAceMan1]

smdemo . . .

Sorry to get back so late. Its hard to get in a post from work.

In any case perform the following:
[ol][li]Disable or remove any current code you have for updating [blue]closed date[/blue] on the [blue]mainform[/blue] (we can't afford any interaction here!).[/li]
[li]Copy/paste the following routine to the [blue]code module of the subform[/blue] ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Private Sub UpDateMaxdate()
   Dim rst As DAO.Recordset, fld As DAO.Field, ctl As Control
   Dim maxDate As Date, flg As Boolean

   Set rst = Me.RecordsetClone
   Set fld = rst.Fields("[purple][b]subFormFieldName[/b][/purple]")
   Set ctl = Forms![purple][b]MainFormName[/b][/purple]![[purple][b]MainForm Closed Date[/b][/purple]]

   If Not rst.EOF Then
      Do Until rst.EOF
         If Trim(fld & "") <> "" Then
            If fld > maxDate Then maxDate = fld
         Else
            flg = True
            Exit Do
         End If

         rst.MoveNext
      Loop

      If Not flg Then
         ctl = maxDate
      Else
         ctl = Null
      End If
   Else
      MsgBox "No Records!"
   End If

   Set ctl = Nothing
   Set fld = Nothing
   Set rst = Nothing

End Sub[/blue]

[/li]
[li][blue]Thats It![/blue][/li][/ol]
[blue]Give it a whirl and let me know . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[smdemo]

Hi AceMan, Thanks for giving it this much effort. I've put the code in on the AfterUpdate of the CloseDate field on the subform and I am getting nothing.

Any idea what is happening? So there's no way to just refresh the parent either?

 

[TheAceMan1]

smdemo . . .

I have this working in a simulation so lets see . . .

[blue] . . . when all those dates are closed I would like the date closed in the main form to populate with the max close date in the subform, but [purple]remain null until all closed in the subform.[/purple][/blue]

The code aborts updating ClosedDate on the first detection of a null. There's no indications of this. [blue]If there were no nulls you'd see an update as prescribed![/blue] There's no update unless all are not null. So perhaps its that single null that makes it look like nothings happening!

To test this you can simply set all date! I allowed the pass thru for smooth ops. If you need prompting of null this can be done in the right spot.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886