Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010

    Unanswered: If IsNull with 2 arguments in Form VBA

    I am using a locked subform in datasheet view for users to view information. There is no data entry done here and it is viewed this way for similarity sake (it looks the same as when the users are data entry)

    Subform is linked to mainform on Account Name.
    There are 2 optional "notes" fields in this form - STATNOTES and RNOTES.

    1.) Because the subform can have more than 1 record showing for each Account Name, I first need a VBA that can return true if all of the Account Name records are null on these text boxes; false if not. I want to hide the column altogether if there are no STATNOTES or RNOTES for this account.

    2.) I am drawing a blank for how to write Is Null for 2 different controls?
    What I've done is put each one under a different event like this:

    Private Sub Form_Current()

    If IsNull(Me![STATNOTES]) = True Then
    Me![STATNOTES].ColumnHidden = True
    Me![STATNOTES].ColumnHidden = False
    End If

    End Sub

    Private Sub Form_Load()

    If IsNull(Me![RNOTES]) = True Then
    Me![RNOTES].ColumnHidden = True
    Me![RNOTES].ColumnHidden = False
    End If
    End Sub

    Although this can work the way it is written, the results are inconsistent and not exactly correct. I am looking for help on 2 things -

    What VBA can I use to check each of the subforms ID's for this account to see if all of the STATNOTES and RNOTES are null?

    What is the proper way to write an IsNull for all textbox1 STATNOTES AND all textbox2 RNOTES so that each one is hidden if true

    thanks in advance!

  2. #2
    Join Date
    Apr 2011
    Not sure I quite understand your problem, but here are some obervations when using IF statements and IsNull function.
    You don't need to write
    If IsNull(Me![STATNOTES]) = True Then
    you can write
    If IsNull(Me![STATNOTES]) Then
    You can also make assignments in the form
    Me![STATNOTES].ColumnHidden = IsNull(Me![STATNOTES])
    You can use AND
    If IsNull(Me![STATNOTES]) AND IsNull(Me![RNOTES]) then

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts