• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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_#"
0
dsheridan
Asked:
dsheridan
  • 12
  • 11
  • +2
1 Solution
 
Aaron TomoskyTechnology ConsultantCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
dsheridanAuthor Commented:
We could have this db will only be used one person at a time.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DockieBoyCommented:
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.

0
 
DockieBoyCommented:
Oh, sorry, after the * 1000 you need to add  "+ 1", otherwise your result on your new year numbers will start at 000
0
 
dsheridanAuthor Commented:
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).
0
 
DockieBoyCommented:
There is indeed, it all just depends on when you want to get the result?
0
 
dsheridanAuthor Commented:
When I press the New Record button
0
 
DockieBoyCommented:
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.
0
 
dsheridanAuthor Commented:
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

0
 
dsheridanAuthor Commented:
When I press the button I get:

Syntax error in date in query expression 'Max(LIR_LIR_#)'.
0
 
DockieBoyCommented:
Hmm.  In your table are you storing just the numbers or are you storing the "MN" part as well?
0
 
dsheridanAuthor Commented:
Storing the entire MN-11-001 string.  I have placed a [ ] around the LIRi_LIR_# field and now I get a type mismatch
0
 
dsheridanAuthor Commented:
Name = LIRi_LIR_#
Type = Text
Field Size = 9
Input Mask = "MN-11-"999;0;_
0
 
DockieBoyCommented:
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.

0
 
dsheridanAuthor Commented:
If its easier, I can change to a number field.
0
 
dsheridanAuthor Commented:
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?
0
 
DockieBoyCommented:
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
0
 
DockieBoyCommented:
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
0
 
dsheridanAuthor Commented:
Syntax error in date in query expression 'Max(LIR_LIR_#)'.
0
 
DockieBoyCommented:
Really?  Works for me, lol.

can you upload the db?
0
 
dsheridanAuthor Commented:
0
 
trbazeCommented:
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

0
 
DockieBoyCommented:
Sorry, you need [] around your field and table name in the Dmax code.

eg.

MyMax = Nz(DMax("[LIR_LIR_#]", "[LIR_Master]"), "MN-00-000")
0
 
dsheridanAuthor Commented:
Great, that worked DockieBoy!!!! Thank you
0
 
DockieBoyCommented:
Welcome
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 12
  • 11
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now