Canceling and deleting a record via code – help please.

[FatMan]

Hi all:
I have a form on which one field is the “BlockIDâ€, this field is something
like the 8th or 10th field the user enters data on. So the record has been
created and partially filled in by the time they hit this field. After the
user has updated the “BlockID†field I check to ensure we have received all
the required info on the Block. If any of the required info is missing an
“error form†pops up with the proper error message displayed on it. At this
point the user is presented with two options:

1) “Accept and Proceed with data entryâ€
2) “Reject Recordâ€

I have the first option working and would like to know how one goes about
canceling the data entry of the record and if need be how to delete it
through code.

Can anyone help me?

All help is greatly appreciated.

Thanks,
FatMan

 

[Allen Browne]

The simplest way to do this is to open your table in design view. For each
field where there must be something entered, select it, and in the field
properties (lower pane of table design), set Required to Yes. If the user
misses out any of these, Access won't let them save the record.

Still in table design, the next option is to set up the Validation Rule of
the field (lower pane), or of the table (in the Properties box.) If the
record does not meet your rules, Access won't accept the record. For more on
validation rules, see:
http://allenbrowne.com/ValidationRule.html

To perform checks that are too complex for a validation rule, or to offer a
warning but let the user override it and save the record anyway, use the
BeforeUpdate event of the form. Access fires this event before it saves the
record, and if you cancel the event the record doesn't get saved. You must
use the event of the form (not of a control) to check if a field was left
blank, because the control's events won't fire if the user clicks elsewhere
and never visits that control. It's also best to use Form_BeforeUpdate to
compare fields.

Here is an example. It's designed to make it easy to add more tests, just by
adding more If blocks.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as String
Dim bWarn As Boolean

If IsNull(Me.Surname) Then
Cancel = True
strMsg = strMsg & "Surname required." & vbCrLf
End If
If IsNull(Me.City) Then
Cancel = True
strMsg = strMsg & "City required." & vbCrLf
End If
'etc for other required fields

If Not Cancel Then
If IsNull(Me.FirstName) Then
bWarn = True
strMsg = strMsg & "First Name is blank." & vbCrLf
End If
'etc for other warnings
End If

If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid data"
Else
strMsg = strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Are you sure?") <>
vbYes Then
Cancel = True
End If
End If
End Sub

 

[FatMan]

Allen:
Thanks for the info. I am aware of the “field required and validationâ€
rules in table design but don’t think they will help me here.

Allow me to explain further:
There are really three tables involved with this problem and they are
- TblItntake (PK – IntakeID, FK – BlockID)
- TblBlockInfo (PK – BlockID)
- TblSprayRec (PK – SprayID, FK – BlockID)

Ok, now for the background:
The database was designed to allow the entry of intake lots of fruit and has
grown over the years to the point where we needed to know what “block†of the
orchard the fruit came from and now we need to know what agriculture
chemicals have been applied to that block.

The database works fine for allowing the input of the intake lots and
tracking what block they have come from. The challenge this season is
tracking what agriculture chemicals have been applied to the blocks. To
accomplish this I have developed another database and have packaged and
deployed it to our growers. This database allows the grower to enter and
track what agriculture chemicals he has applied to the blocks of fruit and
submit the records to us. These records are imported into our database here
at the office so when a grower brings in his fruit we can check to see first
if we have a spray record and if so what is the earliest harvest date for the
block. All this is working fine.

Now what I would like to know:
As I mentioned in my original post the data entry has already been started
and some of the fields in the tblIntake table have been updated by the time
BlockID field is entered. What I have done is on the after update event I
check the tblSprayRec table to see if there is a record and what the earliest
harvest date is. If there is no spray record or the intake date is before
the earliest harvest date I open an error form and display an error message.
On this error form I have/want to give the user two options:
1) Accept and proceed with the data entry
2) Reject the record

What I would like to do when the reject the record option is chosen is close
the error form and cancel the data entry of the intake record and/or delete
the intake record.

Can this be done? If so then how would one do it?

Thanks,
FatMan

 

[Allen Browne]

Use Form_BeforeUpdate

Pop up the MsgBox() as shown for the warnings.
If the user answers no, you can cancel the entry by adding the line:
Me.Undo

 

[FGM]

If you want to delete the record that was changed and it was already saved so
UNdo will not work some of the following might be useful.
‘First under a module I put the following variables

Option Compare Database
Option Explicit

Public mIDNumber As Long
Public mDelete As String
Public mNextRecord As Long


Note:
‘mDelete gets set in the After Update. mNextRecord is the new record that
you are on,
‘not the one you want to delete. mNextRecord is used to get back to the
record after the
‘deletion and requery

Private Sub Form_Current()
Dim strSQL As String

If mDelete = "Yes" Then
mNextRecord = Me.IDNumber
DoCmd.SetWarnings False
mDelete = "No"
strSQL = "Delete * from MSTRLIST where IDNumber =" & mIDNumber
DoCmd.RunSQL strSQL
DoCmd.Requery
Me.IDNumber.SetFocus
DoCmd.FindRecord mNextRecord, , True, , True
DoCmd.SetWarnings True
End If
End Sub

‘In the before update it saves the record number of the current record
Private Sub Form_BeforeUpdate(Cancel As Integer)

mIDNumber = Me.IDNumber
End Sub

‘In the AfterUpdate it checks to see if you want to delete the record
‘mDelete is set to “Yes†so that when it gets to the next record it will
delete the previous record if Yes

Private Sub Form_AfterUpdate()
Dim result As String
result = MsgBox("Do you want to Delete the record?", vbYesNo)
If result = True Then
mDelete = "Yes"
End If
End Sub

There might be an easier way but this worked for me.

fgm