Validation Rule - a bit more complicated

[Ben Scanlan]

Hi,

I am having trouble using the Query builder to enter a validation rule for a control on a form.

The Form is called [CAPs Initial] and the control is called [Report Ref].

I want the validation rule to lookup values in a separate table ( table name: [CAPs Report Reference], field name: [Report Ref] ) and check to see if the entry already exists. If not then I want the form to allow the entry.

This is what I have entered as the validation rule at the moment:

=DLookUp("Report Ref","CAPs Report Reference","Report Ref = Forms!CAPs Initial!Report Ref") Is Null

I get a message saying there is a syntax error. I'm not bothered about whether this is coded this way or in VBA. I just would like it to work!

Hope someone can help me please.
Thanks

 

[danikuper]

Try using this instead:

=isnull(DLookUp("Report Ref","CAPs Report Reference","Report Ref "= & Forms!CAPs Initial!Report Ref))

This might take care of the syntax problem.

 

[Rich]

Why not just use a combo box?

 

[Ben Scanlan]

Thanks for the help. Got the syntax correct by put some of the expression in square brackets (because I has spaces in control names) . I've managed to solve the problem by using a similar expression in a macro.

Cheers