Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-02
3
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 24048352
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
ID: 24048380
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 2000 total points
ID: 24048457
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

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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