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

[RickSanchez]

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:

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:

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

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.

 

[Gasman]

Where were you putting that code?

 

[RickSanchez]

Where were you putting that code?

In the main form's On Open event

 

[theDBguy]

In the main form's On Open event

Have you tried?

DoCmd.Close acForm, Me.Name, acSaveNo

 

[Gasman]

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.

 

[RickSanchez]

Have you tried?

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.

 

[June7]

Cross-post https://stackoverflow.com/questions...e-layout-of-a-subform-query-on-an-access-form

 

[RickSanchez]

Cross-post

That's my account. I chose to ask on here as well because I wasn't getting any answers on SO. When I get an answer somewhere, I can delete the question on the other site.

 

[RickSanchez]

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?

 

[MajP]

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.

 

[Gasman]

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.

 

[Gasman]

Cross-post https://stackoverflow.com/questions...e-layout-of-a-subform-query-on-an-access-form

Thank you @June7

 

[RickSanchez]

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!