Solved What causes a sub form being locked?

[The_Doc_Man]

When considering options, be aware that Access forms have a displayed size limit. If you are "packing" a form with lots of sub-forms, you will be doing your best to widen the form to keep as much together in parallel. When doing so, the outermost (and therefore the widest) form will be limited to a width of 22.75 inches. Since modern displays can get fairly big, just watch out for that particular limit.

 

[KitaYama]

When considering options, be aware that Access forms have a displayed size limit. If you are "packing" a form with lots of sub-forms, you will be doing your best to widen the form to keep as much together in parallel. When doing so, the outermost (and therefore the widest) form will be limited to a width of 22.75 inches. Since modern displays can get fairly big, just watch out for that particular limit.

Didn’t know about that. Though we don’t need a 22 inch size form, but it’s good to know the limits.
we will add your point in our discussions.
Thanks for the tI’m.

 

[ebs17]

The data on the main form should not be saved until the user checks a lot of options and see if the result is acceptable (simulation, stress and other factor’s result).

It is bad and problematic to start editing a record in a bound form and then do something completely different before finishing - perhaps the user will add a visit to the doctor?

Shouldn't it be the case that when you enter a record, all mandatory entries must be present and enterable? Therefore, you would have to put together such mandatory information before starting the main record.
Choice information can be provided after the record has been saved. Maybe additional timestamps as flags for “under observation” and “completed” will help.

A simple solution would be to use the main form for the new creation as an unbound form and have the record displayed as a group of unbound controls. Then you can freely play in other places.
When you think you have all the necessary information and insights, you then transfer the control contents to the correct table record using a query or recordset statement.

 

[Edgar_]

I need to click on one of the records of the subforms while the main form is dirty.

The suggested unbound listbox offers that behavior. If saving the record by switching to a subform is not the behavior needed, you might want to consider deleting the saved record. Alternatively, an unbound form does not have a dirty property, but you would handle the saving process yourself.

 

[KitaYama]

perhaps the user will add a visit to the doctor?

I like your posts and read every single of them. I also admire your intelligence and the way you solve problems. Your answers, specially the queries you offer has been always a surprise.
But I also have to admit sometimes I don’t like the way you talk to others. I find some kind of humiliation in them and the way someone talk from above to others beneath. I know you don’t mean ill and maybe it’s just because of my shortcoming in English, but sometimes I find your sarcastic comments hurts very bad, and leaves a scar.
I think this one is one of those. But again, maybe it’s because of the difference in our cultures.

Shouldn't it be the case that when you enter a record, all mandatory entries must be present and enterable?

Then you have to give them a pen and a paper and tell them to calculate first and when you have all the pieces, come back to the form and input whatever you have. Sometimes data entering is not just a simple order to type it from an order sheet. In our case, each calculation is based on previous results and the form gets dirty with first set of input.
For example VBA can not start the first set of calculation until user selects the finish surface and the delivery date can not be estimated until the tools and necessary limits are selected. And each set of data are pre calculated based on the customer. As I said, at first all these pieces were unbound combo boxes and listboxes in the same form, But because we need to have a better visual effects on selected items and being able to use conditional formatting, we decided to test what will happen if we use independent forms.

A simple solution would be to use the main form for the new creation

I think I like this idea. I‘ll do some experiments to see how it goes.

Million thanks for your time, advice and help.

 

[ebs17]

but sometimes I find your sarcastic comments hurts very bad

I like to exaggerate identified problems to make them clearly visible. In this specific case, the chosen statement may not be understandable to everyone and may be misinterpreted. However, it should point out that there is a possibility that the user will not achieve a result in a timely manner or not at all with the necessary side actions. What then happens to the unfinished record?

But I comment on things and facts, not on people, so I see no basis for defamation.

 

[LarryE]

As I said in Post #14, just remove the Cancel=True in the forms BeforeUpdate event and it will work. That line prevented you from selecting records in the sub-form.

 

[mike60smart]

I guess the question is, why do you have to move focus to a different form while you are editing the main form?

Mike didn't tell us in words what he did but it looks like he removed the subform and replaced it with a query in the ControlSource of the subform control. Therefore, the control isn't showing a subform and so the save trigger isn't activated

Hi Pat

