Creating Project Number in VBA RRS feed

  • Question

  • I am attempting to create a new ID in a New Project Form based off a query which basically adds one (1) to the highest project number so far. However, I am receiving the following error:

    Compile error: expected function or variable and the OpenQuery is highlight in my code.  Can anyone assist me?

    Private Sub cmdCreateProjectNumber_Click()
    On Error GoTo ErrorHandler
    Dim strNewID As String
    Dim Msg, Style, Title As String
    Msg = "Verify You wish to used this number!"  
    Style = vbYesNo + vbDefaultButton2            
    Title = "New Project ID Number"               
    Set strNewID = DoCmd.OpenQuery("CreateProjectNumber", , acReadOnly)
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
        Me!ID = strNewID
        End Sub
    End If
        Exit Function
        Resume ExitProcedure
    End Sub

    Friday, August 21, 2020 3:54 PM

All replies

  • make your query 'CreateProjectNumber' to be an aggregate query that returns 1 record which is the max of the ID...let's call this field: MaxID

    change this:

    Set strNewID = DoCmd.OpenQuery("CreateProjectNumber", , acReadOnly)

    to this:

    strNewID = DLookUp("MaxID","CreateProjectNumber")

    strNewID = strNewID + 1

    this will get you your new new ID value.... all the rest on how it is presented via message box or whatever is a different topic...

    Friday, August 21, 2020 4:16 PM
  • I'd use DMax instead of a query. For example

    Public Function Test_code()
        Dim varNewID As Variant
        varNewID = DMax("ID", "Employees") + 1
        Debug.Print varNewID
    End Function

    By the way, you're error handler has a problem, too. the ExitProceedure should be Exit Sub, not Exit Function

    Bill Mosca



    Friday, August 21, 2020 4:16 PM
  • Just 

    DoCmd.OpenQuery("CreateProjectNumber", , acReadOnly)

    Delete Set strNewID=

    Friday, August 21, 2020 4:17 PM
  • yeah change mine to bill's DMax - I like that better...
    Friday, August 21, 2020 4:18 PM
  • You might like to take a look at CustomNumber.zip in my public databases folder at:


    This little demo file illustrates a number of methods for generating sequential numbers in a variety of contexts.  The option for 'Sequential Numbering' should suit your requirements.  The method used here is an extension of that popularised by Roger Carlson, a key feature of which is that it handles and resolves any conflicts in a multi-user environment, where two or more users might be inserting a new record simultaneously.

    My demo extends Roger's original method by allowing for the next number to be 'seeded' and/or the amount by which each increment is made can be changed from the default of 1.

    Ken Sheridan, Stafford, England

    Friday, August 21, 2020 5:05 PM