Why does this code stop working?

[Matthew]

I have a Clients database, and I've created forms to clean up duplicated
data. There's tblClients and a main Clients form (frm022-01_AdminPeople1).
There are ten tables that are related to tblClients, each is represented by
a subform.

I've done the following in another database, it works perfectly. But not
here.

When a user clicks on the Delete button, my code checks to see if there are
records in any of subforms. If there are, the user is warned appropriately,
the delete is cancelled, and the user can deal with those records that would
be orphaned.

Each of the subforms has a field in it called HowMany, with Control source
=Count(*).

My code works for the first one, two, maybe three times. Then, it fires the
warning if a subform has records, but once those records are cleared out,
pressing Delete does absolutely nothing at all. I can go to a different
record, close the form & reopen it, and it still doesn't delete a record
that it should have no problem deleting, until I exit and relaunch the
application.

Here is my code. I've only included the first three subform checks, for
brevity; the rest follow suit.


Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Call ContactOrphanCheck

Exit_cmdDelete_Click:
Exit Sub
Err_cmdDelete_Click:
If Err.Number = 2501 Then
Err = 0
Me.Requery
Resume Next
End If
Resume Exit_cmdDelete_Click
End Sub



Public Function ContactOrphanCheck()

On Error GoTo Err_ContactOrphanCheck

If Forms![frm022-01_AdminPeople1]![ChildAddresses].Form![HowMany] > 0 Then
MsgBox "This person still has an address. Please correct before
deleting."
ElseIf Forms![frm022-01_AdminPeople1]![ChildAffiliations].Form![HowMany] > 0
Then
MsgBox "This person still has affiliations. Please correct before
deleting."
ElseIf Forms![frm022-01_AdminPeople1]![ChildTels].Form![HowMany] > 0 Then
MsgBox "This person still has telephone numbers. Please correct before
deleting."
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

Err_ContactOrphanCheck:
If Err.Number = 2501 Then
Err = 0
Resume Next
End If
End Function



Any help will be greatly appreciated!!!

Thanks,

Matthew

 

[Marshall Barton]

I have a Clients database, and I've created forms to clean up duplicated
data. There's tblClients and a main Clients form (frm022-01_AdminPeople1).
There are ten tables that are related to tblClients, each is represented by
a subform.

I've done the following in another database, it works perfectly. But not
here.

When a user clicks on the Delete button, my code checks to see if there are
records in any of subforms. If there are, the user is warned appropriately,
the delete is cancelled, and the user can deal with those records that would
be orphaned.

Each of the subforms has a field in it called HowMany, with Control source
=Count(*).

My code works for the first one, two, maybe three times. Then, it fires the
warning if a subform has records, but once those records are cleared out,
pressing Delete does absolutely nothing at all. I can go to a different
record, close the form & reopen it, and it still doesn't delete a record
that it should have no problem deleting, until I exit and relaunch the
application.

Here is my code. I've only included the first three subform checks, for
brevity; the rest follow suit.


Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Call ContactOrphanCheck

Exit_cmdDelete_Click:
Exit Sub
Err_cmdDelete_Click:
If Err.Number = 2501 Then
Err = 0
Me.Requery
Resume Next
End If
Resume Exit_cmdDelete_Click
End Sub



Public Function ContactOrphanCheck()

On Error GoTo Err_ContactOrphanCheck

If Forms![frm022-01_AdminPeople1]![ChildAddresses].Form![HowMany] > 0 Then
MsgBox "This person still has an address. Please correct before

You will have to find another way to do that. VBA code can
not reliable use a text box expression to calculate a value
because the VBA code and the expression evaluator run in
different tasks at different priorities.

I think you can get what you want by using:
Forms![frm022-01_AdminPeople1]![ChildAddresses].Form.Recordset.Recount>0

A Requery might be needed after the deletes are done.

Using DoMenuItem might work, but it is an archaic leftover
from Access version 2, so think about replacing it with some
other method.

 

[Bob Quintal]

I have a Clients database, and I've created forms to clean up
duplicated data. There's tblClients and a main Clients form
(frm022-01_AdminPeople1). There are ten tables that are related to
tblClients, each is represented by a subform.

I've done the following in another database, it works perfectly.
But not here.

When a user clicks on the Delete button, my code checks to see if
there are records in any of subforms. If there are, the user is
warned appropriately, the delete is cancelled, and the user can
deal with those records that would be orphaned.

Each of the subforms has a field in it called HowMany, with
Control source =Count(*).

My code works for the first one, two, maybe three times. Then, it
fires the warning if a subform has records, but once those records
are cleared out, pressing Delete does absolutely nothing at all.
I can go to a different record, close the form & reopen it, and it
still doesn't delete a record that it should have no problem
deleting, until I exit and relaunch the application.

Here is my code. I've only included the first three subform
checks, for brevity; the rest follow suit.


Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Call ContactOrphanCheck

Exit_cmdDelete_Click:
Exit Sub
Err_cmdDelete_Click:
If Err.Number = 2501 Then
Err = 0
Me.Requery
Resume Next
End If
Resume Exit_cmdDelete_Click
End Sub



Public Function ContactOrphanCheck()

On Error GoTo Err_ContactOrphanCheck

If Forms![frm022-01_AdminPeople1]![ChildAddresses].Form![HowMany]

0 Then

MsgBox "This person still has an address. Please correct
before
deleting."
ElseIf
Forms![frm022-01_AdminPeople1]![ChildAffiliations].Form![HowMany]

0 Then

MsgBox "This person still has affiliations. Please correct
before
deleting."
ElseIf Forms![frm022-01_AdminPeople1]![ChildTels].Form![HowMany] >
0 Then
MsgBox "This person still has telephone numbers. Please
correct before
deleting."
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

Err_ContactOrphanCheck:
If Err.Number = 2501 Then
Err = 0
Resume Next
End If
End Function



Any help will be greatly appreciated!!!

Thanks,

Matthew

Your error handling is the pits. and probably cause of the problem.
the cmdDelete_Click error handler uses a resume next, inside the IF
block for error 2501, then has a resume Exit_cmdDelete_Click
outside the loop.

If you get an error 2501, your system will go crazy trying to
process first one then the other resume location.

Your Error handler in ContactOrphanCheck is adequate, so you can
remove the special case in the main error handler, it's redundant
and unnecessary.