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

[RuralGuy]

First off, change the procedure to a Public Function. It then becomes a Method of the Form so: Me.SubFormControlName.FORM.UpdateParent will work. The SubFormControlName is the name of the Control that displays the SubForm, not the SubForm. They are often named the same bot it is not necessary.

 

[Sharky II]

Hey RG - i believe the main date function you gave me was public anyway? Or do you mean make Current Event in the main form public?

I've currently made the main form current event public, and am using:

Me.Child24.Form.UpdateParent

Which gives no error, but i just want to be sure that the logic is correct (i have no records to test this with - i will test it on the main system now)

Thanks!

Eddie

 

[Sharky II]

Seems to be working well!

 

[RuralGuy]

Excellent! The Current Event does not need to be Public, just the UpdateParent procedure.

 

[Sharky II]

Hey RG - the code works well, but i'm trying to fix an issue where:

If someone fills in the main form but doesn't enter an item in the 'subform' (e.g. they didn't finish entering the item details or the form is new) - then the Oracle Clear Date is 12/1899. If you click on the box, then the date changes to 00:00.

FYI the format of this oracle date fields on the form is set to mm/yyyy, but they contain real dates.

Once you enter a TransactionItem, with a valid OracleClearDate, it updates the field on the main form with the correct date. You can see what i mean in the attached screen grabs!

If you have any answers, i'm all ears as usual

Many thanks!

 

[RuralGuy]

I would try changing the later part of the UpdateParent so it reads like this:

   If AllCleared Then
      '-- All records have a valid date
     [COLOR="Red"] '-- Change the ForeColor of the Parent control to *black*
      Me.Parent.OracleClearDate.ForeColor = #000000[/COLOR]
      Me.Parent.OracleClearDate = MyDate
   Else
      '-- Clear any existing date
      [COLOR="Red"]'-- Make the value of the Parent control invisible by setting the ForeColor to match the CackColor
      Me.Parent.OracleClearDate.ForeColor = #FFFFFF[/COLOR]
      Me.Parent.OracleClearDate = Null
   End If

 

[Sharky II]

Hey RG - thanks once again. Any idea why i'm getting a syntax error on the line: Me.Parent.OracleClearDate.ForeColor = #000000

Changing them to the words 'black' and 'white' respectively didn't work. The bg colour is actually #F5F5F5, but that's neither here not there.

Any ideas? I'll keep trying

Eddie

P.S. - i appreciate you continuing effort very much!

 

[RuralGuy]

Just change it to ....ForeColor = 0
...and see what happens.

 

[Sharky II]

Just change it to ....ForeColor = 0
...and see what happens.

There's no 'error' now, same as when i had it as black/white, but it still shows 1899 on a new record (in normal black colour).

 

[RuralGuy]

We're calling the code from the Current event of the MainForm, right?

 

[Sharky II]

We're calling the code from the Current event of the MainForm, right?

Yes, here's my current for the main form:

Private Sub Form_Current()
    
    Dim q As Date
 
    q = Nz(DLookup("AllowEditDate", "AllowEditsDateTable"), "1/1/1900")
 
    If q = "1/1/1900" Then
        Me.AllowEdits = True
        Me.AllowDeletions = True
    Else
        If q >= Me.FinanaceReportDate Then
            Me.AllowEdits = False
            Me.AllowDeletions = False
        Else
            Me.AllowEdits = True
            Me.AllowDeletions = True
        End If
    End If

    Me.Child24.Form.UpdateParent

End Sub

HOWEVER, including the UpdateParent call in the main current means that that code to 'lock' the forms doesn't' work unless i refresh the form. Then it locks the form correctly (assuming it's older than the date that's 'looked up')...

 

[RuralGuy]

Have you single stepped to make sure the code is executing?

 

[Sharky II]

I thought that feature was just for macros. I'll try that now

Also, the call doesn't really need to be in the main current anymore - i went in and 'updated' the 500 records in the database by loading up the form with the call in the main form, and it updated all the 'erroneous' entries, so there aren't any entries where there is a record in the subform with a date, and the main form hasn't updated (which existed because i was using the older code).

Given that it 'breaks' the form locking code, i was hoping that it would be possible to fix the 1899 issue without having to put this call in the current of the main form.

Thanks!

 

[Sharky II]

I think it's 'fixed'! By accident, of course. I simply removed the line: Me.Child24.Form.UpdateParent from the main current method

I'll keep testing because i don't really understand why it's working at the moment, so i wouldn't be surprised if i'm missing something important.

But as far as i'm aware when i create a new entry, there is nothing in the OracleClearDate on the main form. When i enter an item, it updates. Also, all entries created before yesterday, are locked - as they should be, without refreshing. I'm using this code at the moment:

   If AllCleared Then
      '-- All records have a valid date
      '-- Change the ForeColor of the Parent control to *black*
      Me.Parent.OracleClearDate.ForeColor = black
      Me.Parent.OracleClearDate = MyDate
   Else
      '-- Clear any existing date
      '-- Make the value of the Parent control invisible by setting the ForeColor to match the CackColor
      Me.Parent.OracleClearDate.ForeColor = 0
      Me.Parent.OracleClearDate = Null
   End If

I might try changing it to the old code, just to see what happens.

Will post back in a sec!

 

[Sharky II]

Hi,

So, the problem arises when i have the call to update parent in the main form's current method.

Even when i use the old section of the code:

   If AllCleared Then
      '-- All records have a valid date
      Me.Parent.OracleClearDate = MyDate
   Else
      '-- Clear any existing date
      Me.Parent.OracleClearDate = Null
   End If

There is no problem. But if i include the Me.Child24.Form.UpdateParent in the main form current, the 1899 error occurs.

This isn't necessary now as i fixed the records with the problems, so i'll leave the updateparent call out, and all should be fine. I think i'll stick with the simple code i've pasted in this post, too. I can't see a need for the other forecolor 'hiding' code.

Thanks again!

Eddie

 

[RuralGuy]

Thanks for posting back.