Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Item Number (2003)

    I've been asked to create a database to record rejected items, a sort of record for minutes of a meeting. I need a main form containing Date, attendees, etc. The subform will contain the details of the rejected items. The problem I have, is that I need to have the details item number to start at 1, every time a new record is created on the main form. How can I do this?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Item Number (2003)

    You need a little bit of code to do it. It is probably best put in your subform's BeforeUpdate event. It would look something like this (I'm just guessing at field and table names):

    If Me.NewRecord = True 'this will only fire when adding a new record
    Me.ItemNo = nz(Dmax("ItemNo","tblRejects","MainFormID=" & Me.Parent.Mainform),0) + 1
    end if

    What this does is just prior to writing a new record, it goes out and finds the max # already in use for the ID from the main form (if it can't find one, it makes it 0). Then it just adds 1 to that number.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Item Number (2003)

    I put this code in, and got Application-defined or object-defined error.

    If Me.NewRecord = True Then
    Me.Item_No = Nz(DMax("Item_No", "tblReject_Details", "MainFormID=" & Me.Parent.Mainform), 0) + 1
    End If

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Item Number (2003)

    You didn't substitute for [MainFormID]. I just made up that name, not knowing what your ID was.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Item Number (2003)

    Yes. Table name is tblReject_Details, and field name is Item_No, which are both on the subform. Here's attached the bare database
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Item Number (2003)

    I edited my reply, apparently after you saw it. You still have [MainFormID], which is my made-up name for the ID from the mainform.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Item Number (2003)

    Changed the code, and although I get number 1 in the item number first time, when I try to creat a new line, I get the same error. Here's the code:-

    Me.Item_No = Nz(DMax("Item_No", "tblReject_Details", "ID=" & Me.Parent.Mainform), 0) + 1

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Item Number (2003)

    In Me.Parent.Mainform, you're supposed to use the name of the relevant field instead of copying Mark's code without thinking. Mainform is *not* the name of any field in your database, and neither is frmRejectReviewID, for that matter.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Item Number (2003)

    The correct version of the code is

    Me.Item_No = Nz(DMax("Item_No", "tblReject_Details", "ID=" & Me.ID), 0) + 1

    In this situation there is no need to refer to the parent form since the main form and parent form are linked by the ID field.

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Item Number (2003)

    Thanks Hans, Mark. I've got it working now. Thanks again.

Posting Permissions

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