Custom Autonumber Incrementation Question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • troy_lee@comcast.net
    #1

    Custom Autonumber Incrementation Question

    I have a table that has a PK field with the following format:
    Dyymm123. So that, a typical number might look like this D0806270.

    The first character is literal and never changes. The next four digits
    are derived by the year and month. The final three digits are the
    sequential numbers given to units throughout the year. In the above
    example, 270 would simply be the 270th issuance for this entire year.
    The last three digits reset to 001 on the new year.

    My question is what is the best way to automatically increment this
    number so that the yy and mm numbers change with the calendar and the
    last three digits increment by one each time an employee needs a new
    number? And then, how do I reset the last three digits on the change
    of calendar year?

    Thanks for the help.

    Troy Lee
    Tags: None
    • Salad
      #2
      Re: Custom Autonumber Incrementation Question

      troy_lee@comcas t.net wrote:
      I have a table that has a PK field with the following format:
      Dyymm123. So that, a typical number might look like this D0806270.
      >
      The first character is literal and never changes. The next four digits
      are derived by the year and month. The final three digits are the
      sequential numbers given to units throughout the year. In the above
      example, 270 would simply be the 270th issuance for this entire year.
      The last three digits reset to 001 on the new year.
      >
      My question is what is the best way to automatically increment this
      number so that the yy and mm numbers change with the calendar and the
      last three digits increment by one each time an employee needs a new
      number? And then, how do I reset the last three digits on the change
      of calendar year?
      >
      Thanks for the help.
      >
      Troy Lee
      Does it really need to be the PK? Why not use an autonumber as the PK
      instead? You can always create the key you want for display purposes.

      I would never create the key you mentioned above, if you don't want
      sequence breaks, until a new record is saved. Reason, what happens if
      you have person A go into a new record (key is generated) and person B
      goes into a new record, and person A cancels the add.

      You could break the key into secions:
      1st field char is status type
      2nd field is a date field
      3rd field is sequence

      You could then make sequence when you add the record
      Me.Sequence = Dmax("Sequence" ,"TableName" ,_
      "Year(DateField Name) = Year(date)") + 1

      You can then display the value as
      DisplayID : Status & Format(DateFiel d,"yymm") & Sequence
      in a query. Use the autnonumber as a link to other tables.

      If you absolutely need to make it an id type field, do it in the
      BeforeUpdate event of your form.
      If Me.NewRecord then
      Dim strSQL As String
      Dim rst As DAO.Recordset
      strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
      "FROM TableName " & _
      "WHERE Mid(Id,2,2) = Format(Date,""y y"")"
      set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
      Me.ID = "D" & Format(Date,"yy mm") & NZ(rst!Expr1,0) + 1
      rst.close
      set rst = nothing
      Endif

      Kusha Las Payas
      http://www.youtube.com/watch?v=0wzKT_TEtOQ
      http://www.youtube.com/watch?v=0wzKT_TEtOQ

        Comment

        • troy_lee@comcast.net
          #3
          Re: Custom Autonumber Incrementation Question

          On Jun 27, 1:49 pm, Salad <o...@vinegar.c omwrote:
          troy_...@comcas t.net wrote:
          I have a table that has a PK field with the following format:
          Dyymm123. So that, a typical number might look like this D0806270.
          >
          The first character is literal and never changes. The next four digits
          are derived by the year and month. The final three digits are the
          sequential numbers given to units throughout the year. In the above
          example, 270 would simply be the 270th issuance for this entire year.
          The last three digits reset to 001 on the new year.
          >
          My question is what is the best way to automatically increment this
          number so that the yy and mm numbers change with the calendar and the
          last three digits increment by one each time an employee needs a new
          number? And then, how do I reset the last three digits on the change
          of calendar year?
          >
          Thanks for the help.
          >
          Troy Lee
          >
          Does it really need to be the PK? Why not use an autonumber as the PK
          instead? You can always create the key you want for display purposes.
          >
          I would never create the key you mentioned above, if you don't want
          sequence breaks, until a new record is saved. Reason, what happens if
          you have person A go into a new record (key is generated) and person B
          goes into a new record, and person A cancels the add.
          >
          You could break the key into secions:
          1st field char is status type
          2nd field is a date field
          3rd field is sequence
          >
          You could then make sequence when you add the record
          Me.Sequence = Dmax("Sequence" ,"TableName" ,_
          "Year(DateField Name) = Year(date)") + 1
          >
          You can then display the value as
          DisplayID : Status & Format(DateFiel d,"yymm") & Sequence
          in a query. Use the autnonumber as a link to other tables.
          >
          If you absolutely need to make it an id type field, do it in the
          BeforeUpdate event of your form.
          If Me.NewRecord then
          Dim strSQL As String
          Dim rst As DAO.Recordset
          strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
          "FROM TableName " & _
          "WHERE Mid(Id,2,2) = Format(Date,""y y"")"
          set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
          Me.ID = "D" & Format(Date,"yy mm") & NZ(rst!Expr1,0) + 1
          rst.close
          set rst = nothing
          Endif
          >
          Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_T EtOQ
          Thank you Salad for another great response.

          I would never make this the PK either; the original developer did, so
          all of the tables are linked through this PK for the entire db. In
          addition, only one person in the office will ever create a new
          issuance, thus no fear of simultaneous creations of a new number.

          I love the idea of breaking it up. I will give that a try. My only
          concern is having to "retrofit" all the historical records when I am
          through with the new application, so this may become a big problem
          down the road. Any thoughts there?

            Comment

            • Salad
              #4
              Re: Custom Autonumber Incrementation Question

              troy_lee@comcas t.net wrote:
              On Jun 27, 1:49 pm, Salad <o...@vinegar.c omwrote:
              >
              >>troy_...@comc ast.net wrote:
              >>
              >>>I have a table that has a PK field with the following format:
              >>>Dyymm123. So that, a typical number might look like this D0806270.
              >>
              >>>The first character is literal and never changes. The next four digits
              >>>are derived by the year and month. The final three digits are the
              >>>sequential numbers given to units throughout the year. In the above
              >>>example, 270 would simply be the 270th issuance for this entire year.
              >>>The last three digits reset to 001 on the new year.
              >>
              >>>My question is what is the best way to automatically increment this
              >>>number so that the yy and mm numbers change with the calendar and the
              >>>last three digits increment by one each time an employee needs a new
              >>>number? And then, how do I reset the last three digits on the change
              >>>of calendar year?
              >>
              >>>Thanks for the help.
              >>
              >>>Troy Lee
              >>
              >>Does it really need to be the PK? Why not use an autonumber as the PK
              >>instead? You can always create the key you want for display purposes.
              >>
              >>I would never create the key you mentioned above, if you don't want
              >>sequence breaks, until a new record is saved. Reason, what happens if
              >>you have person A go into a new record (key is generated) and person B
              >>goes into a new record, and person A cancels the add.
              >>
              >>You could break the key into secions:
              > 1st field char is status type
              > 2nd field is a date field
              > 3rd field is sequence
              >>
              >>You could then make sequence when you add the record
              > Me.Sequence = Dmax("Sequence" ,"TableName" ,_
              > "Year(DateField Name) = Year(date)") + 1
              >>
              >>You can then display the value as
              > DisplayID : Status & Format(DateFiel d,"yymm") & Sequence
              >>in a query. Use the autnonumber as a link to other tables.
              >>
              >>If you absolutely need to make it an id type field, do it in the
              >>BeforeUpdat e event of your form.
              > If Me.NewRecord then
              > Dim strSQL As String
              > Dim rst As DAO.Recordset
              > strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
              > "FROM TableName " & _
              > "WHERE Mid(Id,2,2) = Format(Date,""y y"")"
              > set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
              > Me.ID = "D" & Format(Date,"yy mm") & NZ(rst!Expr1,0) + 1
              > rst.close
              > set rst = nothing
              > Endif
              >>
              >>Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_T EtOQ
              >
              >
              Thank you Salad for another great response.
              >
              I would never make this the PK either; the original developer did, so
              all of the tables are linked through this PK for the entire db. In
              addition, only one person in the office will ever create a new
              issuance, thus no fear of simultaneous creations of a new number.
              >
              I love the idea of breaking it up. I will give that a try. My only
              concern is having to "retrofit" all the historical records when I am
              through with the new application, so this may become a big problem
              down the road. Any thoughts there?
              Hmmm...I think the code I supplied for your form's BeforeUpdate event
              should pretty much create the key of your dreams. You only want the key
              created when its a new record value. I called your key "ID" in the
              code. Change it to your field name.

              BTW, I did'n format the resulting number to be zero padded. It should be
              Me.ID = "D" & Format(Date,"yy mm") & Format(NZ(rst!E xpr1,0) + 1,000)

              You'll also notice I prefaced your key with the letter "D". I don't
              know if it's always to be a "D". I guess you can figure out what letter
              to provide.

              You could actually make it a function.
              Private Function MakeNewKey() As String
              Dim strSQL As String
              Dim rst As DAO.Recordset

              strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
              "FROM TableName " & _
              "WHERE Mid(Id,2,2) = Format(Date,""y y"")"

              set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
              Me.ID = "D" & Format(Date,"yy mm") & & FormatNZ(rst!Ex pr1,0) + 1,"000")

              rst.close
              set rst = nothing
              End Function

              Then in your forms BeforeUpdate event simpley enter
              If Me.NewRecord then Me.ID = MakeNewKey()

              The Ketchup Song
              http://www.youtube.com/watch?v=n-Razc6_ibE
              http://www.youtube.com/watch?v=n-Razc6_ibE

                Comment

                • troy_lee@comcast.net
                  #5
                  Re: Custom Autonumber Incrementation Question

                  On Jun 27, 4:43 pm, Salad <o...@vinegar.c omwrote:
                  troy_...@comcas t.net wrote:
                  On Jun 27, 1:49 pm, Salad <o...@vinegar.c omwrote:
                  >
                  >troy_...@comca st.net wrote:
                  >
                  >>I have a table that has a PK field with the following format:
                  >>Dyymm123. So that, a typical number might look like this D0806270.
                  >
                  >>The first character is literal and never changes. The next four digits
                  >>are derived by the year and month. The final three digits are the
                  >>sequential numbers given to units throughout the year. In the above
                  >>example, 270 would simply be the 270th issuance for this entire year.
                  >>The last three digits reset to 001 on the new year.
                  >
                  >>My question is what is the best way to automatically increment this
                  >>number so that the yy and mm numbers change with the calendar and the
                  >>last three digits increment by one each time an employee needs a new
                  >>number? And then, how do I reset the last three digits on the change
                  >>of calendar year?
                  >
                  >>Thanks for the help.
                  >
                  >>Troy Lee
                  >
                  >Does it really need to be the PK? Why not use an autonumber as the PK
                  >instead? You can always create the key you want for display purposes.
                  >
                  >I would never create the key you mentioned above, if you don't want
                  >sequence breaks, until a new record is saved. Reason, what happens if
                  >you have person A go into a new record (key is generated) and person B
                  >goes into a new record, and person A cancels the add.
                  >
                  >You could break the key into secions:
                  1st field char is status type
                  2nd field is a date field
                  3rd field is sequence
                  >
                  >You could then make sequence when you add the record
                  Me.Sequence = Dmax("Sequence" ,"TableName" ,_
                  "Year(DateField Name) = Year(date)") + 1
                  >
                  >You can then display the value as
                  DisplayID : Status & Format(DateFiel d,"yymm") & Sequence
                  >in a query. Use the autnonumber as a link to other tables.
                  >
                  >If you absolutely need to make it an id type field, do it in the
                  >BeforeUpdate event of your form.
                  If Me.NewRecord then
                  Dim strSQL As String
                  Dim rst As DAO.Recordset
                  strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
                  "FROM TableName " & _
                  "WHERE Mid(Id,2,2) = Format(Date,""y y"")"
                  set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
                  Me.ID = "D" & Format(Date,"yy mm") & NZ(rst!Expr1,0) + 1
                  rst.close
                  set rst = nothing
                  Endif
                  >
                  >Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_T EtOQ
                  >
                  Thank you Salad for another great response.
                  >
                  I would never make this the PK either; the original developer did, so
                  all of the tables are linked through this PK for the entire db. In
                  addition, only one person in the office will ever create a new
                  issuance, thus no fear of simultaneous creations of a new number.
                  >
                  I love the idea of breaking it up. I will give that a try. My only
                  concern is having to "retrofit" all the historical records when I am
                  through with the new application, so this may become a big problem
                  down the road. Any thoughts there?
                  >
                  Hmmm...I think the code I supplied for your form's BeforeUpdate event
                  should pretty much create the key of your dreams. You only want the key
                  created when its a new record value. I called your key "ID" in the
                  code. Change it to your field name.
                  >
                  BTW, I did'n format the resulting number to be zero padded. It should be
                  Me.ID = "D" & Format(Date,"yy mm") & Format(NZ(rst!E xpr1,0) + 1,000)
                  >
                  You'll also notice I prefaced your key with the letter "D". I don't
                  know if it's always to be a "D". I guess you can figure out what letter
                  to provide.
                  >
                  You could actually make it a function.
                  Private Function MakeNewKey() As String
                  Dim strSQL As String
                  Dim rst As DAO.Recordset
                  >
                  strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
                  "FROM TableName " & _
                  "WHERE Mid(Id,2,2) = Format(Date,""y y"")"
                  >
                  set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
                  Me.ID = "D" & Format(Date,"yy mm") & & FormatNZ(rst!Ex pr1,0) + 1,"000")
                  >
                  rst.close
                  set rst = nothing
                  End Function
                  >
                  Then in your forms BeforeUpdate event simpley enter
                  If Me.NewRecord then Me.ID = MakeNewKey()
                  >
                  The Ketchup Songhttp://www.youtube.com/watch?v=n-Razc6_ibE
                  WOW! Thanks for the insight.

                  One more question. Will this automatically reset the counter at the
                  turn of the year to where the last three digits reset to 001?

                  Thanks again.

                  Troy Lee

                    Comment

                    • Salad
                      #6
                      Re: Custom Autonumber Incrementation Question

                      troy_lee@comcas t.net wrote:
                      On Jun 27, 4:43 pm, Salad <o...@vinegar.c omwrote:
                      >
                      >>troy_...@comc ast.net wrote:
                      >>
                      >>>On Jun 27, 1:49 pm, Salad <o...@vinegar.c omwrote:
                      >>
                      >>>>troy_...@co mcast.net wrote:
                      >>
                      >>>>>I have a table that has a PK field with the following format:
                      >>>>>Dyymm123 . So that, a typical number might look like this D0806270.
                      >>
                      >>>>>The first character is literal and never changes. The next four digits
                      >>>>>are derived by the year and month. The final three digits are the
                      >>>>>sequenti al numbers given to units throughout the year. In the above
                      >>>>>example, 270 would simply be the 270th issuance for this entire year.
                      >>>>>The last three digits reset to 001 on the new year.
                      >>
                      >>>>>My question is what is the best way to automatically increment this
                      >>>>>number so that the yy and mm numbers change with the calendar and the
                      >>>>>last three digits increment by one each time an employee needs a new
                      >>>>>number? And then, how do I reset the last three digits on the change
                      >>>>>of calendar year?
                      >>
                      >>>>>Thanks for the help.
                      >>
                      >>>>>Troy Lee
                      >>
                      >>>>Does it really need to be the PK? Why not use an autonumber as the PK
                      >>>>instead? You can always create the key you want for display purposes.
                      >>
                      >>>>I would never create the key you mentioned above, if you don't want
                      >>>>sequence breaks, until a new record is saved. Reason, what happens if
                      >>>>you have person A go into a new record (key is generated) and person B
                      >>>>goes into a new record, and person A cancels the add.
                      >>
                      >>>>You could break the key into secions:
                      >>> 1st field char is status type
                      >>> 2nd field is a date field
                      >>> 3rd field is sequence
                      >>
                      >>>>You could then make sequence when you add the record
                      >>> Me.Sequence = Dmax("Sequence" ,"TableName" ,_
                      >>> "Year(DateField Name) = Year(date)") + 1
                      >>
                      >>>>You can then display the value as
                      >>> DisplayID : Status & Format(DateFiel d,"yymm") & Sequence
                      >>>>in a query. Use the autnonumber as a link to other tables.
                      >>
                      >>>>If you absolutely need to make it an id type field, do it in the
                      >>>>BeforeUpdat e event of your form.
                      >>> If Me.NewRecord then
                      >>> Dim strSQL As String
                      >>> Dim rst As DAO.Recordset
                      >>> strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
                      >>> "FROM TableName " & _
                      >>> "WHERE Mid(Id,2,2) = Format(Date,""y y"")"
                      >>> set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
                      >>> Me.ID = "D" & Format(Date,"yy mm") & NZ(rst!Expr1,0) + 1
                      >>> rst.close
                      >>> set rst = nothing
                      >>> Endif
                      >>
                      >>>>Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_T EtOQ
                      >>
                      >>>Thank you Salad for another great response.
                      >>
                      >>>I would never make this the PK either; the original developer did, so
                      >>>all of the tables are linked through this PK for the entire db. In
                      >>>addition, only one person in the office will ever create a new
                      >>>issuance, thus no fear of simultaneous creations of a new number.
                      >>
                      >>>I love the idea of breaking it up. I will give that a try. My only
                      >>>concern is having to "retrofit" all the historical records when I am
                      >>>through with the new application, so this may become a big problem
                      >>>down the road. Any thoughts there?
                      >>
                      >>Hmmm...I think the code I supplied for your form's BeforeUpdate event
                      >>should pretty much create the key of your dreams. You only want the key
                      >>created when its a new record value. I called your key "ID" in the
                      >>code. Change it to your field name.
                      >>
                      >>BTW, I did'n format the resulting number to be zero padded. It should be
                      >>Me.ID = "D" & Format(Date,"yy mm") & Format(NZ(rst!E xpr1,0) + 1,000)
                      >>
                      >>You'll also notice I prefaced your key with the letter "D". I don't
                      >>know if it's always to be a "D". I guess you can figure out what letter
                      >>to provide.
                      >>
                      >>You could actually make it a function.
                      >>Private Function MakeNewKey() As String
                      > Dim strSQL As String
                      > Dim rst As DAO.Recordset
                      >>
                      > strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
                      > "FROM TableName " & _
                      > "WHERE Mid(Id,2,2) = Format(Date,""y y"")"
                      >>
                      > set rst = currentdb.openr ecordset(strsql ,dbopensnapshot )
                      > Me.ID = "D" & Format(Date,"yy mm") & & FormatNZ(rst!Ex pr1,0) + 1,"000")
                      >>
                      > rst.close
                      > set rst = nothing
                      >>End Function
                      >>
                      >>Then in your forms BeforeUpdate event simpley enter
                      > If Me.NewRecord then Me.ID = MakeNewKey()
                      >>
                      >>The Ketchup Songhttp://www.youtube.com/watch?v=n-Razc6_ibE
                      >
                      >
                      WOW! Thanks for the insight.
                      >
                      One more question. Will this automatically reset the counter at the
                      turn of the year to where the last three digits reset to 001?
                      >
                      I guess the best way to see if it resets is to test it out. Make a copy
                      of your front/back end. Link the tables in the front end copy to the
                      backend copy. Now change your computer's date to 1/1/2009. Then add a
                      record and see if it works. Since it's going by the system date, I
                      expect it would change from 08 to 09. After the test, change your
                      calendar/system date back.

                      Lambada
                      http://www.youtube.com/watch?v=5AfTl5Vg73A
                      http://www.youtube.com/watch?v=5AfTl5Vg73A



                      Thanks again.
                      >
                      Troy Lee

                        Comment

                        Previous template Next
                        Working...