Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    validata data in subform (2000 all updates)

    This is a many-to-many relationship construction. tblFamily and tblIndividual are joined by a linking table called tblJoinIndividualFamily.
    tblJoinIndividualFamily has 3 fields - Composite key made up of IndividualID from tblIndividual and FamilyID from tblFamily, and RelationshipID which is a number field that matches RelationshipID in tblRelationship.

    The purpose of tblRelationship is to provide the various possible relationships in a family structure (Adult Householder 1, Adult Householder 2, Child, Adult Son, Adult Daughter, and so on). This has only two fields, RelationshipID (auto number) and Relationship (text field).

    The main form, frmFamilies, is based on qryFamily. On the form there is a subform called fsubJoinIndividualFamily which is linked to frmFamilies by the FamilyID field.

    Now, for the problem...
    On the subform, fsubJoinIndividualFamily, one control is a combo box called cboRelationship based on tblRelationship. What I am trying to do is ensure that the user can only have 1 "Adult Householder 1" selected. To accomplish this, I have been trying to put code on the BeforeUpdate event of the cboRelationship control.

    The code I have been trying is...
    Dim intTemp As Integer

    intTemp = DLookup("[RelationshipID]", "tblJoinIndividualFamily", "[FamilyID] = FamilyID And [RelationshipID] = 1")
    If Not IsNull(intTemp) Then
    MsgBox "You already have one Adult Householder 1"
    Cancel = True
    End If

    The message box comes up regardless of the selection.

    I have also tried to create a query that counts the number of RelationshipID where "RelationshipID = 1" (the number for "Adult Householder 1") and then refer to that number in the DLookup structure by chaning the varTemp to intTemp and replacing the one line with
    'intTemp = DLookup("[CountOfRelationship]", "Query3", "[FamilyID] = FamilyID And [RelationshipID] = 1")

    This produces the same message box every time.

    I have worried this thing to death for quite a while. Where am I going wrong?

    Thanks.

    Tom

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

    Re: validata data in subform (2000 all updates)

    Try


    intTemp = DCount("*", "tblJoinIndividualFamily", "FamilyID = " & Me.FamilyID " And RelationshipID = 1 And Not IndividualID = " & Me.IndividualID)
    If intTemp > 0 Then
    ...
    End If

    Note that the values from the form have been placed outside the quotes, and that the current IndividualID is excluded from the count.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validata data in subform (2000 all updates)

    Hanks
    Thanks, but things still aren't right.

    Using the DCount that you suggested resulted in a compile error in this part " And RelationshipID = 1 And Not IndividualID = "
    So I added an ampersand following Me.FamilyID, to make the whole thing read
    intTemp = DCount("*", "tblJoinIndividualFamily", "FamilyID = " & Me.FamilyID & " And RelationshipID = 1 And Not IndividualID = " & Me.IndividualID)

    The compile error goes away, however that still ends up with a message box each time.

    Tom

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

    Re: validata data in subform (2000 all updates)

    Sorry, that was an oversight.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validata data in subform (2000 all updates)

    Hans
    Actually, the problem can be solved in another way. By using the following code...
    Dim intTemp As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim intID As Integer
    Dim intrelID As Integer


    intrelID = Me.cboRelationship

    If intrelID = 1 Then

    intID = Me.FamilyID
    Set db = CurrentDb()
    strSQL = "SELECT tblJoinIndividualFamily.FamilyID, tblJoinIndividualFamily.RelationshipID " & _
    "FROM tblJoinIndividualFamily " & _
    "WHERE (((tblJoinIndividualFamily.FamilyID)=" & intID & ") AND ((tblJoinIndividualFamily.RelationshipID)=1));"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    intTemp = Nz(rs.RecordCount, 0)
    If intTemp >= 1 Then
    MsgBox "You can only have one AdultHouseholder1. Choose another relationship.", vbExclamation, "Too many Adult 1's!"
    rs.close
    set rs = nothing

    Exit Sub
    End If
    End If

    What's your opinion of the merits of using the approach of a recordset rather than a Domain Aggregate function?

    I'm going to strip down a copy and post it anyway, in a second message...for your perusal.

    Tom

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

    Re: validata data in subform (2000 all updates)

    Using a recordset should work well, but I don't understand yet why DLookup or DCount don't.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validata data in subform (2000 all updates)

    Hans
    Here is a stripped down copy of the database.

    Tom

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

    Re: validata data in subform (2000 all updates)

    It works well for me with the DCount version... <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    See attached database.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validata data in subform (2000 all updates)

    Hans
    Yep, she do work. (I like the little scratchy guy!!)

    So, on a matter of principle, which do you regard as the better to use - the DCount approach, or the recordset approach? Is it just a matter of preference? I have read that Domain Aggregate functions consume more database resources.

    Tom

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

    Re: validata data in subform (2000 all updates)

    Although I don't know the technical details, I assume that the domain aggregate functions open a recordset, retrieve the desired result, then close the recordset. But it's all done invisibly. So I don't think there's much difference in this situation. I prefer DCount here, because the code is shorter. If you would need several calls to domain aggregate functions on the same table/query, you'd be opening and closing a recordset for each call, so then it becomes more efficient to open a recordset yourself once, retrieving various results from it, then closing it when you're done.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validata data in subform (2000 all updates)

    Hans
    I modified the code, as I need also to prevent duplicate "Adult Householder 2" persons.

    The modified code is as follows:
    Dim intTemp As Integer
    Dim intID As Integer
    Dim intrelID As Integer
    intrelID = Me.cboRelationship
    If intrelID = 1 Then
    intID = Nz(Me.FamilyID)
    intTemp = DCount("*", "tblJoinIndividualFamily", "FamilyID = " & intID & _
    " And RelationshipID = 1 And Not IndividualID = " & Me.IndividualID)
    MsgBox "You can only have one AdultHouseholder1. Choose another relationship.", _
    vbExclamation, "Too many Adult 1's!"
    Cancel = True
    Me.Undo
    End If

    If intrelID = 2 Then
    intID = Nz(Me.FamilyID)
    intTemp = DCount("*", "tblJoinIndividualFamily", "FamilyID = " & intID & _
    " And RelationshipID = 2 And Not IndividualID = " & Me.IndividualID)
    If intTemp >= 1 Then
    MsgBox "You can only have one AdultHouseholder2. Choose another relationship.", _
    vbExclamation, "Too many Adult 2's!"
    Cancel = True
    Me.Undo
    End If
    End If
    End Sub

    This works fine as long as there are entries already in the subform. However, if no entries exist, this code kicks in and disallows the entry or an Adult Householder 1 and 2.

    I tried adding a line
    If Not Me.NewRecord Then
    just before the code, but then the code doesn't kick in all on new record entries.

    Any thoughts?

    Tom

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

    Re: validata data in subform (2000 all updates)

    You've managed to remove the test

    If intTemp > 1 Then
    ...
    End If

    from the part that checks for duplicate AdultHouseHolder1. The inconsistent indentation (not visible in your post because you didn't format it correctly) is a clue.

  13. #13
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validata data in subform (2000 all updates)

    Hans
    You are absolutely correct. I don't know how I managed to do that, but I did. Note that the line exists in the code for Adult Householder 2. (for the want of a shim, the motorcycle won't run)

    As for indenting, I copied the code directly from my program and pasted it here. The indentation is correct in my program. I have noticed before that formatting goes away when it's pasted here.

    Thanks for catching my error.

    Tom

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

    Re: validata data in subform (2000 all updates)

    Yes, I know that your code was originally indented. There are two ways to preserve it in a post:

    1) Place a <!t>[pre]<!/t> tag before, and a <!t>[/pre]<!/t> tag after your code. Be careful: text between these tags isn't wrapped automatically, so long lines make the post look weird and may cause the Lounge window to scroll horizontally. Because of this, you get a warning when your post contains <!t>[pre]<!/t> tags.

    2) Replace the number of spaces representing a tab with the <!t>[tab]<!/t> tag. I copy code into an empty Word document, run the macro from <post:=346,967>post 346,967</post:>, then copy the result and paste it into a post.

Posting Permissions

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