Before_Update with Command Close button on Subform

[Ruth]

My form/subform represents a contract with multiple individual line items for amounts to be spent on different projects. The goal of this form is to add more line items to an existing contract. The main form displays the contract header information (for tblContract); the subform is for entering detail line items (for tblContract_Lines). The tables/forms are linked on Contract_Number. The main form information is not editable, the form opens with the focus on the subform. The subform is a continuous form, so that more than one line item can be added to the contract.

-I have a command button ("OK") to close the form. The command button is located on the subform.
-I have a before_update event on the subform itself (not the subform control of the main form) to validate fields.
-I have an after_update event on the subform itself (again not the subform control of the main form) to ask if the user wants to add another line item (the msgbox is fired if the user either tabs through all the controls and completes the data entry on a line, or if the user clicks OK to signal they're completed).

My problem is that if the before_update event results in a fail, it does not return the user to the form field. It basically goes back to the close command button and closes the form. Here's the code:

Private Sub Close_Form_Button_Click()
    DoCmd.Close acForm, "frmMod_Contract_Form"
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull([Forms]![frmMod_Contract_Form]![sbfrmMod_Contract_Lines].[Form]![Project_Number]) Then
      MsgBox "Please enter a Project Number."
      [Forms]![frmMod_Contract_Form]![sbfrmMod_Contract_Lines].[Form]![Project_Number].SetFocus
      Cancel = True
      Exit Sub
    End If
End Sub
 
Private Sub Form_AfterUpdate()
    Dim intAnswer As Integer
        intAnswer = MsgBox("Are you done entering contract modification lines?", _
            vbQuestion + vbYesNo, "Contract Modification")
        If intAnswer = vbYes Then
            DoCmd.Close acForm, "frmMod_Contract_Form"
            Exit Sub
        Else
            DoCmd.GoToRecord , , acNewRec
            Me!Amount.SetFocus
        End If
End Sub

Thank you for any help.

 

[pono1]

For starters try working with this code in the subform's before update event handler...

Private Sub Form_BeforeUpdate(Cancel As Integer)

  If IsNull(Me.Project_Number) Or Me.Project_Number = 0 Then
    MsgBox "Please enter a Project Number."
    Cancel = True
  End If

End Sub

Regards,
Tim

 

[Ruth]

Hi Tim,

I put in the code just as you say, but get the same result. I know that it's catching the error, because I get the msgbox. But once I click OK on the msgbox, the form closes. It doesn't take me back to the missing data box on the form.

Thanks, Ruth

Ruth

 

[pono1]

The subform closes? Is the subform embedded in the main form?

Answers aside...a proposal for working through this: Create a copy of your Access DB file and then strip out all code from your subform except the BeforeUpdate code...

Run a test to trigger the Before Update event...

If the form closes then
-- I don't know why because I can't reproduce this behavior.
Else
-- Add a single piece of the previous code to the sub and test again.

Keep adding chunks of code to the subform and testing after each change. When the form inexplicably closes again, you can blame the behavior on the last change you made. And you should then closely examine the added code in an effort to debug it.

Regards,
Tim

 

[CameronM]

Just a thought, but woudn't you expect the form to close if the user presses the close button? The cancel=True call in the Before_Update event relates only to the updating of the current record, it has no magical 'Stop all previous commands' functionality.

You would have to validate your form, prior to using DoCmd.Close acForm, "frmMod_Contract_Form".

Private Sub Close_Form_Button_Click()
    If ValidateForm Then
        DoCmd.Close acForm, "frmMod_Contract_Form"
    End If
End Sub

Then create a new Function Called ValidateForm which does the work on checking the form has all the details entered.

Private Function ValidateForm() As Boolean
    Dim blnValidate As Boolean
 
    'add your code to validate the subform and return either true or false
 
    ValidateForm = blnValidate
End Function