Enabling Button If Fields are not Null Issue

[mpspeer]

Hi all- New here..

I'm having an issue (I have done some searches but was affraid to update threads over a year old).

The idea is to not have the Save Entry button enabled unless all the fields have criteria. There are two fields that auto populate data and are not in the code below, I assume they didn’t need to be because they will never be null.

My Issue:
The save button is not becoming enabled after all fields have data.

FYI the first 3 in the below code are combo boxes, me.description is a text box…

Also, I am placing this code in the events sections of the Form selection type in the Forms Property Sheet.

[COLOR=black]Private Sub Form_BeforeUpdate(Cancel As Integer)[/COLOR]
[COLOR=black]If IsNull(Me.Workstream) And IsNull(Me.Activity) And IsNull(Me.Hours) And IsNull(Me.Description) Then[/COLOR]
[COLOR=black]Me.SaveEntry.Enabled = False[/COLOR]
[COLOR=black]Else[/COLOR]
[COLOR=black]Me.SaveEntry.Enabled = True[/COLOR]
[COLOR=black]End If[/COLOR]
[COLOR=black]End Sub[/COLOR]

P.S. I have tried this On Current, On Dirty, and Before Update to no avail. In my head I would think the form would have to do some type of refresh in order to register whether there is any data populated in the fields. I’m also somewhat new to VBA, so please be easy on me. Thanks.

 

[Isskint]

code wise i would suggest checking the combobox status with the ListIndex property (should be -1 if nothing selected). With the text field you have to check for Nothing also, so NZ(Me.Description,0)=0 instead of IsNull(Me.Description)

 

[boblarson]

1. create a Function within that form's module like this:

Function DoIEnable()
Me.SaveEntry.Enabled= (Len(Me.Workstream & vbNullString) > 0 _
And Len(Me.Activity & vbNullstring) > 0 And Len(Me.Hours & vbNullstring)>0 And Len(Me.Description & vbNull)>0)
End Function

Then you just call it from each AFTER UPDATE event of the CONTROLS you have listed in that function.

Private Sub Workstream_AfterUpdate()
    DoIEnable
End Sub

You should be able to paste the function I wrote into your form's module. Then just do like I showed in lower procedure (after update) for each of those controls. And, the function I wrote first, should be able to be used exactly as written with no modifications.

 

[mpspeer]

Then just do like I showed in lower procedure (after update) for each of those controls.

When you say "Controls" do you mean for each of the fields the user has to choose from on the Form? Or do you mean just the specific button I want to enable/disable?

 

[boblarson]

When you say "Controls" do you mean for each of the fields the user has to choose from on the Form? Or do you mean just the specific button I want to enable/disable?

Fields are in Tables and Queries. Controls are on Forms and Reports. Fields can be BOUND to controls.

So I am talking about the after update event of these controls:

Workstream
Activity
Hours
Description

 

[mpspeer]

1. create a Function within that form's module like this:

Function DoIEnable()
Me.SaveEntryEnabled= (Len(Me.Workstream & vbNullString) > 0 _
And Len(Me.Activity & vbNullstring) > 0 And Len(Me.Hours & vbNullstring)>0 And Len(Me.Description & vbNull)>0)
End Function

Then you just call it from each AFTER UPDATE event of the CONTROLS you have listed in that function.

Private Sub Workstream_AfterUpdate()
    DoIEnable
End Function

You should be able to paste the function I wrote into your form's module. Then just do like I showed in lower procedure (after update) for each of those controls. And, the function I wrote first, should be able to be used exactly as written with no modifications.

Ok, so I attempted this morning on updating the code steps you posted above and I am getting an error "Complie Error: Invalid Use of Me Keyword" And VB highlights "Function DoIEnable()" in yellow and the ME. in gray in front of Workstream.

Secondly, should'nt the second peice of cose you have posted here end in End Sub?

Thanks.

 

[nanscombe]

Me.SaveEntryEnabled should probably be Me.SaveEntry.Enabled as well.


Another way of doing it.

Function DoIEnable()
' With comments
DoIEnable = TRUE ' Starts off as true

' If control is empty changes to FALSE
DoIEnable = DoIEnable AND (nz(Me.Workstream,"") <>"") 

' If control is empty changes to FALSE
DoIEnable = DoIEnable AND (nz(Me.Activity,"") <>"")

' If control is empty changes to FALSE
' Alternatives for text or number. If it is numeric, allows for a value of 0 to be entered
'DoIEnable = DoIEnable AND (nz(Me.Hours,"") <>"") ' If text
'DoIEnable = DoIEnable AND (nz(Me.Hours,-1) <>-1) ' If numeric

