Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Combined ID problem - probably involving DMAX

Discussion in 'Information Technology' started by SJC, Jul 28, 2006.

  1. SJC

    SJC
    Expand Collapse
    Guest

    I have a form which creates a project ID (can be any user specified string)
    and then opens a subform to add sub-projects, which are strictly contiguous
    within each project. How can I pre-populate the sub project field in the sub
    form with the next sequential number for that Project based on the Project ID
    of the currently open form? (All projects can have a sub project 1, the
    uniqueness is enforced by combining Sub Project ID with Project ID). I'm
    guessing it involves using DMAX but I can't figure out how to apply the
    current Project ID as a criteria.
    Hope my garbled explanation makes sense, any help gratefully received
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    If you have only one user adding records, you could use the BeforeInsert
    event of the form to look up the highest number so far for that project, and
    add 1.

    Something like this:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim strWhere As String
    If IsNull(Me.Parent![ProjectID]) Then
    Cancel = True
    MsgBox "Enter the project in the main form first."
    ElseIf IsNull(Me.[SubProjectID])
    strWhere = "[ProjectID] = """ & Me.Parent![ProjectID] & """"
    Me.[SubProjectID] = Nz(DMax("[SubProjectID]", "[SubTable]",
    strWhere),0) + 1
    End If
    End Sub

    Adjust the names to suit your actual field names.

    If the ProjectID in the main form is a Number field (not a Text field), drop
    the extra quotes, i.e.:
    strWhere = "[ProjectID] = " & Me.Parent![ProjectID]

    If you have multiple users, you might prefer to use the BeforeUpdate event
    of the form, since that fires at the last possible moment.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "SJC" <SJC@discussions.microsoft.com> wrote in message
    news:555CC139-1348-44EA-9251-A5572A20FDB9@microsoft.com...
    >I have a form which creates a project ID (can be any user specified string)
    > and then opens a subform to add sub-projects, which are strictly
    > contiguous
    > within each project. How can I pre-populate the sub project field in the
    > sub
    > form with the next sequential number for that Project based on the Project
    > ID
    > of the currently open form? (All projects can have a sub project 1, the
    > uniqueness is enforced by combining Sub Project ID with Project ID). I'm
    > guessing it involves using DMAX but I can't figure out how to apply the
    > current Project ID as a criteria.
    > Hope my garbled explanation makes sense, any help gratefully received
     
  4. Graham R Seach

    Graham R Seach
    Expand Collapse
    Guest

    This sort of thing should do.

    SubProjectID = Nz(DMax("SubProjectID","tblSubProject","ProjectID = " &
    Me.Parent.Form!txtProjectID), 0) + 1

    Regards,
    Graham R Seach
    Microsoft Access MVP
    Canberra, Australia
    ---------------------------

    "SJC" <SJC@discussions.microsoft.com> wrote in message
    news:555CC139-1348-44EA-9251-A5572A20FDB9@microsoft.com...
    >I have a form which creates a project ID (can be any user specified string)
    > and then opens a subform to add sub-projects, which are strictly
    > contiguous
    > within each project. How can I pre-populate the sub project field in the
    > sub
    > form with the next sequential number for that Project based on the Project
    > ID
    > of the currently open form? (All projects can have a sub project 1, the
    > uniqueness is enforced by combining Sub Project ID with Project ID). I'm
    > guessing it involves using DMAX but I can't figure out how to apply the
    > current Project ID as a criteria.
    > Hope my garbled explanation makes sense, any help gratefully received
     

Share This Page