On Mon, 24 Nov 2003 20:15:29 +0200 in comp.databases.ms-access, "home"
<re**@telkomsa.net> wrote:
I have a question,
I want to build a JobNo made up from the YY part of the date on my form then
with a "/" and then the month followed by a counter field.
Something like this
03/11698 (03 = Year, /, 11= month, 698 counter)
Any ideas please
The Easy bit:
Sub Form_BeforeUpdate(Cancel As Integer)
' or could be BeforeInsert, if you like
If IsNull(Me!JobNo ) Then
Me!JobNo = format(JobDate,"YY/MM") & GetCounter(JobDate)
End If
End Sub
The hard bit: working out a counter.
(warning: air code - untested)
Function GetCounter(pDate As Date)
' assumes a table called "tblJobNo" with fields:-
' YearMonth : Text 5 - Primary Key
' TheCounter: Long Integer
' also assumes DAO.
Dim rs as recordset, db as database, i as long
set db = currentdb()
' next line may wrap in newsreader
set rs = db.openrecordset("select * from tblJobNo where YearMonth =
'" & Format(pDate,"YY/MM") & "'")
with rs
if .eof then
.addnew
!YearMonth = Format(pDate,"YY/MM")
i=0
else
i = !theCounter
.Edit
end if
i = i + 1
!TheCounter = i
.update
.close
end with
set rs=nothing
set db =nothing
GetCounter = i
' maybe GetCounter = Format(i,"000")
End Function
Add error trapping to suit. Also some locking options on OpenRecordset
method (dbDenyRead or dbDenyWrite but I forget which parameter). ADO
would require a slight difference in the opening of the recordset but
the rest of the code would be the same.
--
A)bort, R)etry, I)nfluence with large hammer.