Access Form  Before Update Event on button click to change filter on displayed records. Error Handling.

I have a form with a subform.

On the  main form (frmcases) there are two buttons. One filters the records in the subform (frmCare_Options) to show only those in the "Past Requirements" section. The other shows only those in the "Future Requirements" section.

When the  main form is first opened it shows the  "Past Requirements"  records.

The checking for any errors when entering a Past Requirement or a Future Requirement (obviously) record on the subform is on the Before Update event of the form.

EVERYTHING WORKS FINE UNLESS THERE ARE NO  PAST REQUIREMENTS RECORDS WHEN THE FORMS FIRST OPEN.

My problem happens when there are no Past requirement records, and the form has just been opened, but you decide you want to enter a Future Requirements record and  click the "Future Requirements" button. If there was a Past requirements record there and  it was in error the error message comes up  fine , you correct it and then clcik the Future requirements button again.

But if there wasn't a Past Requirements record (just a blank form with some default values showing)  when you click the Future Requirements button, the Before Update Event fires but the the  onclick event on the Future Requirements button also starts and  won't cancel (or I can't cancel it). So what happens is the before update error on the Past Requirements record comes up with the correct message etc. but rather than go back to showing the record that was in error to let you re-enter the data or Esc,  the system continues to process the commands for the Future Requirements  button event and gets into a bit of a pickle (running the after update events again etc...).

Just to reiterate this only happens when there are no past requirements records and ONLY when the form has just been loaded. If you go back to the main record with no past requirements later and click the future requirements the process works fine - tells you all the required fields haven't been entered and you can correct that or esc before pressing teh Future Requirements button again to carry on..

SO : I want the Past requirements button event to be cancelled if the before update event on the  "Past requirements" record picked up an error - like what happens in all circumsatance except on inital load when there a re no Past Requirements.  or ignore the Past Requirments untouched blank (apart from defaults) record.

SEE RELEVANT CODE BELOW.

Thanks Lou



CODE FOR BEFORE UPDATE EVENT
Private Sub Form_BeforeUpdate(Cancel As Integer)
' make sure a care option description is entered

On Error GoTo Error
     
    If Len(Me!CO_Desc.Value & vbNullString) = 0 Then
         Call CCEntryReq
         Cancel = True
    End If
    Exit Sub
   
Error:
    MsgBox Err.Number & " " & Err.Description
    Resume Next
End Sub

Public Function CCEntryReq()

MsgBox "All mandatory fields (those marked *) do not have a value entered. Please make an entry in all these fields or press Esc to abandon this record.", 48, _
        "Care Report Costing System"
       
End Function

CODE FOR FUTURE REQUIREMENT BUTTON ON CLICK EVENT
Private Sub btnFuture_Click()
'Highlights the Future Requirements button and filters the records to those equal to section 1
 
    On Error GoTo Error
   
    Me.Form![frmCare_Options].SetFocus
    Me.[frmCare_Options].Form.Visible = True
    Me.[frmCase_Equipment].Form.Visible = False
   
    CCHighlightSection Me.btnFuture
    CCInactiveSection Me.btnPastReq
    CCInactiveSection Me.btnEq
    CCInactiveSection Me.btnReport
   
    Me.Form![frmCare_Options].Form.RecordSource = "qryCOFuture_Section"
    lngSection = 2 ' so section can be set on a care option new record later
    If Me.[frmCare_Options].Form.NewRecord Then
     frmCare_Options.Form!C_Section = lngSection
    End If
 
ExitSub:
    Exit Sub

Error:
    MsgBox Err.Number & " " & Err.Description
    Resume Next

End Sub


Start Free Trial