Solved Invalid Use Of Null (1 Viewer)

[micks55]

Hi All. I have three unbound controls on a form. If all 3 have content I want to enable a Save button but if any one of them does not then I want to disable the button. I've put vba in the afterupdate of each of them and tried Len(), Nz(), IsNull() and = "". It works fine once all three have content but if I delete the contents of one then I get an Invalid use of Null error. The most recent attempt was

If Len(Nz(Me.Text4, "")) = 0 Or Len(Nz(Me.Text8, "")) = 0 Or Len(Nz(Me.Text15, "")) = 0 Then Me.Command11.Enabled = False Else Me.Command11.Enabled = True

Thanks

 

[theDBguy]

Hi. I think you could also try using Conditional Formatting.

 

[micks55]

Hi DBguy, thanks for replying. I'm ok with CF to change a control's format but don't understand how it might be used to enable another control.

 

[theDBguy]

Hi DBguy, thanks for replying. I'm ok with CF to change a control's format but don't understand how it might be used to enable another control.

I was thinking you would apply the CF on the button, but I wasn't sure if that's allowed. I think maybe CF doesn't work on buttons but can't check right now.

Have you tried using IsNull()?

 

[micks55]

Just been checking out how to use CF to hide some of the buttons on a continuos form. Can see how it might work by using a text box that looks like a button but expecting a problem because this "hides" the button just by changing the fore colour and back colour to same as the form. As the text box is actually still there then I'm thinking the OnClick event will still trigger if you know where to click. I'll retry your suggestion before I start experimenting with a CF'd text box.

 

[micks55]

IsNull() is working thanks. Still have an issue but it's not that so now looking again at rest of vba.

 

[theDBguy]

Just been checking out how to use CF to hide some of the buttons on a continuos form. Can see how it might work by using a text box that looks like a button but expecting a problem because this "hides" the button just by changing the fore colour and back colour to same as the form. As the text box is actually still there then I'm thinking the OnClick event will still trigger if you know where to click. I'll retry your suggestion before I start experimenting with a CF'd text box.

Another approach, then, is to not disable the button. Rather, simply handle the situation when the user clicks on it. If any of the Textboxes is empty, do nothing.

 

[bob fitz]

Use the following code in the After Update event of each of the textboxes.
Or better still, create a sub procedure containing the code and call that sub procedure in the After Update event of each textbox.

If Len(Nz(Me.Text4, "")) = 0 Or Len(Nz(Me.Text8, "")) = 0 Or Len(Nz(Me.Text15, "")) = 0 Then
  Me.Command11.Enabled = False
Else
  Me.Command11.Enabled = True
End If

 

[micks55]

I was going that way as a last resort but thanks to you making me think about it I have it solved.

In the AfterUpdate of Text 4 the second line of code grabs whats in Text4
'check we have a good email address and it must not contain \ / : * ? < > | [ ] """
Dim myTxt As String, myMsg As String
myTxt = Me.Text4.Value 'don't think I need .Value as it's unbound, will try that next

That works fine if you tab past and leave it empty because there is no AfterUpdate event.

Once you have typed an email in there then later go back and wipe whatever is in there then the AfterUpdate tries to put a Null into myTxt

The simple fix at very start of Text4_AfterUpdate is
If IsNull(Text4) Then Exit Sub

Thanks DBguy

 

[micks55]

Thanks to bob fitz. I'd tried exactly that and had wrongly assumed the error procedure was picking up on that because it was the most recent code addition. Sadly the error was elsewhere.

Thanks all.