troy_lee@comcas t.net wrote:
On Jun 27, 4:43 pm, Salad <o...@vinegar.c omwrote:
>
>
>
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?
>
>
>>troy_...@comc ast.net wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>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
>>
>>>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.
>>>>>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.
>>>>>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?
>>>>>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.
>>>>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.
>>>>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
>>> 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
>>> 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.
>>> 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
>>>>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.
>>>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?
>>>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?
>
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
Thanks again.
>
Troy Lee
>
Troy Lee
Leave a comment: