Solved

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

Posted on 2011-02-24
26
334 Views
Last Modified: 2012-05-11
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
Comment
Question by:dsheridan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
  • +2
26 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 34970673
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
 
LVL 84
ID: 34972106
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
 

Author Comment

by:dsheridan
ID: 34972381
We could have this db will only be used one person at a time.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 3

Expert Comment

by:DockieBoy
ID: 34974085
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
 
LVL 3

Expert Comment

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

Author Comment

by:dsheridan
ID: 34974300
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
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34974468
There is indeed, it all just depends on when you want to get the result?
0
 

Author Comment

by:dsheridan
ID: 34974484
When I press the New Record button
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34974558
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
 

Author Comment

by:dsheridan
ID: 34974581
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
 

Author Comment

by:dsheridan
ID: 34974610
When I press the button I get:

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

Expert Comment

by:DockieBoy
ID: 34974642
Hmm.  In your table are you storing just the numbers or are you storing the "MN" part as well?
0
 

Author Comment

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

Author Comment

by:dsheridan
ID: 34974673
Name = LIRi_LIR_#
Type = Text
Field Size = 9
Input Mask = "MN-11-"999;0;_
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34974750
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
 

Author Comment

by:dsheridan
ID: 34974768
If its easier, I can change to a number field.
0
 

Author Comment

by:dsheridan
ID: 34974814
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
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34975065
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
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34975096
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
 

Author Comment

by:dsheridan
ID: 34975206
Syntax error in date in query expression 'Max(LIR_LIR_#)'.
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34975230
Really?  Works for me, lol.

can you upload the db?
0
 

Author Comment

by:dsheridan
ID: 34975268
0
 
LVL 3

Expert Comment

by:trbaze
ID: 34975338
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
 
LVL 3

Accepted Solution

by:
DockieBoy earned 500 total points
ID: 34975559
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
 

Author Comment

by:dsheridan
ID: 34975627
Great, that worked DockieBoy!!!! Thank you
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34975668
Welcome
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question