Results 1 to 7 of 7

Thread: Add next number based on previous record SET

  1. #1
    New Member
    Join Date
    May 2018
    Location
    Saint Charles, MO
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add next number based on previous record SET

    Form called Input Allotments that shows the current funds of one Description_location.
    Subform that shows all the records associated with the Description_Location: master_data_auto_fiscal_year_subform based on a Query.
    Query: Master_Data_auto_fiscal_year it has all the fields from the MasterData table

    There is a field called DOC_NO this is a number that goes up +1 as a new record is added to the set based on Form: Input Allotments field Description_Location.

    How can i get the Subform based on a Query with the Field name Doc_NO to auto fill based on last Doc_No within the same Description_location

    This is the subform based on Query
    DOC_NO Description_Location Allotments
    1 RD - Salaries and Expenses - 2019 $50,701.14
    2 RD - Salaries and Expenses - 2019 $73,933.69
    ??? RD - Salaries and Expenses - 2019 $378,179.73

  2. #2
    New Member
    Join Date
    May 2018
    Location
    Saint Charles, MO
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next number based on previous record SET

    I have found this code to do that is linked to the subform but can't seem to get it to work. What is missing... yes I'm not doing it on the main key number, the field I'm doing this on is in TEXT format called DOC_NO within the Master_Data table.

    thoughts?

    Option Compare Database
    Option Explicit
    ----------------------------------------
    Private Sub Form_Current()
    If Me.NewRecord = True Then
    Dim strOldID As String
    Dim lngCurrentNumber As Long
    Dim lngNextNumber As Long
    Dim strNextNumber As String
    Dim strNewID As String


    strOldID = DLast("[DOC_NO]", "Master_Data")
    Debug.Print strOldID


    lngCurrentName = getDigits(strOldID)
    Debug.Print lngCurrentNumber


    lngNextNumber = lngCurrentNumber + 1
    Debug.Print lngNextNumber


    strNewID = "A" & strNextNumber
    Debug.Print strNewID


    Me.DOC_NO = strNewID


    End If
    End Sub

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,703
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next number based on previous record SET

    Not sure what your problem is with the code, but here's what I wonder about:

    - you have OPTION EXPLICIT set (good) yet I see a variable that you didn't declare - lngCurrentName.
    - you show doc no as numbers yet you are processing them as text. Two potential issues with that:
    1) text values do not sort as numbers (11 comes after 1, 2 comes after 11, etc.)
    2) in an unordered list, it is unreliable to depend on Last and First (or DFirst and DLast) and a table should be considered an unordered list when mining it. Tables of records are like a bucket of marbles; there is no inherent order. Sure, if sorted in the table or if the table has an autonumber field, the table view appears ordered, but then there's no such order when using Last/First on it UNLESS you order it first. The only way to ensure that you have an ordered list is to base the search on a query that is sorted either ascending or descending - but not on a field containing numbers as text IMHO - for reasons already stated.
    - since lngCurrenttNumber isn't set to anything, it will always be 0 thus lngNextNumber will always be +1 when this runs so I don't see the point.
    - same sort of issue with strNewID

    I suspect you didn't step through this code and check the variables as you executed each line otherwise you probably would have come to the same conclusions.
    BTW "doesn't work" (or in this case, "I can't get it to work") is of no help to me to help you, and please enclose your code in code tags (# on forum toolbar) to make it easier to read.
    Last edited by Micron; Sep 13th, 2019 at 12:13 AM. Reason: correction
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  4. #4
    New Member
    Join Date
    May 2018
    Location
    Saint Charles, MO
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next number based on previous record SET

    I get it to work as it does return a number... the first record set will show next number as 4 as it should.
    DOC_NO Description_Location
    1 Same_Same
    2 Same_Same
    3 Same_Same
    SHOULD BE 4 Same_Same

    However due to this is a subform query set to Description_Location when I go to the next set of Descriptions_Location the DOC_NO will differ depending on how many entries
    DOC_NO Description_Location
    20 Different_Record_set
    21 Different_Record_set
    should be 22 Different_Record_set

    But I get the answer of 5 as it appears to be going off the 1st Description_Location rather than the current.

    Meaning I have gotten the code to pull from the right field just not based on the correct dataset (Description_Location)

    Is there a way to set not just where the data meaning query but also based on Description_Location the last entry then +1 to the next new record?[IMG]\\MOSTLOUIS3S621\Home\cynthia.cooper\Desktop\State Allotment\question.png[/IMG]

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,703
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next number based on previous record SET

    Well, so many points I raised and no comments on any of them. Between (mainly) that and the fact that I can make no sense of your bolded comment, I have to bow out. I figured I ought to try at least because there was no response after a couple of days had passed
    Good luck

  6. #6
    Board Regular
    Join Date
    May 2013
    Posts
    655
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next number based on previous record SET

    That will likely be because you are not taking the description_location into account when using DLast.?
    Use the criteria option to select the correct description_location.

    Also pay attention to Micron's comments.
    Office 2007
    Access novice. Sometimes trying to give something back

  7. #7
    New Member
    Join Date
    May 2018
    Location
    Saint Charles, MO
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add next number based on previous record SET

    Quote Originally Posted by welshgasman View Post
    That will likely be because you are not taking the description_location into account when using DLast.?
    Use the criteria option to select the correct description_location.

    Also pay attention to Micron's comments.
    Yep think you are correct not putting in description_location.... Sorry I was out sick and just got back to working on this.
    I did fixs Micron's note of lngCurrentName... it should have been lngCurrentNumber... now I get issues with the Me.DOC_NO = strNewID on last line.

    Basically I am working in a Form that has a subform. The Subform is based on a query... within the query I say to look at Searchform: Description_location. The query gives me the description_locations associated.

    Within the Form called input there is a subform called Master_Data_subform that are linked to the form with Parent and Child on description_location.
    This all works well.
    However when I want to add a record to the Subform: master_data_subform i need the last record of the subform to either copy the last record within the subform OR to take last records DOCUMENT_NO (not the ID number) and add it to the subform with an increase of 1....

    If the subform document number is 27.... the new record needs to start with 28.
    In the next set of records the subform last record maybe 100 so the next new record would be 101.

    Hope that helps.
    Again sorry for the non response out sick.. still getting over it.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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