Results 1 to 5 of 5
  1. #1
    NickTheG is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    3

    Daily Reset Sequential Numbering Using Dmax.



    Scenario
    I have two tables. Table 1 is for a job and table 2 is for tasks completed on that job. I am looking at not using the auto number and currently has work a system were a ID is genereated based on date i.e 20170921 - 1 were the "-1" increments for every job and these number resets everyday . I currently have this working but resets every year. Also In Table two I want this linked to Table 1 but have an ID that increments 1,2,3,4 based on job so that it resets when a second job is entered.

    I am looking to change the code below so that it does no reset the sequential number every year but resets everyday. I have been trying to look at the bottom codes but cannot get the numbering to reset daily or if the date does not appear in a table. Could i please request so help on modifying the following code. I am a relative beginner to access database and VBA so I may be looking at the wrong implementation. also would be greateful at any help for sequential numbering bases on a sub form.

    Table 1
    ID
    Request ID
    Date of Request
    Item Subject to change
    Change category
    item reference
    justification for change
    approval for change
    rtest
    yearid
    monthid
    dayID
    BaseID
    BCustID
    FormatID


    First Code

    'If IsNull(Me![BaseID]) Then
    'Me![BaseID] = Nz(DMax("[BaseID]", "[tblChangeManagement]", "[YearID]='" & Year(Date) & "'"), 0) + 1
    'End If
    'Me![CustID] = [FormatID] & [YearID] & [MonthID] & [DayID] & "-" & Format([BaseID], "00000")
    'End If

    Second Code

    If DLookup("DateOfRequest", "tblChangeManagement", Date) Then

    Me.[BaseID] = Format(Nz(DMax("[BaseID]", "[tblChangeManagement]", "[YearID]='" & Year(Date) & "'"), 0) + 1)

    Else
    Me.[BaseID] = 1

    End If

    Me![CustID] = [FormatID] & [YearID] & [MonthID] & [DayID] & "-" & Format([BaseID], "00000")
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a query to pull the [RWO] and the bare date values in the RWO. (left 8, no day#)

    in the query: qsDateVals,
    SELECT [RWO],(Left([RWO],8)) AS DateVal FROM tData;

    Now when you need to make a new work order,
    get the MAX value for THAT date,
    then either start from 1 (if null) or add +1 if this day exists:

    Code:
    btnAddNewOrder_click()
    dim vDate, vRet, vNum, vOrder
    dim i as integer
    
    vDate = FORMAT(date,"yyyymmdd")
    vRet = DMax("[RWO]","qsDateVals","[DateVal]=" & vDate)   'find the max entry for the day
    
    if isNull(vRet) then
       vOrder = vDate & "-01"
    else
       i = instr(vRet,"-")
       vNum = mid(vRet,i+1)
       vNum = val(vNum) + 1
       vOrder = vDate & "-" & format(vNum,"00")
    endif
    
    txtOrderNum = vOrder     'fill in new order# in the textbox
    end sub

  3. #3
    NickTheG is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    3
    What does RWO refer to sorry. I cannot seem to gt it passed the first part of the if statement. As it will always give the value I set were its "-01"

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    whatever key field you need it to be.
    mine is RWO.

  5. #5
    NickTheG is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    3
    Apologies I didn't get your first statement make a query to pull the [RWO] and the bare date values in the RWO. Thankyou in advance for replying and being a massive help.

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

Similar Threads

  1. ON TIMER -Always On OK - How to Reset Daily?
    By meg9222 in forum Programming
    Replies: 1
    Last Post: 08-19-2013, 04:39 PM
  2. Replies: 5
    Last Post: 10-15-2012, 12:18 AM
  3. Sequential Numbering in a Form
    By Falafa in forum Forms
    Replies: 6
    Last Post: 10-05-2012, 08:20 AM
  4. Replies: 2
    Last Post: 07-27-2012, 09:07 AM
  5. Help with Sequential Numbering
    By orion in forum Programming
    Replies: 3
    Last Post: 07-06-2009, 01:41 PM

Tags for this Thread

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