424,066 Members | 2,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 IT Pros & Developers. It's quick & easy.

Dmax auto numbering

P: 22
I have auto number which work fine which I use after update to increment, and I was ask to change it such that it increment automatically when new record is added. Please I need your help. Here is the code

Expand|Select|Wrap|Line Numbers
  1. Private Sub SN_AfterUpdate()
  2. GetREGNO
  3.  
  4. End Sub
  5.  
  6. Private Sub GetREGNO()
  7. Dim StrX, LASTREGNO, LVStr, X, z As String
  8. Dim LVNo As Integer
  9.  
  10. X = Year1
  11. If Len(X) < 2 Then X = "0" & X
  12.  
  13. StrX = "/" & Right(Year1, 5) & "/"
  14.  
  15. If Len(SN) = 1 Then z = "000" & SN
  16.     If Len(SN) = 2 Then z = "00" & SN
  17.     If Len(SN) = 3 Then z = "0" & SN
  18.  If Len(SN) = 4 Then z = SN
  19.  If Len(SN) = 5 Then z = Left(SN, 4) & "/" & Right(SN, 1) & ""
  20.  If Len(SN) = 6 Then z = Left(SN, 4) & "/" & Right(SN, 2)
  21.  REGNO.Value = "PLA" & StrX & z
  22.  
  23. End Sub
3 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,780
Mafi,

Perhaps this one needs a bit more explanation.

Mafi:
I have auto number which work fine which I use after update to increment, and I was ask to change it such that it increment automatically when new record is added. Please I need your help. Here is the code
If your auto number works perfectly, then why do you need to change it. Plus, the code you provided has nothing to do with auto numbering. Auto numbering in Access is a unique identifier used by Access (typically as a Table’s primary Key) which consists of a long integer field that is incremented with each successive record created.

However, I infer from your code that “auto numbering” is not really what you want to do, but you want to create a unique registration number for each successive record created. Conceptually, this is relatively simple. However, you have made our task more difficult by not defining what your final registration number ought to look like.

Based on the question, though, rather than performing this task after you update SN, you could do it on using the OnCurrent event, and have the code first check to see if this is a new record If Me.NewRecord Then . . .. Either way, there are inherent challenges with creating unique numbers like this, but conceptually it is simple. You just need to check to make sure there are no other records with the same value or count how many records within a particular year.

Also, the example provided by your code cannot be deciphered, as you have not defined what the values for Year1 and SN are supposed to refer to. We can guess, but this would not be helpful to anyone here.

Perhaps it would be helpful to know what the REGNO should look like.

Hope this hepps!
3 Weeks Ago #2

P: 22
Thanks for the reply. What I want is unique regno for students like PLA/2018/0001 which will increment by 1. The Year1 is a default of year in my textbox and SN is text box which I will update to generate RegNo.
3 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,170
Hi Mafi.

See if you can use the understanding from Item Numbering Within a Group to get a solution to your problem.
3 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.