Dealing with 'if null' syntax when locking old records (VBA)

[Sharky II]

Hi guys!

I have the following code to set AllowEdits and Deletions to false if a date is older than 'AllowEditDate'. 'AllowEditDate' is a date in a table which has only one record and is opened by a form in 'edit' mode so that the old records can be locked by a main user.

However when this field is set to empty (i.e. the user wants none of the forms to be locked), i get an error.

Can anyone let me know how i should modify this so that i can achieve an 'if AllowEditDate is null, then do/lock nothing' kind of thing?

Sorry if i haven't been clear!

Very much appreciated!

Eddie

Old code:

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

I'm thinking something like this, but it doesn't work (invalid use of null on the line q = DLookup("AllowEditDate", "AllowEditsDateTable")):

Private Sub Form_Current()
Dim q As Date
q = DLookup("AllowEditDate", "AllowEditsDateTable")
If IsNull([AllowEditDate]) 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
End Sub

 

[vbaInet]

You didn't mention what error you get?

 

[Sharky II]

Sorry - i put in the last line that i'm getting "invalid use of null" on the line q = DLookup("AllowEditDate", "AllowEditsDateTable")):

I've modified my code to use variant so i can check if it's null and now have:

Private Sub Form_Current()
Dim q As Variant

If IsNull(DLookup("AllowEditDate", "AllowEditsDateTable")) Then
  Me.AllowEdits = True
  Me.AllowDeletions = True
Else
q = DLookup("AllowEditDate", "AllowEditsDateTable")
If q >= Me.FinanaceReportDate Then
  Me.AllowEdits = False
  Me.AllowDeletions = False
 Else
  Me.AllowEdits = True
  Me.AllowDeletions = True
 End If
End If
End Sub

Trying it now!

Thanks - hopefully you can see what i'm trying to do!

Eddie

 

[Sharky II]

I think i've cracked it! Can anyone see any potential issues with that code?

Thanks!

 

[vbaInet]

It means that Financereportdate is Null at the time of execution:

If q >= Nz(Me.FinanaceReportDate) Then

 

[vbaInet]

I think i've cracked it! Can anyone see any potential issues with that code?

Thanks!

* Use DLookup() once by saving it into a variable.
* Avoid declaring Variant type variables.

I hope that helps

 

[Sharky II]

Hey, thanks!

I see what you're saying about using DLookup only once, but how do i save it to a variable and do If "IsNull(DLookup("AllowEditDate", "AllowEditsDateTable")) Then" at the same time?

Thank you!

 

[vbaInet]

Hey, thanks!

I see what you're saying about using DLookup only once, but how do i save it to a variable and do If "IsNull(DLookup("AllowEditDate", "AllowEditsDateTable")) Then" at the same time?

Thank you!

There you go:

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
End Sub

 

[Sharky II]

Hey, thanks! I see how to do that now!

The only difference is that this seems to do the opposite - unlock the OLD records, and lock the new ones - but that is easily solved by changing the >= to a <=

Thank you again! This seems to have cracked it

Eddie

 

[vbaInet]

Happy to help!

 

[Sharky II]

Hey, i went to install the new front end at the office, and for some reason, this doesn't seem to work anymore!

The only thing i've changed is a line which calls a public function (Me.Child24.Form.UpdateParent) - can anyone see why this would not work?

Edit: it works after i REFESH the form manually. I guess it's something to do with the UpdateParent call?

The UpdateParent function updates a field on the main form based on some figures in the subform.

Private Sub Form_Current()

    Me.Child24.Form.UpdateParent
    
    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
End Sub

Can anyone help?

Thanks!

 

[vbaInet]

What happens when you comment out that new line?

 

[Sharky II]

Hey there! If i comment out the line, it works without having to refresh. I could post the code for the UpdateParent function if that helps.

I'm going to try putting the UpdateParent call at the bottom of the Current sub as well, just before the end.

Edit: even when i put it just before 'end sub', including at all means i have to refresh before it makes the form un-editable!

 

[vbaInet]

Naturally you don't update a parent from the child, it's the other way round. You shouldn't be saving values gotten from the the subform in the main form. The only time you get (i.e. not save) a value from a subform is when you need a Total (for example).

Let's see the function.

 

[Sharky II]

Here it is - it was created very kindly by one of the forum regular's here.

There is a field on the main form (called Transaction) called 'OracleClearDate'. There's a subform (TransactionItem/Child24), and each Transaction can have one or more TransactionItems.

There's a field on the continuous Subform called ItemOracleClearDate. The below function goes through all the ItemOracleClearDates and takes the 'last' date and makes the OracleClearDate - but only when all the TransactionItems have a ItemOracleClearDate filled in.

Thanks!

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
  Dim MyDate As Date

  AllCleared = True   '-- Initialize the flag

  '-- Examine all of the records
  If Me.Dirty Then Me.Dirty = False
  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
           Else
              If .ItemOracleClearDate > MyDate Then
                 '-- Capture the latest date
                 MyDate = .ItemOracleClearDate
              End If
           End If
           .MoveNext
        Loop
     End If
  End With

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

EndFunction:
  Exit Function

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

End Function

Called in the current form.

Private Sub Form_Current()
UpdateParent
End Sub

 

[vbaInet]

That whole code could have been done more effeciently, performing an update by executing an SQL statement in code.

In any case, I don't understand why you need to run that code in the On Current event.

 

[Sharky II]

Hi - well OK it could have been done more efficiently, but it works!

I actually had it in the On Current event to correct some errors that were inputted by users before i had this code. Now i have this code and have fixed those errors, i can take it out of the On Current event. So no problem there!

Do you have any feedback on the 1899 (or 00:00 when you click on the field) issue, for when there's no TransactionItem entered yet? Not a biggie, i guess!

Cheers

Eddie

 

[vbaInet]

Hi - well OK it could have been done more efficiently, but it works!

Why fix it if it ain't broken huh!

Do you have any feedback on the 1899 (or 00:00 when you click on the field) issue, for when there's no TransactionItem entered yet? Not a biggie, i guess!

Explain a bit more Eddie. What's the control you get this problem? What are the steps to reproduce the problem?

 

[Sharky II]

Well, actually it is a bit of a problem!

I was confused earlier - but i believe the problem lies with the code we were talking about earlier.

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!

I realise this isn't your code, but i'm sure RG is a busy guy so has't been able to come to the rescue in the other thread!

Many thanks!

 

[vbaInet]

Why not Format() the .ItemOracleClearDate date before passing it to the MyDate variable.