#Name Error

[Rseven]

Hello All,

I have the below code and I basically want module 1 to run to generate a unique report number based on the item type, current year, and then a sequential number starting with 1 so it would look like this - IN12-0001 etc. That report # should also automatically populate in the persons involved table (I have it hidden now) so that we can associate the people involved with the report #. Can anyone see why I get #Name error??

Thanks in advance for your support

Option Compare Database


Function NewSeqNumber(pItem_Type) As String


Dim db As Database
Dim LSQL As String
Dim LUpdate As String
Dim LInsert As String
Dim Lrs As DAO.Recordset
Dim LSeqNumber As String
Dim LYear As String
Dim iLastNbr As Integer

On Error GoTo Err_Execute

Set db = CurrentDb()

'Retrieve last 2 digits of current year
LYear = Left(DatePart("yyyy", Date()), 3)

'Retrieve last number assigned for item_type/year combination
LSQL = "Select Last_Nbr_Assigned from Codes"
LSQL = LSQL & " where Code_Desc = '" & pItem_Type & LYear & "';"

Set Lrs = db.OpenRecordset(LSQL , dbDynaset)

'If no records were found, create a new item_type/year combination in
'the Codes table and set initial value to 1
If Lrs.EOF = True Then

Lrs.AddNew
Lrs("Code_Desc").Value = pItem_Type & LYear
Lrs("Last_Nbr_Assigned") = 1
Lrs.Update

'New sequential number is formatted as "A05-0001", for example
LSeqNumber = pItem_Type & LYear & "-" & Format(1, "0000")

Else
'Determine new sequential number
iLastNbr = Lrs("Last_Nbr_Assigned") .Value
'*** Increment the last number assigned
iLastNbr = iLastNbr + 1

Lrs.Edit
Lrs("Last_Nbr_Assigned") = iLastNbr
Lrs.Update

'New sequential number is formatted as "A05-0001", for example
LSeqNumber = pItem_Type & LYear & "-" & Format(iLastNbr, "0000")

End If

Lrs.Close
Set Lrs = Nothing
Set db = Nothing

NewSeqNumber = LSeqNumber

Exit Function

Err_Execute:
'An error occurred, return blank string
NewSeqNumber = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."

End Function

 

[Andrzejek]

On what line of your code do you get #Name error??



Have fun.

---- Andy

 

[PHV]

Anyway, I'd replace this:
LYear = Left(DatePart("yyyy", Date()), 3)
with this:
LYear = Right(DatePart("yyyy", Date()), 2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

BTW, your line:[tt]
'Retrieve last 2 digits of current year
LYear = Left(DatePart("yyyy", Date()), 3) [/tt]
gets you 3, not 2 (Left) digits, so you would get 201

Consider:[tt]
Right(Date, 2)[/tt]
which gives you 12

Have fun.

---- Andy

 

[Rseven]

Thanks, I did not notice that. It's fixed. The error is showing up on a form data field that i am trying to populate with the autogenerated number

Thanks again

 

[Andrzejek]

So your function NewSeqNumber returns string "#Name"?

Did you step thru your code in that function to see what is going on?

Have fun.

---- Andy

 

[Rseven]

I will do the step thru and see if I can figure out what's happening. Thanks for your time and attention