acCmdUnHideColumns

[Mark Andrews]

Question on a little code:

Access2007 I have a form "frmFilter" and a subform "frmFilterContacts"
The subform is a datasheet.

I have a button on frmFilter with the following code:

Private Sub cmdShowHideFields_Click()
Me.frmFilterContacts1.SetFocus
DoCmd.RunCommand acCmdUnhideColumns
DoCmd.Save
End Sub

The first two lines work fine and popup the list of fields in the datasheet
and allow the user to change which ones are shown/hidden.

The question is how do I suppress the "Do you want to save changes to the
design of form "frmFilterContacts"?
The DoCmd.Save suppressed the same question on "frmFilter".

I tried a few things but thought this was a good question for the newsgroup.

Thanks,
Mark

 

[S.Clark]

Issue a Docmd.Close, and use the Save parameter to specifically save or not
save.

 

[Mark Andrews]

If I use:
DoCmd.Close acForm, "frmFilter", acSaveYes
and/or
DoCmd.Close acForm, "frmFilterContacts", acSaveYes

That's not working. It still prompts do I want to save both forms.
I'm trying to avoid the prompt.

It sounds like acSavePrompt would prompt and acsaveYes would not prompt but
that's not what I am seeing.

Any other help is appreciated,
Mark

PS: Access2007 SP2

 

[Douglas J. Steele]

The problem is, the subform frmFilterContacts isn't actually open as a form.
To be honest, I'm not sure how you'd be able to do this.

 

[Mark Andrews]

I can wrap the doCmd.close in docmd.setwarnings false/true and that works
for my "save and Close" button

Only part that doesn't work is if user clicks the built-in close button (x
in upper right) which I want them to be able to do. It still prompts.

Close but still looking to make it work better (hoping for this software to
be commercial).

Mark

 

[Mark Andrews]

I'm sorry MVPs are not allowed to be stumped.

How about a way to prevent the prompt when the user closes the main form by
click the
close button (upper right corner)? I don't care if things get saved or not
when they close the form this way.

Mark

 

[S.Clark]

In the OnClose, does adding the .Save there help?

Another thought is that if you're going commercial, you'll probably
implement Access User Level Security. If the user only has read-only, will
it still prompt?

 

[Mark Andrews]

No I tried putting it in the Form Close event and that causes other
problems.

It's Access2007 so no Access security being implemented. It will be changed
to an
ACCDE. I should check out that behavior to see what happens?

Mark

 

[Mark Andrews]

In an ACCDE it doesn't allow saving so, Yea no prompts but BOO I can't ever
save changes to datasheets (all I want is for the user to be able to
show/hide fields).

 

[Douglas J. Steele]

If you remove the DoCmd.Save, do you still get prompted?

 

[Douglas J. Steele]

Why do you need to save the changes?

 

[Mark Andrews]

Doug,

Yes. It prompts for saving both the form and sub form. With DoCmd.save,
prompt is only for subform.

However in the long run this will be an ACCDE and you can't save at all, so
no prompts but no saving either.
Since I use show/hide fields all over the place I started another thread
with the new question about how to handle show/hide fields in datasheets in
an accde. Take a look at that one, that's where I really need the help.

Saving info about datasheet configuration (fields shown/hidden, order of
fields and widths of fields) anytime you leave a form and restoring the info
anytime you open the form. I only use this show/hide in one place in a
sub-form, the rest are split forms.

Thanks,
Mark

 

[Mark Andrews]

Here's a scenario:
The majority of my application is structured like this:
- user clicks a ribbon button (let's say "Contacts")
- a split form is shown with some buttons at the top "New contact",
show/hide fields etc...
the datasheet takes up most of the area on the screen
on every row of the datasheet the first column is a hyperlink with "Open"
when clicked it jumps to the detail form.

On Contacts their might be 40 fields possible to display and the user might
want to put them in a certain order and show/hide some so it works well for
their use. It would be good if this information was retained so the form
always looks the same for them. Column sizes might also be adjusted to work
well with their data.

That's the majority of datasheet options in my program.

For this particular one I have a system to allow the user to basically build
a query to help with reporting using forms I have built (well I'm building
them now).
The user might setup a query for "show contacts that are "active" in group
"marketing" and have donations last year > $1000 and this subform shows the
35 contacts that meet that criteria. Similar reason for the show/hide
fields (just to make it nicer for the user). This query could then be used
to drive a report or filter a datasheet of contacts etc....

Thanks for your help,
Mark