468,780 Members | 2,212 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,780 developers. It's quick & easy.

AK2 Failure to delete

Deleting a record on a membership form. Does some VB checks to see if the
family are still in the list. Warns me there is no going back if I delete
the record, so I say OK and it appears to delete OK.
Record count goes down by 1, but if I select "ShowAllRecords", there he is
back and the recordCount back to the original value

Theories gratefully received

Phil
Apr 4 '07 #1
2 1615
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

Apr 4 '07 #2
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

Apr 4 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by August1 | last post: by
4 posts views Thread by Dave | last post: by
24 posts views Thread by anders | last post: by
2 posts views Thread by Gill Smith | last post: by
1 post views Thread by John Siracusa | last post: by
2 posts views Thread by Oliver Bleckmann | last post: by
4 posts views Thread by IanWright | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.