Results 1 to 3 of 3
  1. #1
    mrmims is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    53

    Declaring Global Variables with DLookup?

    Hello,



    I am not new to Access, but new to using Variables properly and all that.

    I have a table [tbl_Working_Coe] which has a bunch of numbers [Working_Coe] which are used through out the database to calculate working coefficients for loads and speeds. I want to give the admin user the ability to change these Working Coefficients and have then update through out the database where used. I would then be using these variables in reports or forms to calculate the desired Working Coefficients for weight and speed.

    A simplified example of what i am trying to accomplish is the conversion of pounds to kilograms. This calculation would be used roughly 20 times around the report, and if for some reason the admin wants to change the conversion from .454 to a the more specific 0.453592, i want them to change it in the table, and then have it update through out the database.

    When I try to declare the variable in a module, I keep getting errors. These are the VBA codes I have tried that I am sure are wrong and have not worked.

    Code:
    Public LBtoKG As Long
        LBtoKG = DLookup("[Working_Coe]", "tbl_Math_Variables", "[Work_Coe_ID] = 3")

    Code:
    Public Const LBtoKG as Long = DLookup("[Working_Coe]", "tbl_Math_Variables", "[Work_Coe_ID] = 3")
    Thank you for your help

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The line to set the variable would have to be within a procedure. You could run a function from an autoexec macro.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm guessing this is only one of many such lookups, based on the conversion being made at the time; perhaps based on combo box selections. In that case, an AutoExec probably would not apply, but as Paul said, the assignment of the value to a variable needs to be in a procedure. So if for example, you want to retrieve the coe for a conversion based on choices, the assignment might take place in an AfterUpdate event of a combo:
    Code:
    Private Sub cmbCOE2
    LBtoKG = DLookup("[Working_Coe]", "tbl_Math_Variables", "[Work_Coe_ID] = 3")
    End Sub
    where Work_Coe_ID is the bound value of the combo and Public LBtoKG As Long is declared in a standard module, or LBtoKG As Long is declared in the declarations section of the form module.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. declaring a Global variable
    By George in forum Forms
    Replies: 3
    Last Post: 05-24-2016, 09:33 AM
  2. Passing global variables question
    By newbieX in forum Programming
    Replies: 6
    Last Post: 10-03-2014, 02:09 PM
  3. Cannot create Global Variables
    By Paul H in forum Programming
    Replies: 3
    Last Post: 05-20-2014, 11:27 AM
  4. Global Variables?
    By futurezach in forum Reports
    Replies: 4
    Last Post: 06-20-2013, 03:45 PM
  5. Setting global variables
    By Remster in forum Programming
    Replies: 1
    Last Post: 08-24-2011, 08:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums