Add next number based on previous record SET

[CLCoop]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DOC_NO[/TD]
[TD]Description_Location[/TD]
[TD]Allotments[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]RD - Salaries and Expenses - 2019[/TD]
[TD]$50,701.14[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RD - Salaries and Expenses - 2019[/TD]
[TD]$73,933.69[/TD]
[/TR]
[TR]
[TD]???[/TD]
[TD]RD - Salaries and Expenses - 2019[/TD]
[TD]$378,179.73[/TD]
[/TR]
</tbody>[/TABLE]

 

[CLCoop]

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

 

[Micron]

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.

 

[CLCoop]

I get it to work as it does return a number... the first record set will show next number as 4 as it should. [TABLE="width: 500"]
<tbody>[TR]
[TD]DOC_NO[/TD]
[TD]Description_Location[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Same_Same[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Same_Same[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Same_Same[/TD]
[/TR]
[TR]
[TD]SHOULD BE 4[/TD]
[TD]Same_Same[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]DOC_NO[/TD]
[TD]Description_Location[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Different_Record_set[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Different_Record_set[/TD]
[/TR]
[TR]
[TD]should be 22[/TD]
[TD]Different_Record_set[/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[Micron]

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

 

[welshgasman]

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.

 

[CLCoop]

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.