Add next number based on previous record SET


New Member
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
1RD - Salaries and Expenses - 2019$50,701.14
2RD - Salaries and Expenses - 2019$73,933.69
???RD - Salaries and Expenses - 2019$378,179.73



New Member
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.


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


Well-known Member
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:


New Member
I get it to work as it does return a number... the first record set will show next number as 4 as it should.
SHOULD BE 4Same_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
should be 22Different_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?


Well-known Member
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


Well-known Member
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.


New Member
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

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...