Table validation rule and form conflicting

[DavidAustin]

Hi there.

I have made a database which has a table with validation rules attached to each of the fields in order to prevent "bad data". For example the forename must be included in the table so I have used a Is Not Null validation rule.

I also have a form used for data entry into the table which works fine. When data is entered into the form and the add new records button is clicked, my custom message also appears as intended. However, if the user then goes into a field as deletes what they have written and then tries to move away from the field, they get caught in a loop of sorts.

Example: "John" is entered into Forename field, User then clicks add new record, message box appears saying error not all fields completed, user then goes back and deletes "John" from the Forename field and tries to click off, message box appears stating the validation text of the table and user cannot move away until something is entered into the field.

I know this is a minor detail which can be avoided by not deleting the text from the field and just moving off of the form but it is annoying knowing it now exists.

Any help will be most appreciated!

[twinnyfo]

You must check to see if the form is "dirty". If it is, then cancel any updates. There are other ways to do this, but this is perhaps the easiest and most straightforward.

[DavidAustin]

Thank you for the quick response, how would I go about checking it it is "dirty". I'm pretty new to Access and VBA.

[twinnyfo]

The general check would be within an If...Then statement:

If Me.Dirty Then
   (perform your required codes)
End If

It just depends on when you want this code to fire and what you want to do when it does fire. Again, lots of different options.

[DavidAustin]

I would want it to fire when the user tries to navigate away from the textbox after it being filled and then deleted.

Is there some sort of code to stop the validation text message box from appearing and allowing the user to navigate to a new control/away from the form?

[twinnyfo]

Place the code in the AfterUpdate Event of the concerned Text boxes. Check for Null. You may have to deactivate the Validation Rules in the Table if you want to do it programmatically (just so you don't accidentally get duplicate error messages).

[DavidAustin]

After trying it out the past couple of days, it seems the only way forward is to remove the rules from the table.

How would I go about putting them into the data entry form so that the rules are tested when an "add new" button is pressed?

I have 6 fields which need different validation rules:

Forename - needs to be included - previously "Is Not Null" in table
Surname - needs to be included - previously "Is Not Null" in table
Date of birth - needs to be included and a valid date in past
Reference number - not mandatory but if included must be 10 digits
Box number - needs to be included
File number - not mandatory

[twinnyfo]

When the Add New button is pressed, evaluate those six text boxes with those same rules in the buttons OnClick event. For example:

Private Sub cmdAddNew_Click()
    Dim strPrompt As String
    strPrompt = ""
    If IsNull(Me.txtForename) Then
        strPrompt = "You must include the Forename"
    End If
    If IsNull(Me.txtSurname) Then
        If strPrompt = "" Then
            strPrompt = "You must include the Surname"
        Else
            strPrompt = strPrompt & "; " & _
                "You must include the Surname"
        End If
    End If
    .... etc.
    If strPrompt = "" Then
        'Go do your other stuff
    Else
        MsgBox strPrompt, vbOkOnly, "Please fix errors"
    End
End Sub

[DavidAustin]

Thank you so much for your help. Can I just walkthrough the code for peace of mind as I'm pretty new to this so I can make sure I know what is going on.

So the strPrompt is a variable which is being changed according to the lack of values in the textboxes. It tests the first textbox to see if the value is null and if it is changes strPrompt to a message. This is then repeated for each of the textboxes and their rules. Finally the variable is tested to see if there are any unfilled boxes (aka if strPrompt is not still "") and informs the user with an error message.

[twinnyfo]

You've got it exactly right. Hope this hepps!

[DavidAustin]

So I have managed to use your code to implement the rules in the data entry form (thanks again!) but I have a new semi-related issue...

I've also got a form where users can edit existing records if mistakes have been made. I've managed to implement that the Forename and Surname must be filled in but I have got stuck because for a record to be "complete" either the Date of Birth or reference number must be included.

So a record can have both DoB and ref number or just DoB or just ref number but shouldn't work if it does not contain neither of them. Any ideas how I could write this? I managed it in the add form using the following (even though it's probably not the most efficent way of doing it, it seemed to make sense!):

'The record must contain at least a DoB or a NHS
'Case 1 - when there is no DoB or NHS
    If IsNull(Me.txtDoB) And IsNull(Me.txtNHS) Then
        If strPrompt = "" Then
            strPrompt = "A record must contain either DoB or NHS No."
        Else
            strPrompt = strPrompt & "; " & _
            "A record must contain either DoB or NHS No."
        End If
    End If
 
'Case 2 - is a NHS but no DoB
    If IsNull(Me.txtDoB) = True And IsNull(Me.txtNHS) = False Then
        If Len(Me.txtNHS) <> 10 Then
            If strPrompt = "" Then
                strPrompt = "The NHS Number is invalid"
            Else
                strPrompt = strPrompt & "; " & _
                "The NHS Number is invalid"
            End If
        End If
    End If
 
'Case 3 - is a DoB but no NHS
    If IsNull(Me.txtDoB) = False And IsNull(Me.txtNHS) = True Then
        If Me.txtDoB >= Date Then
            If strPrompt = "" Then
                strPrompt = "You must include a valid Date of Birth"
            Else
                strPrompt = strPrompt & "; " & _
                "You must include a valid Date of Birth"
            End If
        End If
    End If
 
'Case 4 - when there is both a DoB and a NHS - check for valid
    If IsNull(Me.txtDoB) = False And IsNull(Me.txtNHS) = False Then
        If Me.txtDoB >= Date Then
            If strPrompt = "" Then
                strPrompt = "You must include a valid Date of Birth"
            Else
                strPrompt = strPrompt & "; " & _
                "You must include a valid Date of Birth"
            End If
        End If
 
        If Len(Me.txtNHS) <> 10 Then
            If strPrompt = "" Then
                strPrompt = "The NHS Number is invalid"
            Else
                strPrompt = strPrompt & "; " & _
                "The NHS Number is invalid"
            End If
        End If
    End If
 

[twinnyfo]

That should work. Keep in mind that data validation rules that are user created, although they may work very well, are almost always very ugly when you have many values and possibilities. There are, no doubt, slightly better ways to get your same results, but the key at this point is whether or not it works. As you sit back and reflect on how it works, then you can perhaps find ways to simplify the code.

For example, you could first just check to see if either of the two dates is null:

If IsNull(Me.txtDoB) Or IsNull(Me.txtNHS) Then
    (Evaluate which ones or both and update the string)
End If

Some of this may also be according to your preferences or if you want to have a particular layout for your code that will be easier to update in the future.

[DavidAustin]

It look a little while but I worked out why it wasn't working. I put the code into my edit form's before update event and it would fire when the two fields were null but would still allow the update to occur. Turns out that I had forgotten to include Cancel = True if the strPrompt didn't equal "".

The only final problem I have now is that if someone deletes information in the field then tries to navigate away, the message box will fire fine but the original information has been deleted. Is there a way of resetting the field to the original state of the record prior to editing just in case someone deletes the wrong field when it was correct in the first place?

EDIT: I found the Old Value property! Works perfectly now so thank you for all of your help Twinnyfo!

[twinnyfo]

Glad we could be of service! Keep coding!