' If control is empty changes to FALSE
DoIEnable = DoIEnable AND (nz(Me.Description,"") <>"")

' Assumed control is called SaveEntry and sets value of Enabled property as appropriate
Me.SaveEntry.Enabled = DoIEnable
End Function

Function DoIEnable()
' Without comments
DoIEnable = TRUE
DoIEnable = DoIEnable AND (nz(Me.Workstream,"") <>"") 
DoIEnable = DoIEnable AND (nz(Me.Activity,"") <>"")
'DoIEnable = DoIEnable AND (nz(Me.Hours,"") <>"") ' If text
'DoIEnable = DoIEnable AND (nz(Me.Hours,-1) <>-1) ' If numeric
DoIEnable = DoIEnable AND (nz(Me.Description,"") <>"")
Me.SaveEntry.Enabled = DoIEnable
End Function

 

[mpspeer]

So I put the first set of code in the forms module? and the second set as well...?

 

[RainLover]

Private Sub Workstream_AfterUpdate()
    DoIEnable
End Function

Should be End Sub not End Function

 

[boblarson]

Me.SaveEntryEnabled should probably be Me.SaveEntry.Enabled as well.


Another way of doing it.

Function DoIEnable()
' With comments
DoIEnable = TRUE ' Starts off as true
 
' If control is empty changes to FALSE
DoIEnable = DoIEnable AND (nz(Me.Workstream,"") <>"") 
 
' If control is empty changes to FALSE
DoIEnable = DoIEnable AND (nz(Me.Activity,"") <>"")
 
' If control is empty changes to FALSE
' Alternatives for text or number. If it is numeric, allows for a value of 0 to be entered
'DoIEnable = DoIEnable AND (nz(Me.Hours,"") <>"") ' If text
'DoIEnable = DoIEnable AND (nz(Me.Hours,-1) <>-1) ' If numeric
 
' If control is empty changes to FALSE
DoIEnable = DoIEnable AND (nz(Me.Description,"") <>"")
 
' Assumed control is called SaveEntry and sets value of Enabled property as appropriate
Me.SaveEntry.Enabled = DoIEnable
End Function

Function DoIEnable()
' Without comments
DoIEnable = TRUE
DoIEnable = DoIEnable AND (nz(Me.Workstream,"") <>"") 
DoIEnable = DoIEnable AND (nz(Me.Activity,"") <>"")
'DoIEnable = DoIEnable AND (nz(Me.Hours,"") <>"") ' If text
'DoIEnable = DoIEnable AND (nz(Me.Hours,-1) <>-1) ' If numeric
DoIEnable = DoIEnable AND (nz(Me.Description,"") <>"")
Me.SaveEntry.Enabled = DoIEnable
End Function

FYI- Using vbNullString is more efficient than using "" (there is a thread somewhere on the forum which goes into that).

 

[nanscombe]

So I put the first set of code in the forms module? and the second set as well...?

The second set of my code is the same as the first just without all the extra comments.

FYI- Using vbNullString is more efficient than using "" (there is a thread somewhere on the forum which goes into that).

Oh. I haven't done any serious development since 2006, and that was still using Access 97.

 

[mpspeer]

The second set of my code is the same as the first just without all the extra comments.

Oh, I tried both, neither disabled the button.. But there wasn't an error for yours, it just didn't quite work.

 

[boblarson]

Oh, I tried both, neither disabled the button.. But there wasn't an error for yours, it just didn't quite work.

Instead of starting off with the button ENABLED, start off with it DISABLED. And Nanscombe's code has a severe flaw. It doesn't check to see that ALL of the items are filled out before enabling. It takes into account only each item individually. So, if the first two that are checked are not filled in, but the last item checked is, it will enable.

