Solved What causes a sub form being locked? (1 Viewer)

[KitaYama]

I have a single form with several subforms.
There's no Parent/Child links between them.

I need to click on one of the records of the subforms while the main form is dirty.
If I click on the records I need, the main form's record get saved.
If I add a Cancel=True in before update of the main form to prevent save, the sub form gets locked and I can not click on a record.

What am I doing wrong here?
Is there any way to select a record in a subform while the main form is still dirty?

If I'm not clear in explaining the situation, in the attached file, type anything in the textbox,
and try to select a record in the subform while the form is dirty.

Any kind of advice is much appreciated.

 

[theDBguy]

Can't download your file right now, but if you just cancel the event, then I think the form is still dirty, which means clicking into the subform again simply fires the event again.

Sent from phone...

 

[KitaYama]

Can't download your file right now, but if you just cancel the event, then I think the form is still dirty, which means clicking into the subform again simply fires the event again.

Sent from phone...

You mean there's no way to go to a subform while the main form is still dirty?

 

[The_Doc_Man]

Regardless of the exact linkage or lack thereof, the event that is ACTUALLY doing you in is the side-effect of clicking on the sub-form. When your focus leaves a form, you have a Deactivate event which goes along with saving the contents of the form you just deactivated. This is the same thing that would happen if you had two forms open at the same time in "normalized" state (neither minimized nor maximized). This is also the same thing that would happen if you had somehow opened Access twice with different forms in different windows. Swapping focus away from a form will cause it to trigger a "Save Record."

If your goal is to do something in any other form while your main form is still open, you are going to have this behavior.

 

[KitaYama]

Regardless of the exact linkage or lack thereof,

I had a feeling that since the forms are not linked, they can behave separately, because they're not dependent to their PK/FK.

Seems that I was wrong. I'll go back to what I had before and delete the subform and open it as a form instead of a subform.

Thanks.

 

[The_Doc_Man]

I'll go back to what I had before and delete the subform and open it as a form instead of a subform.

If both forms are open at the same time, the same result will occur. It is the act of leaving the form for ANY OTHER DESTINATION that makes Access want to save the contents of the form in question.

 

[KitaYama]

If both forms are open at the same time, the same result will occur.

No it won't. I can move from one form to another and keep both dirty.

Thanks again.

 

[gemma-the-husky]

How are the subform "subforms", if there is no relationship with the main form?

Do you have an even happening that is causing the misinformation to save when you leave it?

If you are cancelling the firm update, something is making it fire to need to cancel it.

 

[LarryE]

Open the sub-forms record source to see if it is editable. Whether or not the Master/Child fields are present and active should not make a difference. You can have sub-forms without Master/Child links and still be a sub-form and still be editable.

 

[mike60smart]

No it won't. I can move from one form to another and keep both dirty.

Thanks again.

If you remove all of the Events from Both forms you are then able to do what you want.

 

[KitaYama]

How are the subform "subforms", if there is no relationship with the main form?

To my knowledge, any form that is embedded in a main form, is called a sub form. I may be wrong though.
While working with huge amount of data, users need to see relating information to help them having a better idea of what their doing.
I know you're not in a manufacturing business and I guess it's hard for you to understand the situation.

I can give you a hundred examples, but I'm sure it would be hard for you to digest what our business are doing there and why so many info should be brought on screen. Imagine the users search current orders, select one of them to work on it, while viewing the details, they need to see the NC program related to the part's design, current available tools that they can choose for their machine, simulation results, ISO limits that should be used, etc.

Do you have an even happening that is causing the misinformation to save when you leave it?

If you are cancelling the firm update, something is making it fire to need to cancel it.

I'm sorry, but I don't understand what you're talking about. What is misinformation? No misinformation has been saved.
I uploaded a demo. Have a look and do as what I explained to see what I meant.

Thanks for stepping in.

 

[KitaYama]

If you remove all of the Events from Both forms you are then able to do what you want.

No, I can't.
At least with my tests.

 

[KitaYama]

Open the sub-forms record source to see if it is editable. Whether or not the Master/Child fields are present and active should not make a difference. You can have sub-forms without Master/Child links and still be a sub-form and still be editable.

As I said, before main form get dirty, the sub form is editable. But I don't want to edit anything.
I just want to select several records from subform.

thanks.

 

[LarryE]

I found the problem. You have Cancel=True in your FrmOrders BeforeUpdate event. Just delete the BeforeUpdate event and it will work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = True
End Sub

 

[gemma-the-husky]

To my knowledge, any form that is embedded in a main form, is called a sub form. I may be wrong though.
While working with huge amount of data, users need to see relating information to help them having a better idea of what their doing.
I know you're not in a manufacturing business and I guess it's hard for you to understand the situation.

I can give you a hundred examples, but I'm sure it would be hard for you to digest what our business are doing there and why so many info should be brought on screen. Imagine the users search current orders, select one of them to work on it, while viewing the details, they need to see the NC program related to the part's design, current available tools that they can choose for their machine, simulation results, ISO limits that should be used, etc.

I'm sorry, but I don't understand what you're talking about. What is misinformation? No misinformation has been saved.
I uploaded a demo. Have a look and do as what I explained to see what I meant.

Thanks for stepping in.

Sorry, that was a text autocorrect.

I'm not sure without trying it, but I thought you said that if you click from a dirty form into another form, that alone doesn't make the dirty form save.

on the other hand clicking a (related) subform may be different and may cause the main form to save.

so it prompted a query about your process, and the nature of an unrelated subform. If form B does not relate to form A, why would changing form B affect form A?

i thought you also said you added a "cancel = true" to cancel an unintentional update of the main form, implying that either leaving the form itself is causing a record save, or something else is causing the record save. (Eg code in the subform's enter or got focus events)

as I say, it's hard to know without trying it out myself, and I was just thinking out loud to understand the process.

 

[mike60smart]

As I said, before main form get dirty, the sub form is editable. But I don't want to edit anything.
I just want to select several records from subform.

thanks.

Is this what you need to achieve??

 

[mike60smart]

As I said, before main form get dirty, the sub form is editable. But I don't want to edit anything.
I just want to select several records from subform.

thanks.

Here with modified db

 

[Pat Hartman]

What am I doing wrong here?

You have a logic problem. When you leave the mainform to go to a subform, Access saves the main form record. When you leave a subform to go to the main form, Access saves the subform record. Cancel stops the save but it leaves focus on the main form, you still can't leave it until you clear the edit or save the record.

 

[Pat Hartman]

I'm not sure without trying it, but I thought you said that if you click from a dirty form into another form, that alone doesn't make the dirty form save.

on the other hand clicking a (related) subform may be different and may cause the main form to save.

Clicking between two independent forms does not cause one or the other to save. This is why we always have to warn people - when you are opening a popup form or a report - you MUST save the current record or the new form/report will not see it or the changes.

Clicking between a mainform and a subform ALWAYS causes the form you are leaving to attempt to save.

Forms are not placed directly on other forms, technically, they are placed in a subform control which is bound to the subform. Whether the master/child links are set or not does not alter the save behavior of either the main or the subform.

These and many other questions about form behavior can be answered if you play around with the form I built to log events for the videos in this link:

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[KitaYama]

You have a logic problem. When you leave the mainform to go to a subform, Access saves the main form record. When you leave a subform to go to the main form, Access saves the subform record. Cancel stops the save but it leaves focus on the main form, you still can't leave it until you clear the edit or save the record.

Since the main form and the sub form are not linked (Parent/child), I hoped Access behave them as two separate forms. But it seems it’s not so.
Thanks for the tip.