Disable Add Customer cmdButton on Form until all fields are filled out

[EDNYLaw]

The title says it all. I want to prevent Employees from adding a new record before all the fields in a form are filled out. Anyone know how I can accomplish this? I'd greatly appreciate the help.

[Robeen]

One possible way is to check in the cmdButton itself to see if all fields are filled in -> and if not - don't proceed with executing the code - instead - inform user [MsgBox] that one or more fields are not filled in.
If a field is not filled in - you can put up the msgbox and then exit the cmdButton Sub [Exit Sub] and set the focus to the first empty field on the Form.

[ssanfu]

Are you using unbound form/controls? If it is a bound form, the new record is created so you have a place to enter the data. You would use the form before update event to check that all controls have data.
To only create a new record after all controls are filled, you would need to use unbound controls.

Don't really have to disable the button. Add code to the add button to check all controls have data.
If you really want to have the add button disabled, you might use the lost focus event of each control to check if all controls have data. If Yes, enable the button, if No, leave it disabled. Each lost focus event would call another routine - maybe call it CheckForData(). The "CheckForData" sub-routine would check all controls - so only one piece of code to maintain.

[EDNYLaw]

There are 4 text boxes, all bound (all are bound to the table from which the form was created), and 2 combo boxes, all bound (Both these Combo boxes are bound to different tables, of which the Form is not based on). OK, I've got the Property box for Before Update, but what exactly is the either VBA Code or Macro I need to set up? I'm relatively new at Access (just learned it by myself last week from massive amounts of Googling) so if you could give me some specifics that'd be awesome! Thank you so much for the help.

I'm not opposed to coding in VBA, I just don't really know the syntax, so if anyone could tell me what the syntax for VBA would be for the BeforeUpdate property of the form to check all fields are filled in and if all are not filled in popup a message box saying all fields must be filled in, that'd be really great. Sorry for the step by step, but this stuff is tough to learn from scratch just with googling

[ssanfu]

Be sure you are using the FORM before update. (BTW, if you use the form before update, the button does not need to be disabled)
Since there are only 4 controls, I would "brute force it'. The code I would use looks like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Len(Trim(Me.MyControlName & "")) = 0 Then
      MsgBox "Data for " & Me.MyControlName.Name & " is missing. Please enter the data"
      Cancel = True
   End If
   If Len(Trim(Me.TheControlName & "")) = 0 Then
      MsgBox "Data for " & Me.TheControlName.Name & " is missing. Please enter the data"
      Cancel = True
      Exit Sub
   End If
   If Len(Trim(Me.ThatControlName & "")) = 0 Then
      MsgBox "Data for " & Me.ThatControlName.Name & " is missing. Please enter the data"
      Cancel = True
      Exit Sub
   End If
   If Len(Trim(Me.WhatControlName & "")) = 0 Then
      MsgBox "Data for " & Me.WhatControlName.Name & " is missing. Please enter the data"
      Cancel = True
      Exit Sub
   End If
End Sub

Change the control names to your control names (2 places)

[EDNYLaw]

Thanks Steve, I'm still not able to get it. I really have no VBA experience, trying to learn. Here's what I've replaced in your code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Trim(Me.Attendees & "NY Number")) = 0 Then
MsgBox "Data for " & Me.Attendees.NY_Number & " is missing. Please enter the data"
Cancel = True
End If
If Len(Trim(Me.Attendees & "First Name")) = 0 Then
MsgBox "Data for " & Me.Attendees.First_Name & " is missing. Please enter the data"
Cancel = True
Exit Sub
End If
If Len(Trim(Me.Attendees & "Last Name")) = 0 Then
MsgBox "Data for " & Me.Attendees.Last_Name & " is missing. Please enter the data"
Cancel = True
Exit Sub
End If
If Len(Trim(Me.Attendees & "Phone Number")) = 0 Then
MsgBox "Data for " & Me.Attendees.Phone_Number & " is missing. Please enter the data"
Cancel = True
Exit Sub
End If
End Sub

But I'm not sure if I've done it right (clearly I haven't because I can leave fields blank and the record still adds with no pop up). The name of my table (that the 4 text boxes record to is Attendees). The fields are NY Number, First Name, Last Name, Phone Number. Since I'm not very familiar with VBA syntax when you say me.[controlname] is that the table or the field or both? And what exactly goes in the quotes after me.[controlname] in the if len(trim(me.[controlname]) line?

Also, can I use this same procedure to make sure my two combo boxes have a value selected in them? The combo boxes draw from two different tables one gets its values from Workshops and the other from Employees.

Thank you for taking the time to answer the questions, I really appreciate it, especially because NO ONE in the government offices has ANY clue about how to use a database let alone provide actual resources.

[EDNYLaw]

Solved: Little did I realize, that if you go into the design view of the table you can just mark fields as required and this is totally what I need. Though thank you very much for the VBA lesson. Now that I see how these fields interact and some of the syntax in VBA I'm gaining a greater understanding. Thank you again for all your help!

[ssanfu]

But I'm not sure if I've done it right (clearly I haven't because I can leave fields blank and the record still adds with no pop up). The name of my table (that the 4 text boxes record to is Attendees). The fields are NY Number, First Name, Last Name, Phone Number.

Hint: Use only number, letters and the underscore in Access object names.
Example:
BAD - Phone Number
Good - Phone_Number or PhoneNumber

"Phone Number" is bad because there is a space in the name. Spaces are delimiters, so you have to enclose the name in brackets to use the space: [Phone Number]
Spaces also cause you headaches when creating queries, form and reports.

Since I'm not very familiar with VBA syntax when you say me.[controlname] is that the table or the field or both?

It means replace [controlname] with the actual name of the control. If the control name is "abcdef" then the code would be Me.abcdef
Access names controls the same name as the bound field. It is a good idea to rename the control by adding a prefix or suffix to differentiate the control from the field.

And what exactly goes in the quotes after me.[controlname] in the if len(trim(me.[controlname]) line?

Me.[Phone Number] returns the value in the control ie the phone number. The empty quotes concatenated to the value converts a NULL to an empty string. The Trim() function removes any leading or training spaces. And the Len() function gets the length (number of characters) of the value from the control.

Also, can I use this same procedure to make sure my two combo boxes have a value selected in them? The combo boxes draw from two different tables one gets its values from Workshops and the other from Employees.

Yes.

Since I'm not very familiar with VBA syntax when you say me.[controlname] is that the table or the field or both? And what exactly goes in the quotes after me.[controlname] in the if len(trim(me.[controlname]) line?

This is what it should have look like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Len(Trim(Me.[NY Number] & "")) = 0 Then
      MsgBox "Data for " & Me.[NY Number].name & " is missing. Please enter the data"
      Cancel = True
   End If
   If Len(Trim(Me.[First Name] & "")) = 0 Then
      MsgBox "Data for " & Me.[First Name].name & " is missing. Please enter the data"
      Cancel = True
      Exit Sub
   End If
   If Len(Trim(Me.[Last Name] & "")) = 0 Then
      MsgBox "Data for " & Me.[Last Name].Name & " is missing. Please enter the data"
      Cancel = True
      Exit Sub
   End If
   If Len(Trim(Me.[Phone Number] & "")) = 0 Then
      MsgBox "Data for " & Me.[Phone Number].name & " is missing. Please enter the data"
      Cancel = True
      Exit Sub
   End If
End Sub

Me.[Phone Number] returns the value in the control
Me.[Phone Number].name returns the actual name of the control. "Name" is a property of the controls collection. It is also a reserved word.

[EDNYLaw]

That was awesome, thank you so much. Learning VBA from scratch is tough, but that really gave me a good starting point!