[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Any way to enforce subfrom edits from main form

Posted on 2017-10-12
Medium Priority
Last Modified: 2017-10-13
In my Access 2013 application I have a mainform 'frmDocumentDefinition' with two subforms 'frmDocumentInterestedParties_Sub' and 'frmDocumentTriggeredCosts_Sub' on it.

The two subforms are continuous forms.  Within the subforms I have an edit routine in the 'before update' event for each record entered or updated on the subforms to make sure that all required fields have been entered on each line.  The edits work and put out messages for any errors encountered.  While working only in the subform the user cannot go to another line until all the edits for the current line are passed.

The problem occurs when users clicks anywhere on the main form while working on an incomplete line in one of the subforms.  When that occurs:

The subform edits are invoked and any subform error messages are displayed " Cost Date is a required field."

However, after the error message from the subform is displayed the focus shifts to the main form.  The bound subform record is saved but it has the errors in it.  For example: required fields are blank.

How do I stop the subform from losing focus when there are edit errors on the subform?  

How can I make sure the edits on the subforms are enfoorced from the the main form?
Question by:mlcktmguy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

Anders Ebro (Microsoft MVP) earned 2000 total points
In the subforms before update event, are you setting Cancel=true, when the required fields are missing? The will prevent the record from being saved, and that should also prevent the focus from moving to the main form.

Also if the field must ALWAYS be filled out, then you should, at the table level set the field to be required. Don't get me wrong, the msgbox in the beforeUpdate event is still a good idea, as it presents a much more user friendly message.
LVL 52

Expert Comment

by:Gustav Brock
Also, the subform controls on the main form have an OnExit  event you might be able to use.

LVL 48

Expert Comment

by:Dale Fye
Agree with Gustav, the Exit event of the subform has a Cancel argument, which allows you to cancel exiting the form.  you could simply test to determine whether the subform is dirty and cancel if it is still dirty, something like:
Private Sub SubformControlName_Exit(Cancel as Integer)

    Cancel = me.subformControlName.Form.Dirty

end sub

Open in new window


Author Closing Comment

Anders asked: "In the subforms before update event, are you setting Cancel=true"

I was not setting the Cancel=True

That works and keeps the focus on the subform.  Thanks.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Join & Write a Comment

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question