Can global variable be updated from Access query

Posted on 2006-06-06
Last Modified: 2006-11-18
Can I directly assign a query's returned (max) field value to a global variable? How?
Question by:glentek
    LVL 58

    Assisted Solution

    Hello glentek

        variable = dmax("field", "table", "criteria=true")

    or, also "direct":

        variable = currentdb.openrecordset("select max(field) from table").fields(0)


    LVL 65

    Assisted Solution

    Harfang has given u the VBA way which may be the easier to code. My personal choice would be to use DMAX if u just have the one table to lookup.

    If u wanted it to do it in a query, u can make use of query functions

    SELECT SetVar(max(fld)) from table

    but then u have to have a public procedure - so we are back to VBA

    Public Function SetVar(ByVal sVar As String)
        g_sGlobalVar = sVar
    End Function

    LVL 44

    Accepted Solution

    Global variables are normally set before any functions or subroutines in a module.  Those areas are not available to to Jet and the results of queries.  You can programatically alter the contents of a module via VBA but you asked specifically if it could be done from a query - no!
    LVL 30

    Expert Comment

    by:Wayne Barron
    Hello GRayl;

    Would you mind taking a look at the following Post?
    Thanks a bunch.

    LVL 30

    Expert Comment

    by:Wayne Barron

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now