New record numbering.

[Egg 'n' Bacon]

Hi, can one of you clever people tell me how to do the following;;

I have a standard new record button (

Code: Select all

DoCmd.GoToRecord , , acNewRec

) but what I would like is to populate the Record ID field with the next record number automatically, and set the focus to the next field i.e. [ActivityArea].

The ID field numbering format is "CO" follwed by a three digit number i.e. 001 up to 999 (I'm using;

Code: Select all

NewID: Right([ID],3)+1

as a starting point)

Any ideas how best to achieve this?

TIA

[HansV]

Is ID a number field formatted to display COnnn, or is it a text field?

[Egg 'n' Bacon]

It's formatted as Text

[HansV]

You could use code like this. I have used tblData as name of the table, and cmdNew as name of the command button:

Code: Select all

Private Sub cmdNew_Click()
    Dim strID As String
    Dim lngNum As Long
    On Error GoTo ErrHandler
    ' Save current record if necessary
    If Me.Dirty Then
        Me.Dirty = False
    End If
    strID = Nz(DMax("ID", "tblData"), "CO000")
    lngNum = Right(strID, 3)
    If lngNum = 999 Then
        MsgBox "All available IDs have been used!", vbInformation
        Exit Sub
    End If
    strID = "CO" & Format(lngNum + 1, "000")
    RunCommand acCmdRecordsGoToNew
    Me.ID = strID
    Me.ActivityArea.SetFocus
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

[Egg 'n' Bacon]

Oops!

That works a treat... but I made a mistake; TblMain is used for similar records, but are prefixed with either "RA", or "EA". So the numbering jumps a bit.

I guess the line;

Code: Select all

lngNum = Right(strID, 3)

is where the change is needed, but I don't really know what to use and syntax

[HansV]

But how do we know which prefix should be used for the new record?

[Egg 'n' Bacon]

The form is specific to the "CO" records. The other forms have different focus and not all fields are shared.

[HansV]

OK, then change the line

Code: Select all

    strID = Nz(DMax("ID", "tblData"), "CO000")

to

Code: Select all

    strID = Nz(DMax("ID", "tblMain", "ID Like 'CO*'"), "CO000")

(I assume that you use tblMain here)

[Egg 'n' Bacon]

That's the ticket!

Cheers Hans