Need to make a field in a form required for saving but Validation rules not working!

Margarita

Registered User.
Local time
Today, 15:22
Joined
Aug 12, 2011
Messages
185
Hello,
I have a form where the user hits some buttons to manipulate and clean data in a table. Then, the user needs to have access to the entire table in datasheet view to spot check things and manually make any necessary changes to one field (the rest of the fields are locked). What I need to do is make the Comments field required- they have to fill in an explanatory note if they make any manual changes. In the underlying table, I set the Required property of the Comments field to Required and in the form, I set the Validation Rule property to Is Not Null and put in some validation text.
But this isn't doing the job. I can enter any change I want and move to any other record and the form isn't stopping me- even when I close it out.

What is the correct way of making this field required? What I want to be able to do is make it required before closing out the form, but also before moving to another record.
Thanks in advance for your suggestions.
(Access 2003)
 
I think your need to put some code in the Before Update Event of the form to check if something has been entered in the field.

Do a search here for: "required fields" or something similar and a lot of relevant posts will show up.

Catalina
 
Hi Catalina,
I actually did try code before- I used the code you provided in one of your posts in this forum. Here is my modified message box:
PHP:
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
'http://www.access-programmers.co.uk/forums/showthread.php?t=182574
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
 
nl = vbNewLine & vbNewLine
 
For Each ctl In Me.Controls
If ctl.Name = "Comment" Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "If you are making changes to PayStatus or OTType, please include an explanatory note in the Comment field."
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next
 
End Sub

I couldn't get it to work (I am able to move to different records and close out the form without any warning, and the record saves), so I searched more and found suggestions for using teh Validation Rule property, and so that's where I am stuck now. Would you be able to point out to me where I am going wrong with your code? I put it into the Before Update property of the datasheet view form that comes up when the user hits the button on the main form.
Thank you!
 
Hello, for anyone browsing this thread for help, here is how I solved my problem:
In the form, I set the Required property of the field back to No and in the form deleted the Is Not Null validation rule. In the beforeupdate property of the form (the form that contains the table fields in quiestion- NOT the form on which the user hits the button to open that form), I put this code:

PHP:
If IsNull(Me.Comment) Then
     Cancel = True
     MsgBox "You have made changes to either OTType or PayStatus but have not commented the change. Please write a short note in the Comment field explaining why the change was made."
End If

This code can be found in this very helpful thread:
http://www.dbforums.com/microsoft-access/1063326-required-fields-form.html

Now, the user cannot close out the form or move to another record without having made a comment. But it also allows me to make changes or undo changes in the table itself without making unnecessary comments. My form doesn't have a custom close button, the user can only 'x' out of it- but the thread above also contains tips on how to handle the on-click event of any such button that would replicate the same field-required behavior.
 

Users who are viewing this thread

Back
Top Bottom