Mandatory Fields in MS Access

[sreyes17]

How do you set up a mandatory field in access?

For my first field in the form, I have a drop down menu option (change and no change). If the option, change is selected, I want the next field to be mandatory. And if no change is selected, I want the next field to be invisible.

HELP.

[CreganTur]

This is easy to accomplish, but requires a little understanding of how Forms work.

First of all, you need to make sure that all of the objects on your form have unique names- objects are labels, textboxes, comboboxes, buttons, etc. Don't use the default names (ie. Label1) that's just bad practice and can lead to some issues down the road as you make more complex forms/programs.

First, we'll cover making the desired textbox invisibe if 'no change' is selected in the combobox. On your properties sheet, select the combobox in design view, and select the After Update event (choose code builder if asked). Now, I suggest disabling the textbox, instead of making it invisible. This will grey out the textbox and will not allow anyone to make use of it. Here's the code (in this example cboChange is the name of your combobox and txtField is your textbox):
If Me.cboChange= "no change" Then
Me.txtField.Enabled = False
End If
The above states that if the value of your combobox is 'no change' then the textbox will be disabled.

'Me.' is a special keyword in Access that refers to the current open Form. It's the easiest, and most accurate way to refer to any object exisitng on the current Form.

----------

The only way to make a field mandatory is to have a check in place somewhere in your code.

I am going to assume that you have some sort of button that will make use of the data entered into the textbox if your combobox = 'change'. So, you would put some data validation behind that button. For example:
If cboChange = "change" And IsNull(txtField) Then
msgBox "This field must be filled in!"
Me.txtField.SetFocus
Exit Sub
End If
The above code first looks to see if your combobox value is 'change', and it looks to see if the textbox is empty. If both of these conditions are true, then a message box fires telling the user that the textbox must be filled in. The SetFocus command puts focus on the textbox. Exit Sub ends your code so that it cannot continue until the user fills in the textbox.

HTH

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: Tony (OBP) reminded me that make a field 'Required' is also a Table property. Go to the design view of your table and take a look at the properties for the field(s) you want to make required. There should be a 'Required' option. Just set this to True.

[sreyes17]

I tried your VBA


If Me.cboChange= "no change" Then Me.txtField.Enabled = False
End If


But then for each new record in the form it's also shaded out. And if I were to switch back to the option Change the field remains shaded. So basically, I need the code to not have each new record be shaded out AND if I were to switch back to the option "Change" I need that not shaded out, but for the text field to be mandatory.



Also, I'm not sure what you mean by this comment
"I am going to assume that you have some sort of button that will make use of the data entered into the textbox if your combobox = 'change'. "


Button? I don't have a button... Should I? I have that combo box with the option Change and No Change and below it I have a free text box for people to fill out if there was a Change...

[CreganTur]

if I were to switch back to the option Change the field remains shaded.

It's because no code has been written to affect that change. You need to tell VBA everything you want to happen, it won't do anything on its own. So in this case you can use:


If Me.cboChange = "no change" Then
Me.txtField.Enabled = False
ElseIf Me.cboChange = "change" Then
Me.txtField.Enabled = True
End If



I need the code to not have each new record be shaded out

How are you moving to the next record? Are you simply using the navigation buttons that are along the bottom of your form by default?




Button? I don't have a button... Should I? I have that combo box with the option Change and No Change and below it




I assumed that you had a button that would submit the value of the textbox into your table. From your response, I can tell that this assumption is incorrect.


How are you updating what you write in the textbox into your table?



I have a free text box for people to fill out if there was a Change...
What kind of change are we talking about here? Can your users write anything into this textbox and have it saved into your table? Are there certain set change 'reasons' that should be entered, or can the User enter in anything that they want?


I'm just trying to understand your process so I can better help.

[sreyes17]

How are you moving to the next record? Are you simply using the navigation buttons that are along the bottom of your form by default?

Yes. I would use the navigation buttons that are along the bottom of my form or use the scroll on my mouse.






I assumed that you had a button that would submit the value of the textbox into your table. From your response, I can tell that this assumption is incorrect.



How are you updating what you write in the textbox into your table?



I think I used the wrong terminology when I said text box. Because I think I know what you are saying...the text box is actually a field. Where you write free text so it's like a text box. And no, I don't have a button.






What kind of change are we talking about here? Can your users write anything into this textbox and have it saved into your table? Are there certain set change 'reasons' that should be entered, or can the User enter in anything that they want?





They write whatever they want. The purpose of this is for the user to write the reason why were was a change.




Thank you.