asked on

New Record ID numbering question (cannot find example, need help)

Upon a new record button click I want to auto create the next ID number that is formatted as
MN-xx-xxx (MN-2 digit Calendar Year-3 digit incremented number)
I have a format of "MN-11-"999;0;_;;_ to give me the correct format, but I need now to have it autoselect the year and the the next number.

Next criteria is at the end of the year (say the last id was MN-11-578, at the next year I need it to start at MN-12-001.

This field is the primary key, but is not an autonumber field.

Table name is "LIR_Master" and field is "LIR_LIR_#"

Aaron Tomosky

The general idea is to take the highest number that already exists, and add one to it. You know the mn and you know the year, so Lookup the highest number for thus year, add 1, there's ya go.

I'm sure someone else will give you a copy paste solution. I just wanted to lay out the logic incase you wanted to do it yourself.

Scott McDaniel (EE MVE )

Are there multiple users on the database? If so, then you can run into issues with this unless you're very careful how and when you get that "next" number.

Dan Sheridan

ASKER

We could have this db will only be used one person at a time.

DockieBoy

Try this,

Dim MyMax As Long
MyMax = Nz(DMax("LIR_LIR_#", "LIR_Master"), 0)
If Left(MyMax, 2) = Right(DatePart("yyyy", Date), 2) Then
Me.MyControl = MyMax + 1
Else
Me.MyControl = Right(DatePart("yyyy", Date), 2) * 1000
End If
End Sub

Substitute MyControl for the name of the control that you want to display the data in.

DockieBoy

Oh, sorry, after the * 1000 you need to add "+ 1", otherwise your result on your new year numbers will start at 000

Dan Sheridan

ASKER

Looking at the code, is there a line missing at the top?

Am I placing this against the Public Sub or the form (On Current).

DockieBoy

There is indeed, it all just depends on when you want to get the result?

Dan Sheridan

ASKER

When I press the New Record button

DockieBoy

If you are using a bound form;

Use the "After Insert" event trigger.

Or if it is an unbound form or you have made your own button to create a new record, put the code in the buttons " On Click" event trigger.

Dan Sheridan

ASKER

Here is my code that I have pasted. Will this give me the format of MN-YY-999?

Private Sub cmdNewRec_Click()
On Error GoTo Err_cmdNewRec_Click

    DoCmd.GoToRecord , , acNewRec
    LIRi_Num.SetFocus
    Dim MyMax As Long
         MyMax = Nz(DMax("LIR_LIR_#", "LIR_Master"), 0)
        If Left(MyMax, 2) = Right(DatePart("yyyy", Date), 2) Then
         Me.LIRi_Num = MyMax + 1
        Else
         Me.LIRi_Num = Right(DatePart("yyyy", Date), 2) * 1000 + 1
        End If
    LIRi_Type.SetFocus
        
Exit_cmdNewRec_Click:
    Exit Sub

Err_cmdNewRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdNewRec_Click
    
End Sub

Open in new window

Dan Sheridan

ASKER

When I press the button I get:

Syntax error in date in query expression 'Max(LIR_LIR_#)'.

DockieBoy

Hmm. In your table are you storing just the numbers or are you storing the "MN" part as well?

Dan Sheridan

ASKER

Storing the entire MN-11-001 string. I have placed a [ ] around the LIRi_LIR_# field and now I get a type mismatch

Dan Sheridan

ASKER

Name = LIRi_LIR_#
Type = Text
Field Size = 9
Input Mask = "MN-11-"999;0;_

DockieBoy

The problem is not the [], which needs to be left out anyhow, the problem is trying to use the DMax function on a text field.

Normally I would store only the numbers and then display the other data on forms and reports as necessary, eg field in table stores "11001" control on form displays "MN-11-001".

Let me see if I can modify this for you.

Dan Sheridan

ASKER

If its easier, I can change to a number field.

Dan Sheridan

ASKER

If I change to Number how do I change the Input Mask 99-999;0;_ and then do I use Format property to format it in the MN-yy-999?

DockieBoy

Keep it as is and change the code to the following;

Dim MyMax As String
Dim Mytemp As String
MyMax = Nz(DMax("LIR_LIR_#", "LIR_Master"), "MN-00-000")
If Mid(MyMax, 4, 2) = Right(DatePart("yyyy", Date), 2) Then
Mytemp = Mid(MyMax, 4, 2) & Mid(MyMax, 7, 3)
Mytemp = Val(Mytemp) + 1
Me.MyTextBox = "MN-" & Mid(Mytemp, 1, 2) & "-" & Mid(Mytemp, 3, 3)
Else
Me.MyTextBox = "MN-" & Right(DatePart("yyyy", Date), 2) & "-001"
End If

DockieBoy

Set the text box control properties as follows if you will always calculate this value and the user will not be entering the data for it.

Enabled = No
Locked = Yes

No need for input mask or format

Dan Sheridan

ASKER

Syntax error in date in query expression 'Max(LIR_LIR_#)'.

DockieBoy

Really? Works for me, lol.

can you upload the db?

Dan Sheridan

ASKER

LIRdb.mdb

trbaze

You could try this code(You will need to create two global variables- strp1 and strp2):

Function ParseFirstComp(pValue) As String

    Dim LPosition As Integer

    'Find postion of underscore
    LPosition = 3 + InStr(pValue, "-")

    'Return the portion of the string before the underscore
    If LPosition > 5 Then
        strP1 = Left(pValue, LPosition - 1)
    Else
        strP1 = ""
    End If

End Function

Function ParseSecondComp(pValue) As String
    
    Dim LPosition As Integer

    'Find postion of underscore
    LPosition = 3 + InStr(pValue, "-")

    'Return the portion of the string after the underscore
    If LPosition > 5 Then
        strP2 = Mid(pValue, LPosition + 1)
    Else
        strP2 = ""
    End If

End Function

Private Sub Command0_Click()
Dim stryear As String
Dim rs As DAO.Recordset
stryear = Format(Date, "yy")  'Grab two-digit year



Set rs = CurrentDb.OpenRecordset("SELECT Table1.ID FROM Table1 WHERE(((Table1.ID)Like 'MN-" & stryear & "-*'));")

If rs.BOF And rs.EOF Then   'No record exists
    With rs
        .AddNew
        ![ID] = "MN-" & stryear & "-000"
        .Update
    End With
Else        'Record exists
    With rs
        .MoveLast
        ParseFirstComp (![ID])
        ParseSecondComp (![ID])
        .AddNew
        ![ID] = strP1 & "-" & Format((strP2 + 1), "000")
        .Update
    End With
End If
rs.Close
Set rs = Nothing
    
    
        

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

DockieBoy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dan Sheridan

ASKER

Great, that worked DockieBoy!!!! Thank you

DockieBoy

Welcome