What's wrong with this code?

[Chris Nebinger]

If you are relying on opening the table with no sort
order, but rather relying on the table sort order, you
could end up with this problem. Also, it seems expensive
to open an entire table to get the results of one record.
I would replace

Set rst = db.OpenRecordset("tblNotices")
rst.MoveLast

with:

Set rst = db.OpenRecordset("Select Top 1 Issue_Num from
tblNotices ORDER BY Issue_Num DESC")
If rst.EOF then
intLast = 0
Else
If Left(rst!Issue_Num, 6) = Format(Date, "yyyymm") Then
intLast = Right(rst!Issue_Num, 4)
Else
intLast = 0
End If
End If
intNew=intLast + 1



Chris Nebinger

 

[shaggles]

Thanks Chris. That works much better than my code. It
also solves the problem of generating the first Issue_Num.

 

[Larry Linson]

The DMAX domain aggregate function may work even better and it will only
return one record. Opening a recordset will retrieve all the records to find
the last one... not much of a problem with your three test records, but if
your database grows to tens or hundreds of thousands of records, you might
notice the performance hit.

Larry Linson
Microsoft Access MVP