If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
controlling the sequence number (primary key)
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. |
#2
|
|||
|
|||
controlling the sequence number (primary key)
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 "Capt James Cook" wrote in message ... 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. |
#3
|
|||
|
|||
controlling the sequence number (primary key)
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) -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Capt James Cook" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|