MS Access VBA - Help with DLookup function

Posted on 2011-10-31
Last Modified: 2012-05-12
Okay, I am looking to use the DLookup function to populate a variable. I want to use the DLookup function or some SQL Code that will check to see what the maximum value is in the table. I will then assign that value to a variable.
Then I will increment the same variable by 1 (alot like a database sequence).
Question by:jonsuns7
    LVL 75

    Accepted Solution


    Dim x
    x= Nz(DMax("SomeField","SomeTableOrQuery","<OptionalCriteria>") , 0)

    LVL 33

    Assisted Solution

    Why not DMax?

    x= DMax("[Field1]", "tblTableName", "[Field2]=10")

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...then perhaps



    Dim lngCurrent as long
    Dim lngNext as long
    lngCurrent = DMax("[Field1]", "tblTableName", "[Field2]=10")
    lngNext =lngCurrent +1

    I am sure you get the idea...


    Author Closing Comment

    Both solutions were similar and came in at about the same time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    758 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

    14 Experts available now in Live!

    Get 1:1 Help Now