Welcome to SPN

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

Sign Up Now!

Disallow Blank sub-form

Discussion in 'Information Technology' started by Colin Hammond, Nov 17, 2005.

  1. Colin Hammond

    Colin Hammond
    Expand Collapse
    Guest

    I have a membership database which has family membership, so more than one
    person can be associated with one membership. The main input form has
    address etc and the names of people are entered via a sub-form. My problem
    is that now and again someone enters a membership without any names. I would
    like to stop this action and display a message; a bit beyond my capability
    at the moment!

    Colin
     
  2. Loading...

    Similar Threads Forum Date
    USA Utah Sikhs bring warmth to homeless with blanket donation Breaking News Dec 21, 2013
    History The Art of Firing Blanks: Jaswant Singh and the BJP Punjab, Punjabi, Punjabiyat Aug 25, 2009
    Sikh News Security blanket over Punjab as dera deadline draws close (IANS via Yahoo! India News Breaking News May 27, 2007

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 19:11:52 -0000, "Colin Hammond"
    <nojunkemailthanks@nojunkemailthanks.com> wrote:

    >I have a membership database which has family membership, so more than one
    >person can be associated with one membership. The main input form has
    >address etc and the names of people are entered via a sub-form. My problem
    >is that now and again someone enters a membership without any names. I would
    >like to stop this action and display a message; a bit beyond my capability
    >at the moment!
    >
    >Colin
    >


    Well, this is pretty tricky. When you open the Form and move to a new
    record to enter a new family, the subform MUST be blank (as there is
    no family to have members assigned) - so the situation "empty subform"
    or even "no family members assigned" is in fact a legal situation.
    It's even legal after a family record has been saved, since that
    saving is (must be!) done before you can start entering data on the
    subform; so you can't use the mainform's BeforeUpdate event to check.

    I'd suggest running a "Unmatched" query in the mainform's Close event
    to detect any records which violate the rules:

    SELECT Family.FamilyID
    FROM Families
    LEFT JOIN People
    ON People.FamilyID = Families.FamilyID
    WHERE People.FamilyID IS NULL;

    If this query returns any family ID's, those are the erroneous ones.
    You could write code... ah what the heck...

    Private Sub Form_Close(Cancel as Integer)
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    strSQL = "SELECT Family.FamilyID FROM Families LEFT JOIN People " _
    & "ON People.FamilyID = Families.FamilyID " _
    & "WHERE People.FamilyID IS NULL;"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, vbOpenSnapshot)
    If rs.RecordCount > 0 Then
    ' there were families with no members
    MsgBox "All families must have at least one member!", vbOKOnly
    Cancel = True
    Me.Recordsource = strSQL ' set the Form to display the bad records
    Else
    Cancel = False ' no bad records, let the form close
    End If
    End Sub

    Alternatively you could just navigate the form to the first FamilyID
    in the recordset (and check for other erroneous records next time the
    user tries to close).

    John W. Vinson[MVP]
     
  4. tina

    tina
    Expand Collapse
    Guest

    > You could write code... ah what the heck...

    LOL <g>
    you're an old softy, John! ;)
     
  5. Colin Hammond

    Colin Hammond
    Expand Collapse
    Guest

    Thanks very much for your help John
    Colin
    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:4obnn1h5s8m0b2km38mv4mjfbcu7jrsglb@4ax.com...
    > On Wed, 16 Nov 2005 19:11:52 -0000, "Colin Hammond"
    > <nojunkemailthanks@nojunkemailthanks.com> wrote:
    >
    >>I have a membership database which has family membership, so more than one
    >>person can be associated with one membership. The main input form has
    >>address etc and the names of people are entered via a sub-form. My problem
    >>is that now and again someone enters a membership without any names. I
    >>would
    >>like to stop this action and display a message; a bit beyond my capability
    >>at the moment!
    >>
    >>Colin
    >>

    >
    > Well, this is pretty tricky. When you open the Form and move to a new
    > record to enter a new family, the subform MUST be blank (as there is
    > no family to have members assigned) - so the situation "empty subform"
    > or even "no family members assigned" is in fact a legal situation.
    > It's even legal after a family record has been saved, since that
    > saving is (must be!) done before you can start entering data on the
    > subform; so you can't use the mainform's BeforeUpdate event to check.
    >
    > I'd suggest running a "Unmatched" query in the mainform's Close event
    > to detect any records which violate the rules:
    >
    > SELECT Family.FamilyID
    > FROM Families
    > LEFT JOIN People
    > ON People.FamilyID = Families.FamilyID
    > WHERE People.FamilyID IS NULL;
    >
    > If this query returns any family ID's, those are the erroneous ones.
    > You could write code... ah what the heck...
    >
    > Private Sub Form_Close(Cancel as Integer)
    > Dim strSQL As String
    > Dim db As DAO.Database
    > Dim rs As DAO.Recordset
    > strSQL = "SELECT Family.FamilyID FROM Families LEFT JOIN People " _
    > & "ON People.FamilyID = Families.FamilyID " _
    > & "WHERE People.FamilyID IS NULL;"
    > Set db = CurrentDb
    > Set rs = db.OpenRecordset(strSQL, vbOpenSnapshot)
    > If rs.RecordCount > 0 Then
    > ' there were families with no members
    > MsgBox "All families must have at least one member!", vbOKOnly
    > Cancel = True
    > Me.Recordsource = strSQL ' set the Form to display the bad records
    > Else
    > Cancel = False ' no bad records, let the form close
    > End If
    > End Sub
    >
    > Alternatively you could just navigate the form to the first FamilyID
    > in the recordset (and check for other erroneous records next time the
    > user tries to close).
    >
    > John W. Vinson[MVP]
     

Share This Page