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

[Sharky II]

Hi there

I have the following scenario - a form which is basically an 'invoice' - and this contains a subform with one or more items in it, that belong to this invoice. Each of these sub-form items has a 'ItemClearDate', which is marked manually by the user when each of the items is 'cleared' by the finance department.

The main form has a field from the invoice table called 'AllItemsClearedDate'. This field should be filled in with the date when ALL items in the subform are marked with a date, and as such are all clear - or when all subform 'ItemClearDates' are NOT null.

This would also populate the AllItemsClearedDate field in the 'Invoice' table, with a date.

Can someone give me some tips on how to do this?

It's been a while since i've done any VBA, and would appreciate some help.

Many thanks!

Eddie

 

[RuralGuy]

I would create the procedure you need and call it from the AfterUpdate event of the 'ItemClearDate' control.

 

[Sharky II]

Hey, thanks for the reply. That's the bit i'm comfortable with, it's the procedure writing i can't do!

Sorry to ask but is it possible to spoon feed me/point me in the right direction a bit more, i'm very rusty when it comes to my VBA! Psuedocode is something like:

If ALL Me!ItemOracleClearDate (from subform) NOT null then

OracleClearDate (on main form) = Date()

Thanks for any advice you can give... i'm reading up on VBA and looking at some old projects i created - it's coming back to me slowly...

 

[RuralGuy]

Give me a bit and I'll work on the procedure. I'm assuming a Continuous Form View for the SubForm.

 

[Sharky II]

Thank you so much - yes, it's a continuous form for the subform! Here's the form, for clarity:

 

[RuralGuy]

Here's a function that you can put in the SubForm and invoke in the AfterUpdate event of the ItemOracleClearDate control. It is a Function so you do not want to use call, just use the Function name to invoke it. I guess you could put it right in the AfterUpdate event but as a separate Function you could invoke it from anywhere.

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

   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
            End If
            .MoveNext
         Loop
      End If
   End With

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

EndFunction:
   Exit Function

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

End Function

This does want you to have the DAO library referenced.

 

[Sharky II]

Hey there, i really appreciate your help.

But for some reason i can't get it work - nothing happens. How do i call a function correctly?

I thought that if i put the code into the subform, it should show in the expression builder, and i could choose it. But it doesn't show there.

Or if i just copy the code and put it inside this:

Private Sub ItemOracleClearDate_AfterUpdate()
<here>
End Sub

As i used to do.

I'm new to Access 2007 - last time i was working heavily in Access, i was using 2000 and 2003. Perhaps it's to do with the DAO stuff?

I just checked tools -> references in my visual basic editor and obviously Microsoft DAO 3.6 Object Library is unselected, and i get "Name conflicts with existing module, project, or object library" error when i try to select it. I'll do some reading to figure out the consequences of this. Edit: I unchecked ADO and selected DAO and it didn't seem to make any difference...

Sorry if i'm being dim, but why would nothing happen? I don't get any error messages and the field names in your code are all correct. Perhaps i am calling/using the function incorrectly?

Many thanks

 

[RuralGuy]

All you should need is:

Private Sub ItemOracleClearDate_AfterUpdate()
   UpdateParent
End Sub

Is your db is a trusted location? http://www.btabdevelopment.com/ts/default.aspx?PageId=13

 

[Sharky II]

All you should need is:

Private Sub ItemOracleClearDate_AfterUpdate()
   UpdateParent
End Sub

Is your db is a trusted location? http://www.btabdevelopment.com/ts/default.aspx?PageId=13

Indeed, i've entered the code as you tried it, and yes my 'desktop' (which is where the db is) is a trusted location. Weird!?

 

[RuralGuy]

Did you put in a breakpoint and single step the code? How about a MsgBox just to verify the code is running? You *only* get an AfterUpdate event in a control when the value has been changed.

 

[Sharky II]

Thank you RG, it is now working! I think it was just a case of saving everything and re-loading the form in question. Perhaps i did something incorrectly the first time (first few times?) around.

Thank you again, you have been a great help!

Lastly, do you have any suggestions on how to modify this so that it always shows the 'last' date? i.e., when a user adds an item which completes the list, then deletes that date... perhaps add something to the 'on refresh' button on the form?

Alternatively, i can simply leave the calculated date as 'editable' so that if the user deletes one of the items, they can manually change the date to the correct date. It's not the slickest solution, but i think this is the easiest!

Cheers!

Eddie

 

[RuralGuy]

I'm not sure what you mean by 'last' date but the function I provided will *not* clear the MainForm so when all of the records are complete the MainForm gets set and would need to be cleared some other way. You could also test for a date in the mainForm and *not* update the control if it already has a value if desired.

 

[Sharky II]

Hey RuralGuy, just to pick your brains again.

I am using this on the AfterUpdate event and have a few users complaining.

Basically, if you enter a date then 'move on' with another record or entering another field, the date in the 'Master' form gets updated corrently. But if you delete this date and change it to another date - or delete it completely, the 'old date' remains and doesn't get updated.

Is there a way to remedy this?

Can anyone think of a way to do this - perhaps use the 'On Change' event?

Thank you!

Eddie

 

[RuralGuy]

I don't think I follow. How are you changing or deleting the date?

 

[Sharky II]

Hey, thanks for replying!

So there's a date field in the subform (and the subform is continuous - but often a lot of them just have one item).

I got a user report telling me that sometimes there were dates in the subform but not in the main form. I figured out that what they must be doing is entering a date in the subform and then deleting it and entering another. If you do this, the date does not get updated.

I'm going to do some further testing now to figure out exactly what trips it up.

Thanks!

Edit: This feature doesn't seem to be working correctly at all, i must have changed something or implemented it incorrectly. It was working before, so i don't know what's gone wrong.

E.g.: if i simply create a new transaction, and enter a date in the past on the subform, it enters today's date in the date field on the parent form. I assume that the data entry guy has been deleting the date it manually because it's not correct - but i'm still playing! Here is the code i used on the 'After Update' event on the subform:

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

   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
            End If
            .MoveNext
         Loop
      End If
   End With

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

EndFunction:
   Exit Function

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

End Function

Private Sub ItemOracleClearDate_AfterUpdate()
   UpdateParent
End Sub

 

[RuralGuy]

It looks to me like it should update the parent under the circumstances you mentioned. You should get an AfterUpdate event on the control in both cases and certainly a BeforeUpdate event on the SubForm when you try and change records.

 

[Sharky II]

It's really weird - all my old records (i only have a few dummy records in my back end - i don't have their back end which is stored at the office) have the correct oracle date, but anyone i try today, it just puts in today's date!

 

[RuralGuy]

What date did you want in there?

 

[RuralGuy]

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.

 

[Sharky II]

Hi

I now realise that i want the date that's in the subform (if there is only one item in the subform) - if there is more than one item in the subform, the latest date out of the ones entered.

I.e. the OracleClearDate (parent form) = last ItemOracleClearDate (subform)

If there's more than one ItemOracleClearDate in the subform (multiple entries), than the last date from them.

Thank you - sorry for not being clear. I can see that i actually misunderstood the user requirement - i thought they wanted 'todays date' - which is what i asked for!!!

Sorry for that!

But also: if you delete the item in the subform date field, the one in the parent form is not updating correctly. In fact if you delete it completely from the subform - it remains in the parent form.

Cheers

Eddie