code problem

[Andy]

Hi there,

I have a table "check no" and code as below :

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim MYRECORDSET, MYLOOKUP As Variant
Dim NUMBER 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
NUMBER = 1
End If

If MYLOOKUP = DATE Then
Set MYRECORDSET = CurrentDb.OpenRecordset("CHECK NO")
NUMBER = MYRECORDSET("NO") + 1
MYRECORDSET.Edit
MYRECORDSET("NO") = NUMBER
MYRECORDSET.Update
ElseIf MYLOOKUP < DATE Then
Set MYRECORDSET = CurrentDb.OpenRecordset("CHECK NO")
MYRECORDSET.Edit
MYRECORDSET("THE_DATE") = DATE
MYRECORDSET("NO") = 1
MYRECORDSET.Update
NUMBER = 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"..... the problem is that it is always
indicate "WS-001" only, I found that the table field of [the_date] is till
null after the code run.
Appreciate for your expert comments. Thanks in advance!
(Access 2002 SP3 / Window XP SP2)

 

[Linq Adams via AccessMonster.com]

Two things, to start:

*** Number *** is a reserved word in Access; you need to change this to
something else!

From Access Help:

"The DLookup function returns a single field value based on the information
specified in criteria. Although criteria is an optional argument, ***if you
don't supply a value for criteria, the DLookup function returns a random
value*** in the domain."

 

[Andy]

Hi there,

Have changed the number to mynumber, the problem is still there. Actually
the tanle "check no" always has one record only, I would like to lookup the
value of [the_date] and [[no] in the table, I thing it is okay if the DLookup
function without value for criteria for this case.
Awaiting for your expert comments. Thanks again!

 

[Andy]

The problem is :
1) [no] is equal to 1 after update but [the_date] is still null after
update, why?
2) Why the value of [invoice no] is WS-001 only?

Hope this clear for you. Awaiting for your reply!

Hi there,

Have changed the number to mynumber, the problem is still there. Actually
the tanle "check no" always has one record only, I would like to lookup the
value of [the_date] and [[no] in the table, I thing it is okay if the DLookup
function without value for criteria for this case.
Awaiting for your expert comments. Thanks again!

Two things, to start:

*** Number *** is a reserved word in Access; you need to change this to
something else!

From Access Help:

"The DLookup function returns a single field value based on the information
specified in criteria. Although criteria is an optional argument, ***if you
don't supply a value for criteria, the DLookup function returns a random
value*** in the domain."