| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Generate Alphanumeric ID in Access Form
Using MS Access - Generate Alphanumeric ID in Access Form
Expert: Scottgem - 11/4/2009
Question
QUESTION: I found where Scottgem had helped another Access 2007 user with generating an alphanumeric ID ( http://en.allexperts.com/q/Using-MS-Access-1440/2009/5/Generate-Alphanumeric-ID-... ) and I tried re-creating the solution using some test data/fields and have encountered a "Compile Error" when I click the button. I'm sure I've done something wrong and was hoping you might be able assist me.
I have a very simple table so far "tbl_Contracts" it has only a few fields (BeginDate, ContractNumber and AgencyConNum)as I'm trying to get this problem solved before moving on (to others).
Ultimately I want a number that shows similarly to the answer given to Gosnel, I'd like it a little different "ABCD-###-FY" where the ###'s will count up using the Dmax function and FY would be the current fiscal year (our FY runs "July 1 - June 30" so today 11/03/2009 would be FY 2010).
My test database had only a table and a form, so I've got to be missing pieces of the solution. Any assistance or samples would be GREATLY appreciated. It would be extremely helpful if you could create a dead-simple test database and email it to me, I understand completely if this is not possible.
Thank you in advance for your assistance,
Greg
ANSWER: It would help to see exactly what you have done. If I follow you are using a button to generate the number, so let me see the code behind the button. Also, because your fiscal year is NOT the calendar year, you have to have a way to identify the fiscal year. Finally, is ContractNumber supposed to be the sequential number or did you add another field?
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: So far I really haven't done too much as I'm trying to get this one roadblock cleared before I do literally anything else. The only reason I was thinking of using a button would be to assign the number and have it in the table so no other use could accidently have the same number assigned, like you stated in your previous answer to Gosnel.
So the button code was very similar to the following, with the field/table names changed:
Me.txtLngAutoNo = Nz(Dmax("[lngAutoNo]","tblProjectHeader"),0)+1
Me.Dirty=False
The fiscal year field I figured would use something like what can be found at the following link: http://support.microsoft.com/kb/210249 and dump that year to a field for use later when the complete contract number gets mashed together.
The ContractNumber "should" be a consecutive number, which would reset at the start of each fiscal year.
So, on June 1, 2010 I would start out with "ABCD-001-10" if we had 18 contracts on July 30, 2011 the final number would be "ABCD-018-10" and then June 1, 2011 it would reset and be "ABCD-001-11"
I hope this all makes sense and I truly appreciate your assistance with my problem.
Answer
Do you have a control on your form named txtLngAutoNo? Is it bound to the ContractNumber field or a field name lngAutoNo? Is the ContractNumber an autonumber datatype or a Number/Long Integer?
Since Contract number is going to be your sequential number, then you don't need another field.
As for the Fiscal Year I would create a Global function like this:
Public Function GetFiscalYear(dteBeginDate As Date) As Integer
Dim intMonth As Integer, intYear As Integer
intMonth = Datepart("m",dteBeginDate)
intYear = Datepart("yyyy",dteBeginDate)
If intMonth < 6 Then
GetFiscalYear = intYear-1
Else
GetFiscalYear = intYear
End If
End Function
I would then change the code behind that button to:
Me.txtLngAutoNo = Nz(Dmax("[ContractNumber]","tblProjectHeader","Year([BeginDate]) = " & GetFiscalYear([BeginDate])),0)+1
Me.Dirty=False
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer Ask a Question
|
|
