Create Specific Unique Serial Number II

[gymgyrl4]

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

[ADezii]

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.

[gymgyrl4]

Thank you for your time!

[ADezii]

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.

Dim strResponse As String
 
strResponse = fGenerateNextSerialNumber()
MsgBox strResponse       'should get the return value of the Function

[NeoPa]

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.

[gymgyrl4]

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.

[NeoPa]

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.

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?

[gymgyrl4]

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.

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?

[NeoPa]

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.