Validation rule VBA format

[ADixon]

Hi all
I'm trying to write a procedure that will set the validation for a text box
on a form. The text box validation will be based on a value chosen by the
user from a combo box. For example: if the user selects 'PLU' in the combo,
then the procedure runs and the validation is set to 'Like "PLU????" Or IS
Null'. If they choose EAN8 then the validation is set for this selection. I
can't seem to get the format for the validation string correct. Here is a
snippet which throws up a compile error 'expected end of statement':

Dim strPLUValid As String

strPLUValid = "Like "PLU????" Or IS Null"

If Pack_Bcode_Type.Value = "PLU" Then
NewPack_Bcode.ValidationRule = strPLUValid
Else
End If

Any help is appreciated

 

[Marshall Barton]

I'm trying to write a procedure that will set the validation for a text box
on a form. The text box validation will be based on a value chosen by the
user from a combo box. For example: if the user selects 'PLU' in the combo,
then the procedure runs and the validation is set to 'Like "PLU????" Or IS
Null'. If they choose EAN8 then the validation is set for this selection. I
can't seem to get the format for the validation string correct. Here is a
snippet which throws up a compile error 'expected end of statement':

Dim strPLUValid As String

strPLUValid = "Like "PLU????" Or IS Null"

That line is using embedded quotes incorrectly. Try using
this instead:

strPLUValid = "Like ""PLU????"" Or IS Null"

The rule is: To put a quote inside quotes, use two quotes.

OTOH, it is not clear that you really need to use the
validation rule instead of using code in the text box's
AfterUpdate event.

 

[ADixon]

Thanks Marshall

This works exactly as i needed.

For clarification, i only chose to use code instead of normal validation as
i needed to have multiple rules within if statements based on the choice made
within the combo box.

Thanks for the help