Code not performing

[gheyman]

This code is not doing anything. I am trying to identify Fields that the user must enter data by making the field border red is the field is Null or "".

Private Sub Form_Activate()
       
    'Check to see if PID is set to "Select-A-PID"
    If Me.RFPMgr_ID = 1 Then
        DoCmd.OpenForm "frmRFPManagerPIDSelection", acNormal
    If Me.BidNoBid = Null Or Me.BidNoBid = "" Then
        Me.BidNoBid.BorderColor = vbRed
    If Me.ChargeNumber = Null Or Me.ChargeNumber = "" Then
        Me.ChargeNumber.BorderColor = vbRed
    If Me.ProposalDescription = Null Or Me.ProposalDescription = "" Then
        Me.ProposalDescription.BorderColor = vbRed
    If Me.Program = Null Or Me.Program = "" Then
        Me.Program.BorderColor = vbRed
    If Me.Commodity = Null Or Me.Commodity = "" Then
        Me.Commodity.BorderColor = vbRed
    If Me.Customer = Null Or Me.Customer = "" Then
        Me.Customer.BorderColor = vbRed
    If Me.RFPReceipt = Null Or Me.RFPReceipt = "" Then
        Me.RFPReceipt.BorderColor = vbRed
    If Me.RFPReceipt = Null Or Me.RFPReceipt = "" Then
        Me.RFPReceipt.BorderColor = vbRed
    If Me.AgreedResponseDate = Null Or Me.AgreedResponseDate = "" Then
        Me.AgreedResponseDate.BorderColor = vbRed
    If Me.ExtendedResponseDate = Null Or Me.ExtendedResponseDate = "" Then
        Me.ExtendedResponseDate.BorderColor = vbRed
    If Me.RFP_StatusX = Null Or Me.RFP_StatusX = "" Then
        Me.RFP_StatusX.BorderColor = vbRed
    If Me.RFP_StageX = Null Or Me.RFP_StageX = "" Then
        Me.RFP_StageX.BorderColor = vbRed
    If Me.Solicitation_TypeX = Null Or Me.Solicitation_TypeX = "" Then
        Me.Solicitation_TypeX.BorderColor = vbRed
    If Me.Bid_TypeX = Null Or Me.Bid_TypeX = "" Then
        Me.Bid_TypeX.BorderColor = vbRed
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
    Me.Refresh
    
End Sub

But nothing is happening.

 

[Joe4]

OK, access treats Nulls different in different spaces (Queries, Forms, etc), and I always confuse the different ways it needs to be written.
So I went back and did some testing, and the way you are checking for Null values is not valid. It needs to be written in this manner:

If [COLOR=#ff0000]IsNull(Me.BidNoBid)[/COLOR] Or Me.BidNoBid = "" Then

 

[Joe4]

Going to IsNull() = Tue helped for some of the fields, but its not working on fields that are Combo Boxes.

It should work on ComboBoxes too. Just make sure that you haven't set the ComboBox equal to a default value (so it isn't actually Null).

By the way, the =TRUE part is not necessary. It is implied.

 

[gheyman]

Ok The IsNull fixed somethings, where I have a Combo Box the default was set to "0" which explains why the IsNull and "" wasn't working.

Thanks for all the help

 

[stumac]

Rather than checking for it being null and for "" or 0 why not use


Where it defaults to 0

IF nz([COLOR=#ff0000]Me.BidNoBid[/COLOR],0) = 0

or

Where is it blank

IF nz([COLOR=#ff0000]Me.BidNoBid[/COLOR],"") = ""

 

[xenou]

"" is the default so we can also add:

If Nz(Me.BidNoBid) = ""

 

[xenou]

Adding an fyi:

Note that as a general rule in SQL you never use the construction x = null.
Null is never equal to anything, not even Null.

This extends to vba code and most programming languages as well, where also Null is not equal to anything and if you use the x = Null construct you will get bad results.

That construction is "falsey" though. Since it's never true, it's usually treated as false (in SQL, and I guess by extension in some or most programming languages that have nulls).

 

[Micron]

Whenever you find yourself doing something so repetitive as this, look for a better way. If you put the public function below in a standard module, not only can you really condense your code, you'll be able to use it anywhere in your project to test for Null or empty strings. Just pass the name of the control to it and the function returns True or False. What this code won't do is handle where you have default values, which would require a work around, though removing the default is the best approach.

Making use of the control Tag property is what really powers this (I used Reqd as an example - no quotes in the property sheet value). Creating a variable to hold the control (ctl) also eliminates a whole lot of Me.SomeControlName references. Note: I am using the ControlType property values from memory.

Probably in the form Current event, or perhaps on a button that attempts to save the record:

Event Sub Name Goes Here()
Dim ctl as Control

For each ctl in Me.Controls
  If (ctl.ControlType = acTextbox Or ctl.ControlType = acCombobox) And ctl.Tag = "Reqd" Then
    If IsNullEmpty(ctl) Then ctl.BorderColor = vbRed
  End If
Next

End Sub

**************************

Public Function IsNullEmpty(ctl As Control) As Boolean
IsNullEmpty = False
If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
End Function

See how concise that is?