J
johnsonr
I am trying to idiot-proof a form for an office full of people who don't seem
ever to have seen a *real* relational database. My form's source query
includes fields from one parent and three child tables (to avoid nested
subforms). The control for a text field in one of the child tables is
disabled until the user checks a checkbox control, to prevent accidental
entry, subsequent deletion, and resultant referential integrity violations.
But, if they should enter & delete anyway, I want to avoid the default "you
can't do that" message box from Access because it would worry them. So I set
the text-field's control's Validation Rule to "Is Not Null" and the
Validation Text to an explanation of what I want them to do.
What I want them to do in that situation is undo the deletion and then
uncheck the checkbox, which executes a Delete query that identifies and
deletes the entire record from the child table. (Ah -- the text field's
Required property is set to Yes, because the only other field in that child
table is the primary key and I don't want the extra work of routinely
deleting useless records). After the Delete query has run, I want the form
to requery, to avoid the pesky #Deleted that otherwise shows up in the text
field. Here's the code for what happens when the checkbox is un-checked:
Dim Msg As String, TblAdd2ID As Variant, DelAdd2SQL As String, DB As Object
On Error GoTo Line3 'To skip the run-time error I get.
Msg = "The data in Address Line 2 are about to be deleted. Do you wish to
continue?"
If MsgBox(Msg, vbOKCancel, "WARNING") = vbCancel Then
GoTo Line3
Else
End If
TblAdd2ID = Me![#AddressID]
Set DB = CurrentDb()
DelAdd2SQL = "DELETE tblAddress2.[#AddressID] FROM tblAddress2 "
DelAdd2SQL = DelAdd2SQL & "WHERE (((tblAddress2.[#AddressID])=" &
TblAdd2ID & "));"
DB.Execute (DelAdd2SQL)
Me.Requery
Everything works fine when the text-field data are deleted, Validation Rule
invoked, data restored, *and* the current record loses the focus before
getting it back and having the checkbox unchecked. BUT: if data are deleted,
Val. Rule invoked, data restored, and then the checkbox unchecked *before*
the record loses & regains the focus, then: I get "Run-Time Error '3167':
Record Is Deleted" at the Me.Requery, the pesky #Deleted appears in the text
box, and the only way to do anything in that field for that record to close
the form and reopen it.
So, why does invoking the Validation Rule make it unwilling to Requery, and
is there a workaround? I'm using Access 2002 SP2 on WinXPPro SP1.
ever to have seen a *real* relational database. My form's source query
includes fields from one parent and three child tables (to avoid nested
subforms). The control for a text field in one of the child tables is
disabled until the user checks a checkbox control, to prevent accidental
entry, subsequent deletion, and resultant referential integrity violations.
But, if they should enter & delete anyway, I want to avoid the default "you
can't do that" message box from Access because it would worry them. So I set
the text-field's control's Validation Rule to "Is Not Null" and the
Validation Text to an explanation of what I want them to do.
What I want them to do in that situation is undo the deletion and then
uncheck the checkbox, which executes a Delete query that identifies and
deletes the entire record from the child table. (Ah -- the text field's
Required property is set to Yes, because the only other field in that child
table is the primary key and I don't want the extra work of routinely
deleting useless records). After the Delete query has run, I want the form
to requery, to avoid the pesky #Deleted that otherwise shows up in the text
field. Here's the code for what happens when the checkbox is un-checked:
Dim Msg As String, TblAdd2ID As Variant, DelAdd2SQL As String, DB As Object
On Error GoTo Line3 'To skip the run-time error I get.
Msg = "The data in Address Line 2 are about to be deleted. Do you wish to
continue?"
If MsgBox(Msg, vbOKCancel, "WARNING") = vbCancel Then
GoTo Line3
Else
End If
TblAdd2ID = Me![#AddressID]
Set DB = CurrentDb()
DelAdd2SQL = "DELETE tblAddress2.[#AddressID] FROM tblAddress2 "
DelAdd2SQL = DelAdd2SQL & "WHERE (((tblAddress2.[#AddressID])=" &
TblAdd2ID & "));"
DB.Execute (DelAdd2SQL)
Me.Requery
Everything works fine when the text-field data are deleted, Validation Rule
invoked, data restored, *and* the current record loses the focus before
getting it back and having the checkbox unchecked. BUT: if data are deleted,
Val. Rule invoked, data restored, and then the checkbox unchecked *before*
the record loses & regains the focus, then: I get "Run-Time Error '3167':
Record Is Deleted" at the Me.Requery, the pesky #Deleted appears in the text
box, and the only way to do anything in that field for that record to close
the form and reopen it.
So, why does invoking the Validation Rule make it unwilling to Requery, and
is there a workaround? I'm using Access 2002 SP2 on WinXPPro SP1.