Customized Deletion Process

G

Guest

I have a form that shows records from the DB, with the criteria of
ENTRY_DELETED = false.

ENTRY_DELELETED is a field for each record.

If my users delete a record while viewed in a form, I have the Form_Delete
module set to change ENTRY_DELETED to true (indicating that the record has
been deleted) and set CANCEL = true (so that the record really isn't deleted).

How do I update the display of the form to reflect the changes (to no longer
show the record that was 'deleted')?

I have tried Me.Requery and Forms!Form_Name.Requery in the Form_Delete
module, but receive errors in either case.

I tried placing the requery in Form_AfterDelConfirm module, but it is not
activated due to setting CANCEL = true.
 
6

'69 Camaro

Hi, John.
How do I update the display of the form to reflect the changes (to no
longer
show the record that was 'deleted')?
Click to expand...

Requery the form. You had the right idea, but you didn't know how to write
the code for it.
I have tried Me.Requery and Forms!Form_Name.Requery in the Form_Delete
module, but receive errors in either case.
Click to expand...

It's not a good idea to blindly copy code without knowing what it does or
where to put it. I'll explain the coding rules so that you can avoid these
mistakes in the future.
I have tried Me.Requery
Click to expand...

"Me" refers to the current form, report, or class. The only module where it
can't be used is in a standard module, which is exactly where you put it.
and Forms!Form_Name.Requery
Click to expand...

This example you found and blindly copied was given by the author with the
expectation that you would know to replace the sample Form_Name with the
actual name of your form. For example:

Forms!frmAudits.Requery

.. . . where the name of the form is frmAudits.
in the Form_Delete
module
Click to expand...

Never use an identifier (which is an object name, column name, procedure
name, variable, et cetera) that is a reserved word or is the name of
something else, because Access may confuse the two -- but often keeps
silent -- and the only way you know it's using the wrong one is when there
are unexpected errors in the data -- which means one must be on the ball to
see when those errors occur.

Form_Delete is an event for all forms, so it's best to let Access have that
name, while you use another name for your module.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
G

Guest

I mis-spoke when I said I placed them in a module. I actually placed the
code in the 'On Delete' event for the form.

So, the Me.Requery did reference the form and 'should' have worked, but
gives an error.

I was trying to simplify my question and not bore readers with my particular
names I use. Forms!Form_Name.Requery was generic.

ANYONE ELSE have an idea of how to my original question?
 
6

'69 Camaro

Hi, John.
So, the Me.Requery did reference the form and 'should' have worked, but
gives an error.
Click to expand...

Perhaps you can share the error message with us? The form needs to be
requeried, so whatever is preventing that from happening needs to be fixed.
The error message will likely give a clue as to what needs to be fixed.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
G

Guest

Run Time Error 3246
Operation not supported in transactions.

Here is the code that is contained within the form...

Private Sub Form_Delete(Cancel As Integer)
ans = MsgBox("Are you sure you want to delete this record?", vbYesNo)
'Mark records as deleted...................................
If ans = vbYes Then
With Me
.chkDeleted = True
.txtMachineDelete = varMachineID
.txtUserDelete = varUserID
End With
Me.Requery <<<======GIVES ERROR
End If
Cancel = True
Exit Sub
End Sub
 
6

'69 Camaro

Hi, John.

Try the following in your form's module:

' * * * Begin Code * * *

Option Compare Database
Option Explicit

Private m_DeleteRec As Boolean


Private Sub Form_Delete(Cancel As Integer)

On Error GoTo ErrHandler

Dim ans As Integer

ans = MsgBox("Are you sure you want to delete this record?", vbYesNo)
'Mark records as deleted...................................
If ans = vbYes Then
With Me
.chkDeleted = True
.txtMachineDelete = varMachineID ' <-- Defined elsewhere.
.txtUserDelete = varUserID ' <-- Defined
elsewhere.
End With
End If

Cancel = True
m_DeleteRec = True
Me.TimerInterval = 100

Exit Sub

ErrHandler:

MsgBox "Error in Form_Delete() in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Private Sub Form_Timer()

On Error GoTo ErrHandler

Me.TimerInterval = 0
m_DeleteRec = False
Me.Requery

Exit Sub

ErrHandler:

MsgBox "Error in Form_Timer() in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * End Code * * *

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
6

'69 Camaro

Hi, John.

On second thought, the following would be better for the OnTimer( ) event:

' * * * Begin Code * * *

Private Sub Form_Timer()

On Error GoTo ErrHandler

Me.TimerInterval = 0

If (m_DeleteRec) Then
Me.Requery
m_DeleteRec = False
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Timer() in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * End Code * * *

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
6

'69 Camaro

Hi, John.

On third thought, that's a bad idea -- is this Monday morning or
something?!! Geez! The form's requery command should be _after_ resetting
m_DeleteRec = False. I should have left it in the same order as the first
time. So try:

' * * * Begin Code * * *

Private Sub Form_Timer()

On Error GoTo ErrHandler

Me.TimerInterval = 0

If (m_DeleteRec) Then
m_DeleteRec = False
Me.Requery
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Timer() in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * End Code * * *

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
G

Guest

LOL, thank you for the suggestion(s).

Will attempt when I return to work next week!!

Have a nice MONDAY!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Edit --> Delete & Delete Button on a form. 8
delete warning 9
Capture Delete event 1
Form OnDelete event 3
Record insert/delete anomaly 0
type mismatch in DLookup 3
Overriding constraint restriction message, when deleting record(s) from form 6
Deleting a Form & SubForm Record 1

Top