How to set a validation rule using DLookUp?

[Fekla]

Hi everyone!

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 herb.

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
      Me!Herb2.Undo
  End If
    
End Sub

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.

 

[MarkK]

Is herb2 to be planted the following year? If so, it should not be in the same record with herb1. A year from now the record you are currently validating will be wrong again.
Are there no biennial herbs? Seems to me an herb record might need a 'lifecycle' value, and a field ought be occupied for that number of years.
You need a 'field' table which will rarely change, and an 'herb' table which will rarely change, and then a table between these two, say 'herbfield', so you can affect a many-to-many relationship. Then rather than put herb1 and herb2 in the same record, you create unique records for each year for each herb in each field. It's in this 'herbfield' table where all the action happens.

 

[Fekla]

lagbolt, thank you for trying to understand my problem!
Facts are, the real way my db is implemented is similar to what you said, and I just simplified it for uploading and asking whether DLookUp is working as a Validation Rule.

However, Herb2 is not something to be planted next year. It is something planted at the same time as Herb1 but only if Herb1 is annual!