Getting the next available number for users

[MrRocky]

Thanks for the help to those answered my question the first time, but I can't seem to get either one to work. Here what I now have tried. And I tried to debug to see if a nummber is coming in. I do get 1 for the SeqVal.
'SQLstmt = "SELECT max(SequenceNum) FROM tblSequencer WHERE Dept = " & DeptNum & " AND YearValue = " & Year(date) & ";"
'Set RS = conn.Execute(SQLstmt)

lPrevNumber = 0
SQLstmt = "Select max(SequenceNum) from tblSequencer where Dept = " & DeptNum & " AND YearValue = " & Year(date) & ";"
Set rsNum = conn.Execute(SQLstmt)

If rsNum.RecordCount > 0 Then
rsNum.MoveLast
rsNum.MoveFirst
Do While Not rsNum.EOF
If rsNum - 1 > lPrevNumber Then
Exit Do
rsNum.MoveLast
rsNum.MoveNext
Else
lPrevNumber = rsNum
rsNum.MoveNext
End If
Loop
End If
rsNum = lPrevNumber + 1



if rsNum < 10 then
seq = &quot;00&quot; & rsNum
else
if rsNum < 100 then
seq = &quot;0&quot; & rsNum
else
seq = rsNum
end if
end if


'if not RS.eof then
' RS.MoveLast
' RS.MoveNext
' SeqVal = RS(&quot;SequenceNum&quot + 1
'else
' SeqVal = 1
'end if

'if SeqVal < 10 then
' seq = &quot;00&quot; & SeqVal
'else
' if SeqVal < 100 then
' seq = &quot;0&quot; & SeqVal
' else
' seq = SeqVal
' end if
'end if
' response.write(&quot;SeqVal = [&quot; & rsNum & &quot;]&quot
' response.end

EPID = right(DeptNum, 2) & &quot;-&quot; & right(year(Date),2) & &quot;-&quot; & rsNum