You need to check the state of ALL three (which my original code does - and I fixed the code above so the errors shouldn't be there if you try now) in one.

 

[nanscombe]

A working example of my code is attached.

There is no serious flaw in it that I can see, or demonstrate.

DoIEnable = TRUE
Starts as TRUE

DoIEnable = DoIEnable AND (nz(Me.Workstream,"") <>"")
Remains TRUE only if Workstream is not empty

DoIEnable = DoIEnable AND (nz(Me.Activity,"") <>"")
Remains TRUE only if Activity is not empty

DoIEnable = DoIEnable AND (nz(Me.Hours,"") <>"")
Remains TRUE only if Hours is not empty

DoIEnable = DoIEnable AND (nz(Me.Description,"") <>"")
Remains TRUE only if Description is not empty

If any of the controls are empty DoIEnable is changed to FALSE.

 

[boblarson]

Okay, I did misstate that. But given a control should not be enabled until all fields are filled in, it should not be set to be enabled by default. It should be DISABLED from the start and only enabled if it passes muster.

 

[nanscombe]

The variable DoIEnable (the function, as I'm a bit lazy ) starts as TRUE.

The enabled property of the control isn't actually updated until all of the checks are completed.

Function DoIEnable()
' Without comments
[B]DoIEnable = TRUE[/B]

DoIEnable = DoIEnable AND (nz(Me.Workstream,"") <>"") 
DoIEnable = DoIEnable AND (nz(Me.Activity,"") <>"")
'DoIEnable = DoIEnable AND (nz(Me.Hours,"") <>"") ' If text
'DoIEnable = DoIEnable AND (nz(Me.Hours,-1) <>-1) ' If numeric
DoIEnable = DoIEnable AND (nz(Me.Description,"") <>"")

[B]Me.SaveEntry.Enabled = DoIEnable[/B]
End Function

 

[mpspeer]

A working example of my code is attached.

There is no serious flaw in it that I can see, or demonstrate.

DoIEnable = TRUE
Starts as TRUE

DoIEnable = DoIEnable AND (nz(Me.Workstream,"") <>"")
Remains TRUE only if Workstream is not empty

DoIEnable = DoIEnable AND (nz(Me.Activity,"") <>"")
Remains TRUE only if Activity is not empty

DoIEnable = DoIEnable AND (nz(Me.Hours,"") <>"")
Remains TRUE only if Hours is not empty

DoIEnable = DoIEnable AND (nz(Me.Description,"") <>"")
Remains TRUE only if Description is not empty

If any of the controls are empty DoIEnable is changed to FALSE.

Your attached file worked beautifully on my form, with one slight flaw which I am unsure is fixable..

I noticed for the save button to become enabled, you must first click away from description.

Is there a way to enable the save button immediately after data is entered in to Description, not having to click back up on a previous field to "refresh" the form?

 

[nanscombe]

Oooohh, that's awkward. Hmmmm.

Whilst you are in the Description field you can get the value whilst you are typing, you have to use the .Text property but you can only use that property whilst you are in the field ... time for a fiddle.

Function DoIEnable(Optional ByVal useDescriptionText As Boolean = False)
On Error Resume Next
' Without comments
  DoIEnable = True
  DoIEnable = DoIEnable And (Nz(Me.Workstream, "") <> "")
  DoIEnable = DoIEnable And (Nz(Me.Activity, "") <> "")
  DoIEnable = DoIEnable And (Nz(Me.Hours, "") <> "") ' If text

  If useDescriptionText Then
      DoIEnable = DoIEnable And (Nz(Me.Description.Text, "") <> "")
  Else
      DoIEnable = DoIEnable And (Nz(Me.Description, "") <> "")
  End If

  Me.SaveEntry.Enabled = DoIEnable
End Function

I have added an optional parameter which is used only in one place...

Private Sub Description_Change()
  If Len(Me.Description.Text & vbNullString) < 2 Then DoIEnable True
End Sub

DoIEnable is only called by the Change method when me.Description has 0 or 1 characters in it and is called with the optional parameter set to TRUE so it looks at the .TEXT property instead of the default (.VALUE I presume).

A bit of a fiddle but I believe it does what you want.

 

[nanscombe]

After another think I believe I could avoid having that optional parameter at all.

Function DoIEnable()
On Error Resume Next
' Without comments
  DoIEnable = True
  DoIEnable = DoIEnable And (Nz(Me.Workstream, "") <> "")
  DoIEnable = DoIEnable And (Nz(Me.Activity, "") <> "")
  DoIEnable = DoIEnable And (Nz(Me.Hours, "") <> "") ' If text

  If Screen.ActiveControl.Name = "Description" Then
      DoIEnable = DoIEnable And (Nz(Me.Description.Text, "") <> "")
  Else
      DoIEnable = DoIEnable And (Nz(Me.Description, "") <> "")
  End If

  Me.SaveEntry.Enabled = DoIEnable
End Function

And then just used ...

Private Sub Description_Change()
  If Len(Me.Description.Text & vbNullString) < 2 Then DoIEnable
End Sub

ETA:

Yes, that appears to work.

 

[mpspeer]

Yep, either worked pefectly. Thanks!