Increment portion of key

[Guest]

I have a table where the key is YYYY-xxx-zzz where YYYY is the year, xxx is a
building number which is obtained from a text field on the form and zzz is a
sequential number. What is the best way to determine what the next sequence
should be? Table name is tblMOCform, key field is MOCID and building number
text field is txtBuilding.
Thanks
Marnie

 

[Guest]

This should do the trick maybe some small mistakes. I didn't test the code.

public function GetKey() as string
dim strKey as string
dim strPrefix as string
dim lSequence as long

lsequence=0
strPrefix=yyyy & "-" & txtBuilding & "-"
strkey=nz(dmax("MOCID","tblMOCform","MOCID LIKE '" & strPrefix & "*'"))

if strkey<>"" then
lsequence=val(mid(strkey,len(strprefix)+1))
end if
strkey=strprefix & format(lsequence+1,"0000#")

GetKey=strKey

End Function

- Raoul

 

[Guest]

That did it! Thank you very much!
Marni

This should do the trick maybe some small mistakes. I didn't test the code.

public function GetKey() as string
dim strKey as string
dim strPrefix as string
dim lSequence as long

lsequence=0
strPrefix=yyyy & "-" & txtBuilding & "-"
strkey=nz(dmax("MOCID","tblMOCform","MOCID LIKE '" & strPrefix & "*'"))

if strkey<>"" then
lsequence=val(mid(strkey,len(strprefix)+1))
end if
strkey=strprefix & format(lsequence+1,"0000#")

GetKey=strKey

End Function

- Raoul