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

[andi_g69]

On a first glance I think your condition
...
If gintFlag = (gintFlag + 1) Then
...
will never be true.

[judgehopkins]

Your reply helped with my debugging.

I haven't got the rest of the problems solved, but I'm working on it.

Thank you!

[andi_g69]

Try this:

Option Compare Database
Option Explicit

Private Sub Form_Unload(Cancel As Integer)

If Not CheckOrderNumberTest Then
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 Function CheckOrderNumberTest() As Boolean
'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.
' Returns True if exit is possible, false if not
Dim bResult As Boolean
bResult = True
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
bResult = False
Else
DoCmd.GoToRecord , , acLast
Me!OrderNumber.SetFocus
bResult = False
End If
End If
CheckOrderNumberTest = bResult
End Function

[judgehopkins]

andi_g69 this code works great for checking the current record1

Thanks!

Is there any way to make it check that every order number has been filled out correctly?

I do not want to set the order number property on the table as required (because of the weird Access error messages), but I want to programmatically require the order number to be filled out correctly on every record.

Thank you for your help!

[andi_g69]

It should be possible to use the same sort of code in the OnBeforeUpdate event of the form.

[judgehopkins]

Thanks for your reply. I want to do something that I do not think Access is capable of.

I want to clear the entries in a form in only one situation: where the user has gone back to a saved record, changed the order number (which is programmatically required but is not set as required on the table), and then attempts to go to the next record or exit, but has put an order number in which is not correct. The order number must be formatted like 03-1234.

I would like for this to happen in that one situation: the user is given the choice via a message box to stay on the current record and correct the order number or to cancel and clear the form.

The before update event has already happened (this is a saved record). I do not want to disable the control for the order number because the users need to be able to change it if they have made a mistake in entering it the first time.

The code below does clear everything. However, when the user goes to a new record, the listboxes are empty, i.e., no choices show up. I suspect I need to do a requery but the lines below do not work. The list boxes are bound controls so I should be able to requery them but I am doing something wrong!

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 = ""   ' this line & next are problems
        MyControl = ""                       
‘ listboxes have nothing in them on next record
        DoCmd.Requery (MyControl) ‘ this doesn’t solve the problem
    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

[andi_g69]

Sorry, but I can't really follow.
1) You say the user might change the order number. In this case the OnBeforeUpdate does happen, because Access will attempt to save the updated record to the table.

2) The user should be able to cancel, in this case you want to clear the form. This means you lead the user to an infinite loop, because he has an empty form, therefore an empty order number, which violates your checking whenever the user wants to leave the record or the entire form.

3) Did you think about a special update form for the order number? You can do it like this:
1) Disable the Order Number field if a user goes to an existing record
2) Offer a button "Correct Order Number"
3) OnClick popup a form with a single field for the new order number
4) Check the newly entered Order Number and assign it to the original control (field) if it is formally correct.

[judgehopkins]

Excellent suggestions all.

I will try them.

Thanks for your reply!

[Gelu]

Hi,
One idea is to set the property REQUIRED to YES in in the table from which come the order. In this way you can't save the record until this field is filled.
Gelu

[judgehopkins]

Gelu, I programmatically set the control to required because of the incomprehensible error message that Access generates when you set a field to required in the table.

Thanks for your reply!