I have a question concerning the validation. My problem is the following:
I have one table which stores info about Herbs. Herbs can be either annual or perennial. Then another table shows what herbs
grow on a particular field. If a herb is perennial, nothing else can grow. But if it is annual, then there can be another
So, on a form which must be in datasheet view, I first enter Herb1 from a combo box. Then, if Herb1 is “annual” I should be
able to enter a value for Herb2. If it is “perennial”, this must not be possible.
So far I came up with the following code in the before_update event of herb2:
Private Sub Herb2_BeforeUpdate(Cancel As Integer)
Dim findCond As String
findCond = DLookup("Herbs.[Type]", "Herbs", "Herbs.[HerbName] = Herb1")
If findCond = "perennial" And Herb2 "" Then
MsgBox "You cannot add a value", vbExclamation, "Error"
Cancel = True
I found out that I can not enable or disable the Herb2 control, because in datasheet view the whole Herb2 column will
become enabled/disabled, not just the text box for a particular record.
What I don’t like in my solution is that the user is notified AFTER he enters something into the field. Can anyone suggest
anything better for this problem? Whereas I would prefer the program to prevent him from accessing this field.
And, IDEALLY, how do I integrate the
(DLookup("Herbs.[Type]", "Herbs", "Herbs.[HerbName] = Herb1" ‘perennial’) or (is null )
as a validation rule of Herb2? It says that there is a syntax error in this statement.
I would really appreciate some suggestions and advice.