Updating a table field in a form using Public Function

[JDenham]

I have created a Public Function that would get a new Production Instructions number based off the [PI Number] of Tbl_Production_Instruction table.
I have a form that people will put in all information but the PI Number, then when ready they will click a button to update the PI Number. I place
an unbound txtbox that will be hidden, with the control source to =NewPINum(), but when I tested the see if the unbound txtbox was populated with
the new PI Number it was blank. I have done this before but can't figure out what I am doing wrong. Any suggestions
Code:
Public Function NewPINum() As String
Dim vNum As String


Dim strYYMM As String
Dim getnextPI As String
strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then

vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3)

vNum = vNum + 1

getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")

Else

vNum = "001"

getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")

End If
End Function

Thanks,

[orange]

Did you Requery the textbox or the Form?

[JDenham]

Yes, I have tried to Requery on both the form and the textbox, I have stepped through the code and the "getnextPI" gives me the correct value.

[June7]

You need to set the function to return value to calling source.

End If
NewPINum = getnextPI

Or just replace both getnextPI references with NewPINum and don't need the getnextPI variable.

[JDenham]

Thanks, June7 that did the trick.