Validation rule disregards return value of VBA function

Question

I have a VBA function that returns Boolean (true/false) based on the one parameter that is entered into it. I have attempted to plug this function into a validation rule on an Access form.

Validation Rule:

MyFunction ([My Field])

MyFunction in VBA:

Public Function MyFunction(str as String) as Boolean
    MyFunction=Len(str)>4 'Just as an example, the actual function is a bit more complex, but still returns true when expected and false when expected.  This has been observed when stepping through the actual VBA code
End Function

I can see in VBA that the function actually does get called, and that it returns true and false as expected, but for some reason, regardless of what the function actually returns, Access is still seeing the value of [My Field] as invalid (note: [My Field] is a text field with no input mask).

So, what must I do to get the 'True' value that is returned by my VBA function to register as valid in my validation rule?

I'm using Access 2010.

William · Accepted Answer · 2015-02-14 00:02:23Z

1

Figured it out for anyone else that gets stuck with this. Has to do with how Access stored True and False.

So, using

MyFunction([My Field]) = -1

For the validation rule solved this issue for me.

answered Feb 14, 2015 at 0:02

William

5912 gold badges10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gord Thompson Over a year ago

I suspect that MyFunction([My Field]) = True would work too. It really shouldn't be necessary since you defined your function ... As Boolean, but I have seen cases where a SQL statement will behave incorrectly with ... WHERE someBooleanValue and will work correctly with ... WHERE someBooleanValue = True.

William Over a year ago

Yup. MS Access is a quirky product to use. If I had my choice for desktop applications I would rather use WPF and .Net, where Booleans behave like Booleans and where you can separate business logic from the view.

Collectives™ on Stack Overflow

Validation rule disregards return value of VBA function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related