controlling the sequence number (primary key)

[Capt James Cook]

Is there a simple way to generate the next PK by adding 1 to the largest PK
when you add a new record? I can't use Autonum because my order # starts
with the Fiscal year (i.e. 20090001, 20090002, etc). I tried to use:
=DMax([Sequence No],[Workload])+1
in the default value property, but it didn't work.

Thank you.

 

[Jeff Boyce]

If you need a "human-sensible" sequence number, then create a field and use
your "add 1 to the previous largest value" approach (see "Custom Autonumber"
at mvps.org/access for ideas on how to do it).

But don't expect Access Autonumbers to do anything except provide a unique
row identifier.

By the way, your "numbers" are really codes, right? And they consist of
more than one fact stuffed into a single field (not a good idea in database
design), right? Those are YYYY#### (year and sequence number), right?

Instead, how about using one field for sequence number (use your Max + 1
trick), and a query that returns the Year([YourDateField]) concatenated with
the sequence number. That way, you don't need to (probably redundantly)
store the YYYY value.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Hi Cap'n

DMax takes string arguments:

=DMax("[Sequence No]","Workload")+1

Of course, you will have a problem when the fiscal year changes. You should
really be taking the FY into account in the lookup:

Nz(DMax( "[Sequence No]", "Workload", "Int([Sequence No] / 10000)=" & FY ),
FY * 10000) + 1

(where FY is the fiscal year)

I suggest you write a small function to ascertain the next order number from
the date which you have passed. Something like this:

Public Function NextOrderNum( Optional ByVal OrderDate) as Long
Const FirstMonthOfFY = 4 ' FY starts in April
Dim FY as Integer
Dim rs as DAO.Recordset
If not IsDate(OrderDate) then OrderDate = Date ' use today as default
FY = Year(DateAdd(("m", 1-FirstMonthOfFY, OrderDate))
Set rs = CurrentDb.OpenRecordset( "Select Max([Sequence No] " _
& " from Workload where Int([Sequence No] / 10000)=" & FY, _
dbOpenForwardOnly )
If rs.EOF then
' no orders yet for this year
NextOrderNum = FY * 10000 + 1
Else
NextOrderNum = rs(0) + 1
End If
rs.Close
Set rs = Nothing
End Function

You can then use this for your DefaultValue:

=NextOrderNum()

And also in the AfterUpdate event of the OrderDate:

Me.[Sequence Num] = NextOrderNum(OrderDate)