MS Access 2007 adjacency list tree validate against circular reference

Question

I've implemented adjacency list tree in MS Access 2007. Upon insertion or update I want to validate against circular reference (like a->b->c->a). I've wrote a simple function that given table name and id of a tree node returns true if the path of references is finite and false if there is a loop.

Unfortunately, according to http://msdn.microsoft.com/en-us/library/office/aa191586(v=office.10).aspx:

For field and record validation rules, the expression can't contain user-defined functions, domain aggregate or aggregate functions, the Eval function, or CurrentUser method, or references to forms, queries, or tables. In addition, field validation rules can't contain references to other fields. For records, expressions can include references to fields in that table.

I'm limited to MS Access with Access database engine as backend. I know limitations of adjacency list trees model but I still prefer it.

What is the best way to validate those references?

serakfalcon serakfalcon · Accepted Answer · 2013-12-28T05:17:16

You don't need to put it in a validation rule. What you're trying to do is catch things on insert/update, but you should probably catch them before the insert or update.

To do that, make your own validation run your function in the BeforeUpdate event of the input controls, and Me.Undo and Msgbox (or otherwise inform the user of the error) if the function reveals that there will be a problem.

For example:

Private Sub Input_BeforeUpdate(Cancel As Integer)
    If Not inputIsValid() Then
        'input is not valid
        Me.Undo
        MsgBox "Input is not valid", vbOKOnly + vbCritical, "Invalid Input"
    End If
End Sub

MS Access 2007 adjacency list tree validate against circular reference

1 Answers