My apologies for not saying what I did.

The Op stated that they wanted to select Multiple Records.
So I removed the subform and created a Listbox which was set as Multi select

 

[KitaYama]

@Pat Hartman @mike60smart
I really appreciate your concern and trying to help. But as I said before (#23 #28 and maybe prior posts too) these were listboxes at first. Because of some problems we had faced (not being able to use conditional formatting and others) these combo and list boxes were changed to independent forms that users could choose from.
We are now experimenting what will happen if the forms are used as sub forms.

I set the thread as solved because:
According to what I read up to here, There's no way to use a subform instead of a listbox, because the moment I click on subform, the form tries to save the record and BeforeUpdate event of the main form is fired.

I appreciate all members who shared their experience and tried to help.
Million thanks to all.

 

[LarryE]

because the moment I click on subform, the form tries to save the record and BeforeUpdate event of the main form is fired.

For the third and final time. Delete the parent forms BeforeUpdate event and you can select all the records you want in the sub-form.

 

[KitaYama]

For the third and final time. Delete the parent forms BeforeUpdate event and you can select all the records you want in the sub-form.

If I delete Cancel=True, the main form's record gets saved. I don't want the main form's record being saved. I think I've been clear in my request.
I really don't know in what language I have to explain.

The exact phrase from #1, my question says:
Is there any way to select a record in a subform while the main form is still dirty?

The Cancel=True was added simply to prevent the main form's record being saved.

Again from my #1:
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.
It means the first test was done without it.

Thanks again for your help.

 

[The_Doc_Man]

For the third and final time. Delete the parent forms BeforeUpdate event and you can select all the records you want in the sub-form.

LarryE, KitaYama doesn't want the main form to save when doing this selection. Selecting at least one record in the sub-form will do the unwanted action and that is the nexus of this discussion.

If you have no user-defined BeforeUpdate event, it doesn't matter - because Access DOES have its own internalized BeforeUpdate event. What did you think actually fired YOUR BeforeUpdate event? An update will occur whether or not you have any VBA in place. Just like a form will have an Open, Load, and Current event whether you added your own macro or VBA. In fact, it is possible to have a basic form with sub-forms that have no code ANYWHERE. Every event that could happen to such forms WILL happen.

Now, if you dirty the main form then switch to the sub-form, the main form data gets saved during the sequence of events triggered by the change of focus. If you then switch to a SECOND sub-form on the same main without stepping into the main, it won't matter, because the main form is no longer dirty and you don't save clean forms. BUT having no BeforeUpdate event wouldn't prevent the initial main form data save.

It is sort of like the old conundrum of "If a tree falls in a forest and no one is around to hear it, does it make a noise?" Well in this case, "If a bound form changes records but has no VBA code or macro associated defined within it, does the Current Event still happen?" In the latter case, YES. It doesn't do much - but it still happens.

 

[gemma-the-husky]

@KitaYama

As you note that the subform is not a true subform, just another form, why open it within your form. Just open it as a standalone form, and then you should be able to browse it.

I have an app where users can open a number of related but independent forms and place them wherever they want on the desktop. Access remembers the layout, and stores that configuration, and opens/reopens the container form as to the users preferences.

I very rarely use dialog forms. I prefer to let users open any forms they wish to be able to review things, as you are doing.

While in a form, if I decide to open another browse from, I tend to pause execution of the first form.
By doing it this way, you don't get a non-resizeable "popup" form, and you can continue to use other database functions because of the doevents in the pause loop. I appreciate this usage is slightly different to what you are doing, and may not be needed for your circumstance.

So

code in first form
....
....
domcd.openform "newform"
'pause loop
'wait for "newform" to close before continuing
while isopen("newform")
  doevents
wend

the isopen function is this. It expects a form by default, but can pause for any object type

Public Function IsOpen(strForm As String, Optional doctype As Long = acForm) As Boolean
    IsOpen = (SysCmd(acSysCmdGetObjectState, doctype, strForm) <> 0)
End Function

 

[LarryE]

LarryE, KitaYama doesn't want the main form to save when doing this selection. Selecting at least one record in the sub-form will do the unwanted action and that is the nexus of this discussion.

If you have no user-defined BeforeUpdate event, it doesn't matter - because Access DOES have its own internalized BeforeUpdate event. What did you think actually fired YOUR BeforeUpdate event? An update will occur whether or not you have any VBA in place. Just like a form will have an Open, Load, and Current event whether you added your own macro or VBA. In fact, it is possible to have a basic form with sub-forms that have no code ANYWHERE. Every event that could happen to such forms WILL happen.

Now, if you dirty the main form then switch to the sub-form, the main form data gets saved during the sequence of events triggered by the change of focus. If you then switch to a SECOND sub-form on the same main without stepping into the main, it won't matter, because the main form is no longer dirty and you don't save clean forms. BUT having no BeforeUpdate event wouldn't prevent the initial main form data save.

It is sort of like the old conundrum of "If a tree falls in a forest and no one is around to hear it, does it make a noise?" Well in this case, "If a bound form changes records but has no VBA code or macro associated defined within it, does the Current Event still happen?" In the latter case, YES. It doesn't do much - but it still happens.

Yes I understand. The whole thing here defies logic as Pat pointed out earlier. That said, I did some more experimenting and found that the OP's parent forms Data Entry property was set to Yes. With Data Entry set to Yes, you could not even scroll through the Parent form Record Source. I had no idea why someone would want to bind a control on a form and then not save it after making or editing the field. It made no sense. Then I thought OK maybe the OP wants to just scroll the Parent forms data without editing or adding or deleting anything and still select records on the sub-form. It was just a guess. So I set the Data Entry property to No, set the Allow Additions to No, set Allow Deletions to No. Low and behold you could scroll the Record Source and select records in the sub-form without triggering Cancel = Yes.

I am still in the dark as to why the OP is doing this, but obviously it is not using ACCESS as it was intended. So maybe the OP can read this and know how to scroll the parent forms records and still make selections in the sub-form if that was their goal.

 

[KitaYama]

I am still in the dark as to why the OP is doing this

I'm out of words. How many times and how can I explain it any clearer? I am EXPERIMENTING to see if it's possible to use sub forms instead of list boxes and combo boxes in a data entry form. Because list boxes can not use conditional formatting.

• We had several unbound list boxes in a data entry form.
• The list boxes can not accept conditional formatting.
• Instead of list boxes, we used several independent forms that open with the main form.
• Users start their job and while inputting data, they select items from several continuous forms (instead of list boxes) that are opened at the same time. (for some specific calculations)
• Now we have some problems for positioning these forms at the correct location (after a user moves the main form)
• We were trying to see what will happen if those forms were used as sub forms. If the forms are subforms, they move with the main form.
• I ended up with : It's not possible to use them at sub forms (#33)

I'm a foreigner and English is not my native language. I mostly use google translate, so I may have failed to explain the intention. Would someone please translate the above in a more clear way to bring @LarryE out of the dark?

thanks.

 

[ebs17]

Have you already landed on the unbound form? If events and dependencies in a bound form bother me, I eliminate the cause and switch to the unbound form because of a single record in a step-by-step determination of individual content.

 

[KitaYama]

The whole thing here defies logic

I just wanted to add "logic" is a conditional word. When I see a database for an accounting system, I don't judge what is logical and what is not. Because I don't have the slightest idea how an accounting system works.
It surprises me you think what we are doing defies logic without even knowing anything about our business.
I really appreciate your time though. Thanks for your intent to help.

Have you already landed on the unbound form? If events and dependencies in a bound form bother me, I eliminate the cause and switch to the unbound form because of a single record in a step-by-step determination of individual content.

Not landed yet. We are doing some tests to see how it goes. So far, it seems it's the way to go, but I have to take two other supervisors' OK.
thanks again.

 

[KitaYama]

I didn't try this but what if you used reports and the browser control to embed them?

To be honest, what you explained is an unknown world for me and I don't dare to step in an unfamiliar world and do something that I'm not able to correct in case of problems. If for any unknown reason something goes wrong, I don't want to be the one person who's responsible for it.

I just received a message from my supervisor that he had signed and accepted what @ebs17 had suggested earlier. The next update, scheduled for 5th of May will contain our first unbound form.

Thanks for all the comments, advices and help from all who participated.
I think tonight we all can sleep in peace.