Calculate by an ID field from last record in a table

[vad77]

I have a table called ltbl_Contractors and in that table there is a field called Index. It is not an autonumber field and the reason is that my data already had this data in the spreadsheet.


On my form for my contractor table I want to create a button that does the following...
Look as the index field of the last record on the contractor table. (It is sorted numericlly)
Take that value and add 1
On my form set the index field on the new record to the calculation result.
Since my form is sorted on a date field I can't not just look at the last record to get my value.
If my request is a little out there I welcome any suggestions to be able to determine my next index number.
Thanks in adavance for your replies!!!

Vito

[orange]

Dmax(Index) +1

[vad77]

@Orange how would I use that line of code on a command button?

[June7]

@Orange - mixing apples and oranges! Not DMax with that syntax.

Generating a custom unique identifier is common topic. Start with https://www.accessforums.net/access/...nce-40410.html

[orange]

I think this should work

Update tbl_Contractors 
Set [Index] = DMax("[Index]","tbl_Contractors") + 1;

NOTE: Index is an extremely poor choice for a field name. Index has special meaning in database terms.

[MarvinM]

Hey Vito,

I came upon the same issue a few months ago. I want to give you a little something to consider. Is this a table that will be accessed by more than one user at a time? (Mine is.) The problem that comes up is this:
The last record was #17.
User A starts to create a new record. The function checks the last record and determines that the next record will be #18.
User B starts to create a new record. The function checks the last record and determines that the next record will be #18.
User A saves their record, #18.
User B saves their record, #18, and gets a conflict.

If there is only one user, no problem. But if you have a multi-user environment, you'll need to take extra precautions.

Here is a code snippet that might help:

Dim numNewRec As Double

' Determine the new record number.
' Add 1 to the highest Rec_Num in the database.
numNewRec = DMax("Rec_Num", "Main")+ 1
RecNum.Value = numNewRec

I use Double because my Record Numbers are 8 digits. RecNum is a control on my New Record form. I populate the control with the new record number at the beginning of the Save button Click subroutine. It is immediately written into the Main table so that there is virtually no time for a second user to claim the same new Record Number. A couple of milliseconds, maybe, but not enough to worry about.

HTH,
Marvin M

[vad77]

@MarvinM thanks for the response and to answer your question I am the only one using this database. I used your code with my field name Index and my table name of ltbl_Contractor_table and it works perfectly.
Thanks for all your repsonses, another one my questions answered. I know I will have more

Vito

[MarvinM]

Vito,

I've been working with Access over 20 years and I still have questions (almost daily). We're happy to help each other out.
One guy that is really helpful is Allen Browne, http://allenbrowne.com/. His sample code has gotten me out of a lot of jams.

_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
-------------------------------------------------------------------------------------------------------------------
If this post has helped you, please click on the little sheriff badge in the lower left corner. Thanks!
-------------------------------------------------------------------------------------------------------------------

[vad77]

Thanks I just book marked that page. I also found a link on this forum to a site called DataPig which has videos. Even though they are old the principal is the same and I have gotten some good instructions from there also.