Disable Button Until All Fields Are Entered Access 2010

Hello Experts,

I have a form in Access 2010 that has roughly 26 fields and the following buttons (Save, New Record, and Cancel).  The 26 fields are required to have data in them (which I have already enabled them as “Required” in the table) before the record is allowed to be saved. What I am trying to accomplish is have the “Save” and “New Record” button disabled until all the fields have data in them and then this enables only the “Save” Button.  Once the “Save” Button is pressed the “New Record” Button is enabled and the “Save” Button becomes disabled allowing the user to enter the next record.

Justin
LVL 8
justinmoore14IT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VTKeganCommented:
I use a form like this:

 
Private Sub ValidateFormComplete()
Dim Ctr As control
Dim FoundError as Boolean

FoundError = False
    For Each Ctr In Me.Controls
    Select Case Ctr.ControlType
        Case acComboBox, acTextBox, acCheckBox
            If IsNull(Ctr.Value) Or Ctr.Value = "" Then
                Ctr.BackColor = RGB(223, 167, 165)
                FoundError = True
            Else
                Ctr.BackColor = RGB(255, 255, 255)
            End If
    End Select
    Next Ctr

If Not FoundError Then
    Me.SaveButton.Enabled = True
Else
    Me.SaveButton.Enabled = False
End If

End Sub

Open in new window


You will have to put ValidateFormComplete in the AfterUpdate event of all 26 controls on the form.

Then in the OnClick event of the save button, Just add Me.NewRecordButton.Enabled = True after you save that record.

Hamed NasrRetired IT ProfessionalCommented:
Modify to your case, Form_Timer sub is used. I used a 1 second timer interval.
This code in the timer event, enables a command button, Command6, when the form is dirty and fields has values, other wise it is disabled. It checks for textboxes.

Private Sub Form_Timer()
    Dim ctl As Control
    Dim enbl As Boolean
    enbl = True
    If Me.Dirty Then
        For Each ctl In Me.Controls
            If TypeName(ctl) = "TextBox" Then
                If IsNull(Me.Controls(ctl.Name)) Then
                    enbl = False
                Else
                End If
            End If
        Next
        If enbl Then
            Command6.Enabled = True
        Else
            Command6.Enabled = False
        End If
    End If
End Sub
justinmoore14IT ManagerAuthor Commented:
Hello VTKegan and Hnasr, thank you both for your quick responses.

VtKegan, when I load the form after I have made the code changes you suggested above the first thing I notice is the Save and Add buttons are not grayed out?? Also, now if I hit the Save Record button I get the following error message (The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: ValidateFormComplete) Any ideas??


Hnasr,
 
Thank you for your suggestion as well. I would like to keep confusion down to a minimal so I would like to only try one set of code at a time, but I do have a couple of questions right off. Where exactly does the code go?? For Example on the Form, Save, New Record, or each of the 26 field boxes??




Justin
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Hamed NasrRetired IT ProfessionalCommented:
Thecode goes in the ontimer event only once.
How?
Display the form properties.
Select events.
Timer interval: 1000
On Timer : [Event Procedure], then click ... to edit
The code goes there!

The code is checked every 1 second (1000 ms).

Try the code, modifying the Command6 name to what you have.
VTKeganCommented:
Let me explain the code a little more.  You will not need to place this after all 26 controls.  What I would do is to NOT disable the Save Button, but leave it enabled.  In the OnClick event of the SaveButton put ValidateFormComplete, and then it will check to make sure everything is complete.  If it is not, then the required controls will turn red letting the user know they need to complete that field.


So this would be my modified code:

The OnCurrent Event of the Form:

 
Private Sub Form_Current()
    Me.AddNewButton.Enabled = False
End Sub

Open in new window


The OnClick Event of the SaveButton:

 
Private Sub SaveButton_Click()
    If ValidateFormComplete Then
        'Save Record Code Here
        Me.AddNewButton.Enabled = True
    Else
        MsgBox "Form not complete.  Please complete all highlighted fields."
    End If
End Sub

Open in new window


The the forms Code Module (The ValidateFormCompelte Function):

 
Private Function ValidateFormComplete() As Boolean
Dim Ctr As control
Dim FoundError as Boolean

