Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Question Unanswered: Detail record numbering

    I am new to Access 2000 (but with 9 yrs exp in Paradox) and I have created a form with a tabular subform in it.

    Purchase Orders. The tables are 1-N. Parent table has PONum as primary key (entered by user) and Detail table has PONum, ItemNum as compound primary key.

    I can't figure out what code to put in the subform so that ItemNum goes from 1 to N, for each PONum.

    There is no mention of a "PriorRecord" method to look at the value of the previous record and add 1. Do I have to do a recordSetClone? Will it know which record I am at in reference to the PONum?

    I'm kinda lost here. I'm still trying to figure out how Access does things.

    Can anyone help?

    Chris

  2. #2
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Re: Detail record numbering

    One way to do this is to set the default value of your itemNum to something like this:

    Code:
    nz(DMax("[itemNum]","Detail","PONum="&[PONum]),0)+1
    DMax("[itemNum]","Detail","PONum="&[PONum]) returns the maximum value of itemNum in the table Detail where the Detail.PONum = the current form's PONum.

    nz(blah,0) returns zero if no records are returned by the Dmax function

    Then add 1 for your increment.

    Any good?

  3. #3
    Join Date
    Nov 2002
    Posts
    150

    Re: Detail record numbering

    like Bri said or....

    In the BeforeInsert event of the subform put

    ItemNum = Me.Form.RecordsetClone.RecordCount + 1

  4. #4
    Join Date
    Jan 2003
    Posts
    4
    Thanks for the replies.

    I yet have to include this code to see if it works, been working on other projects (and it's 10pm right now), but I wanted both of you to know that I really appreciate the response.

    I'll let you know tomorrow how it works out.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •