Global variable used for branching

[judgehopkins]

This is a global variable question. When the user clicks exit, I do not want to exit unless a valid order number has been entered. I have used a global variable (gintFlag) to try to branch the Sub Form_Unload.

E.g., if the user clicks exit, the unload procedure calls the check order number procedure. If the user in the check order number procedure clicks cancel to go back to the last order entered, gintFlag is set to 1 and the execution goes back to the unload procedure.

The debugger shows the gintFlag set to 1 but the execution completely skips the If gintFlag = (gintFlag + 1) Then
gintFlag = 0
DoCmd.CancelEvent
Exit Sub
part of the code and goes to the “Do you want to exit?” messagebox, etc.

If the user clicks cancel in the check order number procedure, I want the unload procedure to stop.

Is this possible?

Option Compare Database
Option Explicit
Dim gintFlag As Integer

Private Sub Form_Unload(Cancel As Integer)
gintFlag = 0
CheckOrderNumberTest
If gintFlag = (gintFlag + 1) Then
gintFlag = 0
DoCmd.CancelEvent
Exit Sub
'When user clicks close, this confirmation box pops up;
'if user selects no, then Access returns user to the form
ElseIf MsgBox("Are you sure you want to exit?", _
vbYesNo, "Do you want to close?") = vbNo Then
    Cancel = True
End If

End Sub


Private Sub CheckOrderNumberTest()
gintFlag = (gintFlag + 1)
'This procedure requires the user to enter
'a correct order number but also gives the option to cancel
'and go back to the last order entered.
If IsNull(Me!OrderNumber) Or Len(Me!OrderNumber) < 6 _
Or Not Me!OrderNumber Like "##-####" Then
   If (MsgBox("Click OK to enter an order number in the format 03-1234" _
    & vbCrLf & "or click CANCEL to return to the last order entered.", _
    vbOKCancel, "Every order must have a correct order number!") _
    = vbOK) Then
        Me!OrderNumber.SetFocus
    Else
        DoCmd.GoToRecord , , acLast
        Me!OrderNumber.SetFocus
        gintFlag = (gintFlag + 1)
    End If
End If
End Sub

 

[Nouba]

If gintFlag = (gintFlag + 1) Then

might never be true. Revise your logic.

Setting the Cancel Argument of the Form's Close event to True, will prevent the form from closing.

 

[judgehopkins]

Unload vs close

I believe the unload event comes before the close event. You can cancel an unload event but not a close event.

 

[Pat Hartman]

You might consider having the form's BeforeUpdate event call the check order number procedure. If the user simply scrolls to another record, bad data could get added to the table because this edit is not done. Of course, you would need to modify it to have it work properly in that event.

 

[judgehopkins]

I am now using the code below, but it still gives me problem, if the user cancels and goes to the previous record, that there is still a record there with an incorrect order number.

I can make the function delete the offending record, but that is a bit drastic and makes users feel odd when they've done something unintentional that results in a deleted record.

Also, I do not want to make the order number required in the table; I want to programmatically make it required.

Private Sub Form_Unload(Cancel As Integer)
'When the user clicks to exit, function
'checks to see if order number is correct
If Not CheckOrderNumber Then
'If order number is not correct
'unload event canceled
'and user exits this sub
    DoCmd.CancelEvent
    Exit Sub
'If order number is correct, following code executes
'When user clicks close, this confirmation box pops up;
'if user selects no, then Access returns user to the form
ElseIf MsgBox("Are you sure you want to exit?", _
vbYesNo, "Do you want to close?") = vbNo Then
    Cancel = True
End If

End Sub

Private Function CheckOrderNumber() As Boolean
'This function requires the user to enter
'a correct order number but also gives the option to cancel
'and go back to the previous order entered.
'Returns true if exit is possible, false if not
'used in form unload event
Dim bResult As Boolean
bResult = True
'order number must be in format 03-1234
If IsNull(Me!OrderNumber) Or Len(Me!OrderNumber) < 6 _
Or Not Me!OrderNumber Like "##-####" Then
    If (MsgBox("Click OK to enter an order number in the format 03-1234" _
    & vbCrLf & "or click CANCEL to return to the last order entered.", _
    vbOKCancel, "Every order must have a correct order number!") _
    = vbOK) Then
        Me!Refinance.SetFocus
        Me!OrderNumber.SetFocus
'Access bug makes you set focus on next control
'then return it to control you really want it on
        bResult = False
'user has chosen to enter correct order number
Else
        DoCmd.GoToRecord , , acPrevious
        Me!Refinance.SetFocus
        Me!OrderNumber.SetFocus
        bResult = False
'user has chosen to go to previous order number
    End If
End If
    CheckOrderNumber = bResult
'the unload form procedure checks this

End Function

 

[Pat Hartman]

All right. I won't equivicate. Move the code that checks the order number to the form's BeforeUpdate event. Checkiing it in the Unload event is too late. The bad data has already been saved. Check it out if you don't believe me.

With the edit in the form's BeforeUpdate event, if you discover an error use the event's Cancel argument to cancel the update. This is the only certain place to prevent an update from happening.

Cancel = True

By intercepting the error in the correct event, you won't have to go back to a previous record and you won't run the risk of your user simply ignoring your error message.

I would still set the field to required in the table. It is too risky to not do so. Wanting to intercept the standard Access error message is fine but don't bypass Jet's ability to enforce referential integrity to do so.

 

[judgehopkins]

Thanks for your replies!

Now I am trying this.

I want all the info from the form deleted. The code below clears everything but when you go to another record, the boxes are empty. The table hasn't been changed but there is nothing in the dropdown boxes!

I tried a requery after
MyControl.RowSource = ""
MyControl = ""
but the requery will then not let me go back to the previous record.

I just want to delete the entry in listbox.

Is that possible?

‘clears all the data from a form

Private Function ClearForm()
Dim MyControl As Control


For Each MyControl In Me.Controls
   If TypeOf MyControl Is TextBox Then
       MyControl.Enabled = True
       MyControl.Visible = True
       MyControl = ""
   ElseIf TypeOf MyControl Is ComboBox Then
       MyControl.Enabled = True
       MyControl.Visible = True
       MyControl.SetFocus
       MyControl.RowSource = ""   
       MyControl = ""  
   ElseIf TypeOf MyControl Is ListBox Then
       MyControl.Enabled = True
       MyControl.Visible = True
       MyControl.SetFocus
       MyControl.ListIndex = False
   ElseIf TypeOf MyControl Is CheckBox Then
       MyControl.Enabled = True
       MyControl.Visible = True
       MyControl = False
   End If
Next MyControl
End Function