FoundError = False
    For Each Ctr In Me.Controls
    Select Case Ctr.ControlType
        Case acComboBox, acTextBox, acCheckBox
            If IsNull(Ctr.Value) Or Ctr.Value = "" Then
                Ctr.BackColor = RGB(223, 167, 165)
                FoundError = True
            Else
                Ctr.BackColor = RGB(255, 255, 255)
            End If
    End Select
    Next Ctr

If Not FoundError Then
    ValidateFormComplete = True
Else
    ValidateFormComplete = False
End If

End Sub

Open in new window


So it will work like this.  When the form opens up the AddNewButton should be greyed out and disabled. The Save Button will be enabled and normal.

The User Fills out the form.

The User Clicks the save Button.

If the form is complete then the AddNewButton becomes enabled.

If the form is not complete.  All fields that are not complete will be highlighted red and message box will appear to tell the user to complete highlighted fields.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
justinmoore14IT ManagerAuthor Commented:
VTKegan, Thank you for going into much greater detail and explaining your code for me. I think I follow everything except for one part. Please forgive me. You lost me when you said (The the forms Code Module) specifically (Code Module). Does this just get placed under the code for the Forum under the "On Current" procedure??



Justin
VTKeganCommented:
When you open up the VBA Editor by clicking on the '...' It takes you to that forms Code Module.  Code in Access are stored in Modules.  It can either be a Class Module, Global Module, Form Module, or Report Module.  Whenever you write code for a form such as onCurrent or onClick of a button, that code goes into that Forms Code module.  If you look at the left of the screen it will show you a list of all of the modules in your application.

You would just paste this code separately from everything else.  It is its own stand alone function.

So for example, in the OnCurrent Event it created a Sub in this forms Code Module.

Private Sub Form_Current()

End Sub

That is a standalone Sub.  This function gets pasted separately not inside anything else.
This code does not go inside of any Sub.  But you can call this function from anywhere in this forms Code Module.  So just go to the very bottom of the code and paste it there.


justinmoore14IT ManagerAuthor Commented:
VtKegan,

Once again thank you for your explanation. For some reason I am still having issues getting this to work, so I created a Demo database that only has a couple of fields in it (Not the 26) that I have erased what I screwed up and made a clean slate. I think it will just be easier if you don't mind to let you input the code and then me go back and look at what you have done (I am a visual learner) and with your great explanations that you have written I will be able to see exactly what you mean.

Now a couple quick things as this is a demo database. The only required field that is currently set is "State". When you open the database click on the "Add Data" button and it will take you to the form that I am working with.


Justin
Demo---Copy.mdb
VTKeganCommented:
Justin,

Take a look at this database.  I added the code.  I also changed to Cycle only the Current Record.  

This highlights the fields red if they are not completed and gives you a message box.

If it is complete, then the Add New Button will become enabled.  I also renamed the buttons to follow a more standard naming convention, but I'm guessing that might just have been because it was a quick demo.

let me know if this helps.
Demo---Copy.mdb
justinmoore14IT ManagerAuthor Commented:
VtKegan,

That worked great and it makes since now.

One other thing, in seeing how it works now I remembered why I needed the "Save Record" button to become grayed out after it is pressed and the record is saved. If the user for Example is only entering one "Entry" (Will be the case quite a bit) so to speak and they hit the "Save Record" button since Access doesn't actually clear out the fields they may be a little confused on weather or not the record actually saved (Even though you and I both know it did). If that makes since??

Yes, it was a quick demo that I put together, but I appreciate you going ahead and changing some of the names for me. :)



Justin
VTKeganCommented:
You will need to change the OnClick Event Code to this:
Private Sub btnSaveRecord_Click()
    If ValidateFormComplete Then
        'Save Record Code Here
        Me.btnAddNewRecord.Enabled = True
        Me.btnAddNewRecord.SetFocus
        Me.btnSaveRecord.Enabled = False
    Else
        MsgBox "Form not complete.  Please complete all highlighted fields."
    End If
End Sub

Open in new window

justinmoore14IT ManagerAuthor Commented:
Ok, it worked until I hit the "Add New Record" button. Once I did that it cleared out all the fields, but now the "Save Record" and "Add New Record" buttons are both Grayed out??


