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.

 

[mole999]

if you tested for NULL without testing " " does that make a difference it maybe that both could be true so the OR will select a logical answer and no change

 

[gheyman]

I didn't know it could both be true. How can a field be both Null and Blank ""?

Any suggestions?

 

[Joe4]

Did you really mean to nest all those IF statements?
When you do that, the first time you encounter a check that returns FALSE, it will exit out and nothing else under that will be checked.
If that is not what you want to happen, then you need to un-nest them, by placing the END IF line before the next IF THEN line, i.e.

If Me.BidNoBid = Null Or Me.BidNoBid = "" Then
    Me.BidNoBid.BorderColor = vbRed
End If
If...

.
or, since each one only has one line under the IF, you can get rid of the END IF line completely if you place it all on one line, i.e.

If Me.BidNoBid = Null Or Me.BidNoBid = "" Then Me.BidNoBid.BorderColor = vbRed
If...

.

 

[gheyman]

That's how I originally had it and went to nested to see if it would make any difference - it didn't. I know its running through the code because if I take out an End if, it errors. So its running, its just not producing.

Any suggestions on how to do what I want another way. I just want to identify fields that require data when they are not filled in.

 

[Joe4]

That's how I originally had it and went to nested to see if it would make any difference - it didn't. I know its running through the code because if I take out an End if, it errors. So its running, its just not producing.

I am not saying it is not running through the code, I am saying it may not be running through all the steps that you think it is.
Removing and END IF will cause compile errors, which really doesn't prove anything (other than the fact that you have invalid code).

The question that we need you to answer is should these be Nested or not (meaning should each succeeding IF be dependent on the previous one)?

Here is an example of nested IF statements:

If check1 Then
    'do something
    If check2 Then
        'do something
        If check3 Then
            'do something
        End Id
    End If
End If

If check1 is FALSE, without an ELSE statement, it will exit the Sub there and NEVER look at check2 and check3.
So check2 and check3 are dependent upon the condition before them being true (that is what a Nested IF does).

If you want all the conditions checked, regardless of whether the prior ones return TRUE or FALSE (so all checks are independent of each other), they cannot be nested. They need to be set up like this:

If check1 Then
    'do something
End If
If check2 Then
    'do something
End If
If check3 Then
    'do something
End If

Make sense?

So, the question is with all the things you are checking, should they be dependent on the IF statement prior to them, or should they all be independent of each other?

 

[gheyman]

They are all independent and it doesn't matter what the previous one is.

But unless I still misunderstand, I tried doing each with the end If before the next one. That's the way I had it the first time.

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

 

[Joe4]

When should this be running? You may not be using the correct event.
I believe that Form_Activate only fires when a form is first opened. If you want it to change as you scroll through records, I think you want to use Current instead of Activate.

 

[gheyman]

Thanks, Joe. Tried the code above in On Current. No change

I tried

Private Sub ChargeNumber_DblClick(Cancel As Integer)
    If Me.ChargeNumber = "123" Then
        Me.ChargeNumber.BorderColor = vbRed
    End If
End Sub

just to see what happens. If I have 123 in the field and double click it worked. So I assume its the Null Or "" because the double click didn't work when I had that in the code instead of the =123

 

[gheyman]

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