code problem.......please help

[Andy]

Hi there,

I have a table "check no" (no record at first and only 1 record later for
checking)
Two fields only :
[the_date], data type date/time
[no], data type number

The code as below :

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim MYRECORDSET, MYLOOKUP As Variant
Dim MYNUMBER As Variant

MYLOOKUP = DLookup("THE_DATE", "CHECK NO")

If IsNull(MYLOOKUP) Then
Set MYRECORDSET = CurrentDb.OpenRecordset("CHECK NO")
MYRECORDSET.AddNew
MYRECORDSET("THE_DATE") = DATE
MYRECORDSET("NO") = 1
MYRECORDSET.Update
MYNUMBER = 1
End If

If MYLOOKUP = DATE Then
Set MYRECORDSET = CurrentDb.OpenRecordset("CHECK NO")
MYNUMBER = MYRECORDSET("NO") + 1
MYRECORDSET.Edit
MYRECORDSET("NO") = MYNUMBER
MYRECORDSET.Update
ElseIf MYLOOKUP < DATE Then
Set MYRECORDSET = CurrentDb.OpenRecordset("CHECK NO")
MYRECORDSET.Edit
MYRECORDSET("THE_DATE") = DATE
MYRECORDSET("NO") = 1
MYRECORDSET.Update
MYNUMBER = 1
End If

Me![INVOICE NO] = "WS-" & Format(DATE, "YYMM") & Format(NUMBER, "000")

End Sub

I would like the [invoice no] show :
"WS-0711001","WS-0711002","WS-0711003".....at current month
"WS-0712001","WS-0712002","WS-0712003".....at next month
the problem is the [invoice no] always indicate "WS-001" only, I found that
the table field of [no] is updated but [the_date] is till null after the code
update.

1) Why the [the_date] is null after update?
2) Why the [invoice no] show "WS-001" only, it should be "WS-0711001" at 1st
record according to the last line of the code :
Me![INVOICE NO] = "WS-" & Format(DATE, "YYMM") & Format(MYNUMBER, "000")

Appreciate for your expert comments. Thanks in advance!

(Access 2002 SP3 / Window XP SP2)