Thanks for coming back
Not actually using a button, just the record bar on the left, but the
following code runs on the OnDelete Event =CheckFamily(2)
Notes:-
Each Member has a field called MemHeadOfHouseID. This is equal to MemberID
for (Sorry to be sexist) the Alpha Male, and the wife & children will all
have the same MemHeadOfHouseID. That is what links the family together.
The CheckSpace check another table to see if there is a matching record for
MemberID
As you can see, - no cancels anywhere. Incidently written originally 11
years ago, probable Access2. As far as I know, has always worked, but it may
not have been tried as I normally flad members as having resigned, died etc
and they then get archived.
Function CheckFamily(code) ' On delete or resignation of Head
of family
' APS, 6 Jan 96, Warn if family of MemHeadOfHouseID not marked as
resigned
' if head of house no longer active
Dim MyDb As Database
Dim MyQuery As Recordset
Dim Frm As Form, SFrm As Form
Dim Criteria As String
Static Activity(1 To 4) As String
Set MyDb = CurrentDb()
Set MyQuery = MyDb.OpenRecordset("Member", DB_OPEN_DYNASET) ' Open
MemberTable
Set Frm = Forms!Member
If Frm!MemHead = False Then GoTo CheckSpace
Activity(1) = " is active"
Activity(2) = " has resigned"
Activity(3) = " has died"
Activity(4) = " has not rejoined"
Criteria = "MemHeadOfHouseID = " & Frm!MemberID ' Define search
criteria for the member
Select Case code
Case 1 ' afterUpdate of form on retirement
etc
' of HeadOfHouseID
MyQuery.FindFirst Criteria ' Locate first occurrence.
Do Until MyQuery.NoMatch ' Loop until no matching records.
If Frm!MemRetired <1 Then ' Head of House no longer active
If MyQuery!MemRetired <Frm!MemRetired Then
MsgBox "Member " & MyQuery!MemFirstName & " " &
MyQuery!MemSurName & Activity(MyQuery!MemRetired)
End If
End If
MyQuery.FindNext Criteria ' Locate next record.
Loop ' End of loop.
Case 2 ' afterUpdate of form on deleting
HeadOfHouseID
MyQuery.FindFirst Criteria ' Locate first occurrence.
Do Until MyQuery.NoMatch ' Loop until no matching records.
If MyQuery!MemberID <Frm!MemberID Then ' Someone
else
MsgBox "Member's family " & MyQuery!MemFirstName & " " &
MyQuery!MemSurName & " is still on file"
End If
MyQuery.FindNext Criteria ' Locate next record.
Loop ' End of loop.
End Select
MyQuery.Close ' Close table.
Set MyQuery = Nothing
Frm!MemHeadOfHouseID.Requery
Frm!EMemAddID.Requery
CheckSpace: ' Check if Member owns a
boat space
If Frm!MemRetired = 1 Then Exit Function ' Only applies to
members who are not active
Set MyQuery = MyDb.OpenRecordset("jnMemSpace", DB_OPEN_DYNASET) ' Open
Member Space Table
Criteria = "MemberID = " & Frm!MemberID ' Define search criteria
for the member's space
MyQuery.FindFirst Criteria ' Locate first
occurrence.
Do Until MyQuery.NoMatch ' Loop until no matching
records.
MsgBox "Member " & Frm!MemFirstName & " " & Frm!MemSurName & " has
space No: " & MyQuery!SpaceID
MyQuery.FindNext Criteria ' Locate next record.
Loop ' End of loop.
MyQuery.Close
Set MyQuery = Nothing
Set MyDb = Nothing
End Function
"missinglinq via AccessMonster.com" <u28780@uwewrote in message
news:7036690bd78cf@uwe...
Are you using a custom button to delete the record? If so, we need to see
the
code you're using behind this button.
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200704/1
