Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    56

    Unanswered: Assigning Sequential Number in Access 2013

    In Microsoft Access 2013 I need to assign a sequential Project Number for each new record in one table that looks like this 00213001, based on selection of a site (002) from another table + 13 (for 2013) + increment by one with each new project. There are six different sites and the 13 will change in 2014 to 14.


    I believe that I need to update two fields,, the Project Table where I am adding a record and then the site table to indicate the last number used for that site....is that correct and how do I do that?
    Last edited by dhenderson; 05-30-13 at 10:42.

  2. #2
    Join Date
    Nov 2010
    Posts
    84

  3. #3
    Join Date
    May 2013
    Posts
    56
    I can't see enough of your database to understand how to update the one table with the last used Project Number based on the site and then add a record to the Project Table with the new number.

    Can you explain how to create and execute the query

  4. #4
    Join Date
    Nov 2010
    Posts
    84
    It is possible that the example is not entirely similar to your request, so I do an example that just gives you what you want. Everything happens in the following code
    Code:
    Private Sub SiteID_AfterUpdate()
        Year = DMax("[Year]", "Year")
        ID = Nz(DMax("[ID]", "Project", "Year=" & [Year] & " AND SiteID = " & [SiteID]), 0) + 1
        ProjectID = Trim(Right(Str(Me.SiteID + 1000), 3)) & Right(Trim(Str(Me.Year)), 2) & Trim(Right(Str(10000000 + Me.ID), 4))
    End Sub
    And of course, the proposed model is where I added the Year the entity that is automatically starting Autoexec macro that runs the update query QYearAppend
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •