Changing a Validation Rule on a form using vba code

Question

I am trying to find the best approach to change a Validation Rule for a text box named [txtSerialNumber] based on what a user enters into a combo box named [cboAssemblyNumber] on the same form using VBA code in Microsoft Access.

I am currently a novice in writing VBA code, so any help on this would be greatly appreciated.

Answer 1

Rather than using the ValidationRule property I would normally undertake this sort of validation in the BeforeUpdate event procedure of the txtSerialNumber control:

    Const MESSAGE_TEXT = "Please verify assembly number and try again."

    If Me.cboAssemblyNumber = 1 Then

        If Mid(Me.txtSerialNumber,5,7) <> "8102187"

            MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"

            Cancel = True

        End If

    End If

It would be a simple task to extend this code to impose different constraints for each possible assembly number value.  A Select Case statement would be more appropriate for applying multiple constraints.

You should also remove any value in the txtSerialNumber control in the AfterUpdate event procedure of the cboAssemblyNumber control with:

    Me.cboAssemblyNumber = Null

Otherwise there is nothing to stop the user selecting a value other than 1 in the cboAssemblyNumber control, or simply leaving the control empty, and then entering a value in the SerialNumber control which does not comply with the above constraint, following which they could select 1 in the cboAssemblyNumber control, resulting in invalid data.

You can of course add a further layer of validation in the form's BeforeUpdate event procedure, using the same code as above.

Answer 2

If cboAssemblyNumber=1 Then

     Me![txtSerialNumber].ValidationRule = Mid([SerialNumber],5,8)="8102187"

Else

     Me![txtSerialNumber].ValidationText = "Please verify assemblynumber and try again"

End If

The ValidationRule property is a text property, so it has to be specified as a text expression:

If Me.cboAssemblyNumber = 1 Then

    Me.txtSerialNumber.ValidationRule = "Mid(txtSerialNumber,5,8)=""8102187"""

Note that I changed your expression to use the name of the control, txtSerialNumber, for consistency, and took out unnecessary brackets.  However there are still some problems here:

1. You say "Mid([SerialNumber], 5, 8)", but the literal value you gave, "8102187", is only 7 characters long, not 8.
2. You assign a message to the ValidationText property in the Else block, which means that message won't be displayed if the serial number fails the validation rule.  I think, since the message is so generic, you should probably assign it at design time.  It will only be displayed when the serial number fails whatever ValidationRule has been assigned.

Answer 3

Thanks for the response Dirk; however, I should of been more specific on what I am trying to accomplish.

I will try to explain.

I have a form named frmAxionKits where the user enters an Assembly Number in the [cboAssemblyNumber] field and then scans a  Serial Number in the [txtSerialNumber] field.

What I want to happen is when they choose an assembly number, the serial number's validation rule should change depending on which assembly number is selected.

I have been playing with some code that looks like this which does not work:

If cboAssemblyNumber=1 Then

     Me![txtSerialNumber].ValidationRule = Mid([SerialNumber],5,8)="8102187"

Else

     Me![txtSerialNumber].ValidationText = "Please verify assemblynumber and try again"

End If

Answer 4

Here's a simple example:

Private Sub cboAssemblyNumber_AfterUpdate()

    With Me.txtSerialNumber

        If IsNull(Me.cboAssemblyNumber) Then

            .ValidationRule = ""

        Else

            Select Case Me.cboAssemblyNumber

                Case "A"

                    .ValidationRule = "< 10"

                    .ValidationText = "For A, serial number must be < 10."

                Case "B"

                    .ValidationRule = ">= 10"

                    .ValidationText = "For B, serial number must be >= 10."

            End Select

        End If

    End With

End Sub

But not that that won't react to an existing value that is wrong according to the new validation rule;  the rule will only be tested when the value of txtSerialNumber is changed by the user.