Need help with VBA code for a simple function

[msmithtlh]

I have a number field (not an autonumber) that I want to increment by 1 when the user creates a new record. I keep having trouble with vba for this.

I have written pseudo code in comments, but can't seem to find the correct way to do it in VBA.



Private Sub cmdNewbtn_Click()

Dim rst As Recordset
Dim newrecnum As Long
Dim tempnum As Long

'Find last record (table = tblCongregationTeams)

'Store TeamID + 1 to tempnum

'Find new record


'Store tempnum to tblCongregationTeams.TeamID



End Sub


Thanks!!!

[CJ_London]

use what is commonly called 'dmax+1'

tempnum=dmax("TeamID","tblCongregationTeams")+1

If your app is a multi user app then this must be done in the form beforeupdate event otherwise there is a risk of two users getting the same number if doing this at the same time

[Welshgasman]

I would not call it TeamID either? ID is normally associated with Autonumber. TeamNumber perhaps?

[msmithtlh]

Thanks, CJ London.
I also need vba syntax for these:

'Find last record (table = tblCongregationTeams)

'Store TeamID + 1 to tempnum

'Find new record

[Bob Fitz]

As others have already stated, you can use the Dax() function to get the largest value and then add 1 to it.
There are hundreds of pages on the required syntax, for example: https://support.microsoft.com/en-us/...b-c554053d299f
Give it a go and post back with your code if you get stuck.

[ssanfu]

Finding the "Last" record is very subjective. The "Last" record is dependent on the sort order.
The "Last" record if sorted (ordered) by "Last Name" will be different if the sort order is on a date and will be different if sorted by the (autonumber) PK field.
Note that a table has NO inherent order. It is a "bit bucket".