By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Create Specific Unique Serial Number II

P: 4
ADezii,

I am also trying to create a similar auto number, minus the E. I have entered the code above (removing all instances of strStaticValue = "-E") and have entered =fGenerateNextSerialNumber() as the default value. I don't get any errors when I compile the code but in my access form I get "#Error". Do you have any ideas? Here is what I have in my code (sorry it's not as neat as yours above):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function fGenerateNextSerialNumber()
  5. Dim strCurrentYear As String
  6. Dim strCurrentDay As String
  7. Dim strStaticValue As String
  8. Dim strSequentialNo As String
  9. Dim strLastSerialNo As String
  10. Dim strLastSequentialNo As String
  11. Dim strNextSequentialNo As String
  12.  
  13. strCurrentYear = Right(Year(Now()), 1)
  14. strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1
  15. 'strStaticValue = "-E"
  16.  
  17. 'get ready to extract the Sequential Number
  18. strLastSerialNo = DLast("[RA_Number]", "Maintenance_Authorization")        'produces 7235-E001
  19. strLastSequentialNo = Right(strLastSerialNo, 3)         'produces 001
  20.  
  21. 'Generate the Next Sequential Number
  22. strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "000")   'produces "002"
  23.  
  24.   'Generate the next, Unique, Serial #
  25.   'fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strStaticValue & strNextSequentialNo
  26.   fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strNextSequentialNo
  27. End Function
Sep 18 '07 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,597
ADezii,

I am also trying to create a similar auto number, minus the E. I have entered the code above (removing all instances of strStaticValue = "-E") and have entered =fGenerateNextSerialNumber() as the default value. I don't get any errors when I compile the code but in my access form I get "#Error". Do you have any ideas? Here is what I have in my code (sorry it's not as neat as yours above):

Option Compare Database
Option Explicit

Public Function fGenerateNextSerialNumber()
Dim strCurrentYear As String
Dim strCurrentDay As String
Dim strStaticValue As String
Dim strSequentialNo As String
Dim strLastSerialNo As String
Dim strLastSequentialNo As String
Dim strNextSequentialNo As String

strCurrentYear = Right(Year(Now()), 1)
strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1
'strStaticValue = "-E"

'get ready to extract the Sequential Number
strLastSerialNo = DLast("[RA_Number]", "Maintenance_Authorization") 'produces 7235-E001
strLastSequentialNo = Right(strLastSerialNo, 3) 'produces 001

'Generate the Next Sequential Number
strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "000") 'produces "002"

'Generate the next, Unique, Serial #
'fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strStaticValue & strNextSequentialNo
fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strNextSequentialNo
End Function
I'll have a look and get back to you.
Sep 18 '07 #2

P: 4
Thank you for your time!
Sep 18 '07 #3

ADezii
Expert 5K+
P: 8,597
Thank you for your time!
Not a problem. The code seems fine, but let us test it and be sure. This will make it easier to isolate the problem. Place these 3 lines of code anywhere and execute them - you should get a valid response. Let me know how you make out.
Expand|Select|Wrap|Line Numbers
  1. Dim strResponse As String
  2.  
  3. strResponse = fGenerateNextSerialNumber()
  4. MsgBox strResponse       'should get the return value of the Function
Sep 19 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
gymgyrl4,

You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from his needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions). Please post your questions in their own threads in future (See How to Ask a Question).

MODERATOR.
Sep 19 '07 #5

P: 4
Sorry bout it...didn't mean to be a hijaker!

ADezii,

I added the msgbox code and I get the error:
Run-time error '94':
Invalid use of Null

and it points to the line of code:
strLastSerialNo = DLast("[RA_Number]", "Maintenance_Authorization")

The RA_Number field in the Maintenance_Authorization table is of data type number (long integer) and is not required.
Sep 19 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
Don't worry about the thread hijack. It's sorted now and I'm sure you won't do it again now you know.
When a field has not got the "Required" setting set to yes, this means that it is possible for some records in the table to have that field missing. When it is missing it returns a Null value. To get around this you can use the Nz() function.
Expand|Select|Wrap|Line Numbers
  1. strLastSerialNo = Nz(DLast("[RA_Number]", "Maintenance_Authorization"), 0)
Do you mean to return the number and then assign it to a string variable by the way?
Sep 19 '07 #7

P: 4
Don't worry about the thread hijack. It's sorted now and I'm sure you won't do it again now you know.
When a field has not got the "Required" setting set to yes, this means that it is possible for some records in the table to have that field missing. When it is missing it returns a Null value. To get around this you can use the Nz() function.
Expand|Select|Wrap|Line Numbers
  1. strLastSerialNo = Nz(DLast("[RA_Number]", "Maintenance_Authorization"), 0)
Do you mean to return the number and then assign it to a string variable by the way?
Nope, just return the value. That works perfectly! Thanks!
Quick question...It returned 7262001. Tomorrow will the last three digit count restart, giving me 7263001?
Sep 19 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
No problem.
I don't know off the top of my head as I'm not sure exactly what code you're using. Post me up the code (Don't forget the CODE tags) and I'll check that out for you if I can.
Sep 19 '07 #9

Post your reply

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