Link to home
Start Free TrialLog in
Avatar of justinmoore14
justinmoore14Flag for United States of America

asked on

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
Avatar of VTKegan
VTKegan
Flag of United States of America image

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.

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
Avatar of justinmoore14

ASKER

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
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.
ASKER CERTIFIED SOLUTION
Avatar of VTKegan
VTKegan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.


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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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