Solved

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

Posted on 2009-04-02
3
319 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:chris3vic
3 Comments
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
0
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
Hi chris3vic,

Have a look at the following:
http://www.classanytime.com/mis333k/sjsqlexercise1.html

Good Luck!

Gary
0
 
LVL 4

Accepted Solution

by:
Avelan earned 500 total points
Comment Utility
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
TestFunction([Table]![Col1],[Table]![Col2])

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.

-Avelan
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"

        Else

            TestFunction = "The first one's numeric, the second isn't"

        End If

    Else

        TestFunction = "Blah, the first isn't numeric so i won't bother"

    End If

End Function

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now