Solved

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

Posted on 2011-02-24
26
325 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
  • 12
  • 11
  • +2
26 Comments
 
LVL 38

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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now