Justin
VTKeganCommented:
In The OnCurrent Event You will need to add

Me.btnSaveRecord.Enabled = True
justinmoore14IT ManagerAuthor Commented:
Perfect....Now one last question about this code. Is there anyway to Exclude only one Field (Record Numbers Already Used (Please Use The Next Sequential #....More specifically). Just curious.



Justin
justinmoore14IT ManagerAuthor Commented:
Hnasr,

Thank you for your explanation. However, I do still have a question. You said (modifying the Command6) I presume this would be the name of the "Save Button"?? If you have time could you insert the code into the "Demo" Database that I uploaded earlier and then I will be able to see exactly what you are talking about?? Also, the other reason why I ask is does it no only look at "Text Boxes"? and since I have a combination would it still work??



Justin
VTKeganCommented:
Hnasr's code as posted  is for text boxes only and would exclude combo boxes, but could easliy be changed to include combo boxes.  The drawback I see to his code is that you will run a section of code every second which is unecessary.

To get a little deeper I typically do not use the Required property in the table designer unless it is ABSOLUTELY required.  Otherwise I just use my own coding to determine what is required.  So the code I use is like this:

 
Public Function ValidateFormComplete() As Boolean
Dim FoundError As Boolean
Dim Ctr As control

FoundError = False
    For Each Ctr In Screen.ActiveForm.Controls
    Select Case Ctr.ControlType
        Case acComboBox, acTextBox, acCheckBox
            If Ctr.Tag = "*" Then
                If IsNull(Ctr.Value) Or Ctr.Value = "" Then
                    Ctr.BackColor = RGB(223, 167, 165)
                    FoundError = True
                Else
                    Ctr.BackColor = RGB(255, 255, 255)
                End If
            End If
    End Select
    Next Ctr

ValidateFormComplete = Not FoundError

End Function

Open in new window


You will notice there is one additional line of code in this snippet that says:

If ctr.Tag = "*" Then

Each control on your form has a Tag property under 'Other'  So what I do is put a * in that property if I want to make that field required.  Then I just run my function ValidateFormComplete like you are doing now,  and it only checks the controls that have the  * in the Tag property.  This allows me to customize each form and quickly set which controls need to be required.

I hope this helps. It looks like you need to get more experienced with VBA before you dive too deep into code.  There are books available and many other examples for you to look at.  But the best way to learn is by doing, and you are doing that so keep it up.

Good Luck.
Hamed NasrRetired IT ProfessionalCommented:
ustinmoore14,
Have a look at the database. Code inserted, and added check for combo box. You may add for other input controls like check box or option button, etc.

Previous comment for reference.
Modify to your case, Form_Timer sub is used. I used a 1 second timer interval.
This code in the timer event, enables a command button, Command6, when the form is dirty and fields has values, other wise it is disabled. It checks for textboxes.

Private Sub Form_Timer()
    Dim ctl As Control
    Dim enbl As Boolean
    enbl = True
    If Me.Dirty Then
        For Each ctl In Me.Controls
            If TypeName(ctl) = "TextBox" Then
                If IsNull(Me.Controls(ctl.Name)) Then
                    enbl = False
                Else
                End If
            End If
        Next
        If enbl Then
            Command6.Enabled = True
        Else
            Command6.Enabled = False
        End If
    End If
End Sub

Demo---Copy-codeInserted.mdb
justinmoore14IT ManagerAuthor Commented:
VTKegan,

That is exactly what I was after! You said ( But the best way to learn is by doing) I would agree and is why I asked so many questions so now I know for next time, and it is also why I wanted to pursue the code that Hnasr, posted. Even though that is not exactly what I was after in this case later down the road it may be exactly what I need and I will not only have it but understand it. I am always looking to learn new things and I really appreciate you taking all of your time to help me through until I got the exact results that I need as well as teaching and giving me explanations along the way. :-)


Hnasr,

I appreciate you adding the code to the database and then re-uploading it for me to look at. While this is not exactly what I am after (Would like to be able to change the color of the boxes that aren't filled in, Have the "Add Record" button disabled on load, etc...) I still like the code that you provided and may end up using it later on. Plus I learned a different way to do another task. Thank you for sticking with the question and helping me out as well.



Happy New Year to both of you!!



Justin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.