How do I save the layout of a subform after modifying it on the main form's on open event?

RickSanchez

New member
Local time
Today, 04:40
Joined
May 22, 2025
Messages
6
I have a main form where, on the main form's On Open event, it changes the properties of a subform query by hiding/showing a column, depending on the user's permission level:

Code:
Private Sub Form_Open(Cancel As Integer)

    If IsUserInGroup("CostPrice") = True Then
        [subDefaultBOM].Form.Controls("Standard Cost").Properties("ColumnHidden") = False
    Else
        [subDefaultBOM].Form.Controls("Standard Cost").Properties("ColumnHidden") = True
    End If
End Sub

The problem is that when the user closes the form, it asks them if they want to save the changes that were made above:

1747942161921.png


I need it to either not ask to save or to just automatically save it, without bothering the user with this. What can I do here? I've already tried
Code:
DoCmd.Save acForm, Me.Name
, but this didn't work because not only did it still ask me to save the changes to the subform query but also the main form.
 
Last edited:
In the main form's On Open event
Well if you are altering the subform, you need to address the subform surely, not the mainform, or both if @the Db guy's solution does not work. Probably best not to save thinking about it.
 
Have you tried?
Code:
DoCmd.Close acForm, Me.Name, acSaveNo
That would work if I create a dedicated Close button, but then when I'm doing development, it still prompts me to save when I hop back and forth between form view and design view. Ideally, I'd like to modify the subform's property and then save it somehow.
 
Well if you are altering the subform, you need to address the subform surely, not the mainform, or both if @the Db guy's solution does not work. Probably best not to save thinking about it.
How do I make the subform "save"? Also, why do you think it's not a good idea to save?
 
You should not have to save or get this message. I do this all the time without any prompt. However it looks to me that your subform source object is a query object and not a form in datasheet. Make sure to use a datasheet form and your problem should go away. Also call the code in the on load event and not the on open event.
 
How do I make the subform "save"? Also, why do you think it's not a good idea to save?
Because you likely will change it the next time it loads.
If you have to cross post, at least have the decency to advise all the sites, so people do not waste their time offering advice that has been offered already. :(
 
You should not have to save or get this message. I do this all the time without any prompt. However it looks to me that your subform source object is a query object and not a form in datasheet. Make sure to use a datasheet form and your problem should go away. Also call the code in the on load event and not the on open event.
This worked, thank you!
 

Users who are viewing this thread

Back
Top Bottom