How to cancel execution of code when form closes

[tmreic]

I currently have the first listed set of code located behind one of my textboxes on a form. It is used to validate data entry on my form. The code works like it should when entering the data. But everytime i close or exit the form the code excutes and diplays the msgbox and inputbox. Is there a way to cancel this code just during the exiting of the form. I have tried other event but to no success. The second set of code is what i used stop the validation code from excuting as the form loads . I was tryed to do the same for the unload but to no succes.
Any Ideas?
Thank you,
Tim

Private Sub txtSerial_Exit(Cancel As Integer)
If testing = False Then
Exit Sub
End If

Dim VSid As String
VSid = Len(txtSerial.Text)
If VSid = 11 And testing = True Then
txtMillTube.SetFocus
Else
MsgBox "Invalid Virgo Serial Number" & vbCrLf & vbCrLf & " Please Rescan Correct Tag", vbOKCancel
Me!txtSerial = Null
txtSerial.Value = InputBox("Please Rescan Correct Tag&quot
Me!txtSerial.SetFocus
End If

End Sub

Option Compare Database
Option Explicit
Dim testing As Boolean

Private Sub Form_Close()
testing = False
End Sub

Private Sub Form_Load()
testing = True
End Sub

 

[TonyJollans]

Hi tmreic,

What other event(s) have you tried? You should normally validate in the BeforeUpdate event. The problem with the Exit event is that you exit the textbox before you exit the form. I don't know the sequence of all the events and there might be a convoluted series you could use but really you are seeing the result of Access working as designed.

Enjoy,
Tony

 

[tmreic]

Tony,
I have tried the before and afterupdate events, change, lostfocus events. I did use the beforeupdate events with this code but it does not check the value of the textbox until i enter data into the rest of the textboxes and update the form. What i am trying to do is validate the value in the textbox at the time of entering. The only event that would work for this was the exit event. I copied the rest of the code into the form. I thought it might help but things in context

Thanks for you help,
Tim

Option Compare Database
Option Explicit
Public testing As Boolean

Private Sub cmdEnd_Enter()
On Error GoTo Err

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic

'open the recordset
rst.Open "Select * from tblNueHarvesttable where left(milltube,12) ='" & Me.txtMillTube & "'"

'check for duplicate milltube
Dim str As String
str = "[milltube]='" & Me.txtMillTube & "'"
rst.Find str
If Not rst.EOF Then
MsgBox "This mill tube has been used. Please enter a unique Mill Tube ID", vbCritical, "Caution!!!!"
Me.txtMillTube = Null
rst.Close
Set rst = Nothing
txtMillTube.SetFocus
Exit Sub
End If

rst.AddNew
rst!serialid = Me.txtSerial
rst!milltube = Me.txtMillTube
rst!shootfw = Me.txtShootFW
rst!leaffw = Me.txtLeafFW
rst!leafarea = Me.txtLA
rst!timestamp = Now()
rst.Update

Me.Recalc
Me.txtSerial = Null
Me.txtMillTube = Null
Me.txtShootFW = Null
Me.txtLeafFW = Null
Me.txtLA = Null
DoCmd.GoToControl "txtSerial"
rst.Close
Set rst = Nothing
DoCmd.Beep

Exit Sub
Err:
MsgBox "Missing or Invalid Data Type," & vbCrLf & vbCrLf & "Make sure the Serial ID and Weights are Numeric" _
& vbCrLf & vbCrLf & "Once data is corrected, Press Add Record button"
rst.Cancel
txtSerial.SetFocus


End

End Sub

Private Sub Form_Close()
testing = False
End Sub

Private Sub Form_Load()
testing = True
End Sub


Private Sub txtMillTube_afterUpdate()
Rerun:
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic

'open the recordset
rst1.Open "Select * from tblNueHarvesttable where left(milltube,12) ='" & Me.txtMillTube & "'"

'check for duplicate milltube
Dim str As String
str = "[milltube]='" & Me.txtMillTube & "'"
rst1.Find str
If Not rst1.EOF Then
MsgBox "This mill tube has been used. Please enter a unique Mill Tube ID", vbCritical, "Caution!!!!"
Me.txtMillTube = Null
rst1.Close
Set rst1 = Nothing
Me.Recalc
txtMillTube.SetFocus
End If
If txtMillTube.Text = "" Then
txtMillTube.Value = InputBox("Please Enter a Unique Mill Tube ID&quot
GoTo Rerun
Else
End If

Exit Sub
End Sub



Private Sub txtSerial_Exit(Cancel As Integer)


Dim VSid As String
VSid = Len(txtSerial.Text)
If VSid = 11 And testing = True Then
txtMillTube.SetFocus
Else
MsgBox "Invalid Virgo Serial Number" & vbCrLf & vbCrLf & " Please Rescan Correct Tag", vbOKCancel
Me!txtSerial = Null
txtSerial.Value = InputBox("Please Rescan Correct Tag&quot
Me!txtSerial.SetFocus
End If

End Sub

 

[TonyJollans]

Hi Tim,

From yor description it sounds like you tried the Form's BeforeUpdate event rather than the Control's.

Anyway, let's see if I understand this. You have, amongst other controls, 2 textboxes (txtSerial and txtMillTube) and 1 button (cmdEnd).

When data is input (how?) into txtSerial and the focus moved away (<Enter> or <Tab> or (mouse)) you want to run the code you currently have in the Exit event to validate the input. If the validation fails you first issue a message (with 2 buttons which both do the same) and then prompt with an input box.

When I shift this code to the BeforeUpdate event it runs (from a user view at the same time as it did) but fails when it tries to update the control. Is there a particular reason for the input box? If you set Cancel = True after displaying the message the form is re-shown with the focus on the textbox (for re-input). If you also include the line Me.txtSerial.Undo the value is reset to what it started out as, always assuming it is a bound control of course.

I haven't been able to test the code for txtMillTube but, again, I think you want it in the BeforeUpdate not the AfterUpdate.

Finally, briefly, cmdEnd. Is this a button? Is running code when it gets the focus what you really want to do?

I'm not at all sure that I really understand what you are trying to do, but I am fairly sure that you should be using BeforeUpdate for txtSerial (unless it is unbound which might give a bit of a problem). I'm not so sure about txtMillTube because the validation appears to involve multiple fields on the form so you may need to put your code for that in the Form's BeforeUpdate event.

I feel like I'm rambling. Better stop! And my dinner's ready.

Enjoy,
Tony

 

[tmreic]

Tony,
Thanks for bearing with me. In my real life i am a scientist and in my off hours i am trying to learn computer programming. So I am using projects at work to teach myself VB.

Anyway, I took your suggestion of adding the Cancel = true statement and modified the controls to a beforeupdate event. After deleting the extra code the problem i was having originally had was solved. I also changed the txtMilltube to before update and added the cancel=true line. So again thanks for your help. You solutions was exactly what i was looling for. I had stumbled across the cancel=true statement in the help section but did not know how to apply it. I am still not sure what this line does to the event.

Thanks for your patience and help.

Tim

 

[TonyJollans]

Hi Tim,

BeforeUpdate events are passed a boolean parameter called Cancel, which by default is False. Setting it to True tells Access to cancel the update it is about to do. This also stops further processing so the focus remains where it was instead of moving on as requested by the user. Individual controls are updated whenever the focus moves away from them and 'updated' here means updated in the buffer behind the form; updates here can be undone (by the user pressing ESC or, in code, with &quot;Undo&quot. The form (i.e the record) is updated, again, when the focus moves off it - either on to the next record, or if the form is closed; in this case updated means the record in the underlying table (or query) is updated, after which it cannot be undone.

Good luck with the learning, and keep asking questions if you're stuck.

Enjoy,
Tony