Creating an If statement in VBA and calling it from design view

I need to write a pretty long and extensive If statement in MS Access which looks at the values of other fields and determines a code to give the record. The if statement has a lot of arguments based on various value combinations in the record.

I'm ok with the syntax of If, Else statements, what I really want advice on is how to structure that statement in a module and call it from design view.

Tried various ways but having a lot of trouble.

Any help please would be appreciated.
Who is Participating?
AvelanConnect With a Mentor Commented:
You can put the code in a function in a module and use the function in an expression in a query for example.
Not exactly sure what you mean by "design view" so don't know if this is what you need.

For example, if you want to test some conditions against column1 and column2 values and display the result in a third column
in a query, just create a function that takes two arguments and put in a call to it as an expression in the design view of a query.

Type the following in the query design view "Field" of the column you desire to show the result in

Assuming here your function is called TestFunction and you have columns named Col1 and Col2 from a table called Table shown also
on the query.

Not sure if this helps at all.

Function TestFunction(ByVal Value1 As Variant, ByVal Value2 As Variant) As String
    If IsNumeric(Value1) Then
        If IsNumeric(Value2) Then
            TestFunction = "Whee, they're both numeric"
            TestFunction = "The first one's numeric, the second isn't"
        End If
        TestFunction = "Blah, the first isn't numeric so i won't bother"
    End If
End Function

Open in new window

You will probably want to call it from an event procedure on the form such as AfterInsert or AfterUpdate, it may be that you need to call it from multiple events also.

If you need to do this from 1 event then put the code in the event procedure, if multiple then create the code in either the forms module or a global module and set the event(s) to =FunctionName()

Cheers, Andrew
Hi chris3vic,

Have a look at the following:

Good Luck!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.