Invoice cleared date set only when all subform items are marked as 'clear'

[Sharky II]

You may only want to call this function from the AfterUpdate event of the *date* field in the SubForm unless any change to a record should change the mainForm date.

Sure, that's what i'm doing currently - it's on the AfterUpdate of the date field in the subform

 

[RuralGuy]

You should be able to take care of the delete situation by altering this part of your function:

   If AllCleared Then
      '-- All records have a valid date
      Me.Parent.OracleClearDate = Date
   Else
      Me.Parent.OracleClearDate = Null
   End If

 

[Sharky II]

Here's a quick video (1.7mb) showing the behaviour currently! I can't attach it but you can view the .mov file by clicking here:

http://www.roughproductions.co.uk/access/acessproblems.mov

Or just go to http://www.roughproductions.co.uk/access/ and right click save as if you don't like it playing in your browser. Thanks!

 

[Sharky II]

You should be able to take care of the delete situation by altering this part of your function:

   If AllCleared Then
      '-- All records have a valid date
      Me.Parent.OracleClearDate = Date
   Else
      Me.Parent.OracleClearDate = Null
   End If

Thanks - i tried this code but if i delete the date in the subform, it remains in the parent form?

 

[RuralGuy]

I made some changes to the code. Give this a try:

Public Function UpdateParent()
'-- Update the Parent Form OracleCleardate
'-- ... IF all local ItemOracleClearDate fields have a valid date
   On Error GoTo ErrUpdateParent

   Dim AllCleared As Boolean
   [COLOR="Red"]Dim MyDate As Date[/COLOR]

   AllCleared = True   '-- Initialize the flag

   '-- Examine all of the records
   With Me.RecordsetClone
      If .RecordCount Then   '-- Only if records exist
         .MoveFirst       '-- Resets record pointer position
         Do While Not .EOF
            If Not IsDate(.ItemOracleClearDate) Then
               '-- Reset the flag
               AllCleared = False
            [COLOR="Red"]Else
               If .ItemOracleClearDate > MyDate Then
                  '-- Capture the latest date
                  MyDate = .ItemOracleClearDate
               End If[/COLOR]
            End If
            .MoveNext
         Loop
      End If
   End With

   If AllCleared Then
      '-- All records have a valid date
      Me.Parent.OracleClearDate = [COLOR="Red"]MyDate
   Else
      '-- Clear any existing date
      Me.Parent.OracleClearDate = Null
   [/COLOR]End If

EndFunction:
   Exit Function

ErrUpdateParent:
   MsgBox "Error No:    " & Err.Number & vbCr & _
          "Description: " & Err.Description
   Resume EndFunction

End Function

 

[Sharky II]

Wow - thank you! Trying this out now.

 

[RuralGuy]

Thanks - i tried this code but if i delete the date in the subform, it remains in the parent form?

What is the FieldType of the MainForm control?

 

[Sharky II]

It's just a text box with format q/yyyy?

I'm getting some odd results with your new code - please check out this new video - it's now one step 'behind'!

http://www.roughproductions.co.uk/access/accessproblem2.mov

 

[RuralGuy]

It's just a text box with format q/yyyy?

I'm getting some odd results with your new code - please check out this new video - it's now one step 'behind'!

http://www.roughproductions.co.uk/access/accessproblem2.mov

The control is not bound to a field in the Recordset of the Parent Form? BTW, I do not have Quick Time so can not play the videos.

 

[Sharky II]

Sorry, yes, the field is bound to a field in the main form/table called 'OracleClearDate'

I will upload the last video to youtube so you can see it - it's very short/small, brb!

 

[RuralGuy]

Is the 'OracleClearDate' field a DateTime field? I'm downloading QuickTime now but it is large.

 

[Sharky II]

Here's a youtube link: http://www.youtube.com/watch?v=UrGqK-aEoVk !!

 

[Sharky II]

Is the 'OracleClearDate' field a DateTime field? I'm downloading QuickTime now but it is large.

In the table - yes it is!

Don't bother with quicktime, you can view on youtube: http://www.youtube.com/watch?v=UrGqK-aEoVk

The last video came up garbled, so i uploaded a new HD one. HD will be ready in a couple of minutes, when youtube has processed it. Hopefully it's clear now

Thank you for all your help!

 

[RuralGuy]

I see the YouTube video. The date has not been saved yet in the Recordset of the SubForm in the AfterUpdate event of the date control. Let's add a:
'-- Examine all of the records
If Me.Dirty Then Me.Dirty = False
...and see if that corrects the issue

 

[Sharky II]

I see it!

Yes, i put in the If Me.Dirty Then Me.Dirty = False code and it works beautifully, i can't trip it up! I can add more than one and it goes to the 'later' date, i can delete and the main form updates as expected. Excellent!

I think you've cracked it. Thank you so much!

 

[RuralGuy]

Great! Where there's a will...there's a way.

 

[Sharky II]

I'm back

(sorry RG)

But is there a way of refreshing this 'On delete' also? When i delete one of the records in the subform, it doesn't update the date with the latest one. Is there a simple 'OnDelete' where we can refresh the code so that it calculates again?

It would be great if perhaps this could be refreshed on load so even old one's which (currently) have a date in the subform but not in the main form could be refreshed on load? I am going to try to see if i can simply call that function on page reload! The only catch is that i don't have any of the 'faulty' records (with no date in main form) to test this bit out!

I'm gonna have a quick play to see if there's anything i can figure out!

Thanks

 

[Sharky II]

Think i sorted it! I converted the delete button (on the subform) from using a macro to VBA, then called UpdateParent after it's deleted.

I can't get UpdateParent to be called on loading the form (which would 'fix' the records which got jumbled up last time) - i get an error saying 'You can't assign a value to this object' - but to be honest, i don't really mind. They can just fix the ones with the problems manually, and then all the future ones should work perfectly.

Thanks!

 

[RuralGuy]

Try invoking it in the Current Event of the MainForm.

 

[Sharky II]

Hey - but how do i call a function which is defined in a subform, from the main form?

I already have a bit of code going on in the Current Event on the main form.

I tried Me.Child24.UpdateParent but it didn't work!

Thanks!