Don't save modified design on exit

R

rtcdoes

Dear Access Guru's

MS Access 2003
I have a form which users can browse to search for data; they can
resort the form anyway they like but at the end I would like to have
the original sort order back, when closing the form users shouldn't be
allowed to save the modified form.
Could you point me in the right direction? Please bear in mind, I'm
not that sophisticated with Macro or VBA...(that is, some basic
understanding available but KISS appreciated...)

Thanks for your reply

*** RTC ***
 
A

Allen Browne

How about clearing any OrderBy when the form opens:

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = vbNullString
Me.OrderByOn = False
End Sub
 
J

John W. Vinson

Dear Access Guru's

MS Access 2003
I have a form which users can browse to search for data; they can
resort the form anyway they like but at the end I would like to have
the original sort order back, when closing the form users shouldn't be
allowed to save the modified form.
Could you point me in the right direction? Please bear in mind, I'm
not that sophisticated with Macro or VBA...(that is, some basic
understanding available but KISS appreciated...)
Click to expand...

You could put a command button named cmdClose on the form; set its Click event
to [Event Procedure], click the ... icon, choose Code Builder, and edit it to

Private Sub cmdClose_Click()
DoCmd.Close acForm,me.Name,acSaveNo
End Sub

Set the Control Box property of the form to No to eliminate the builtin close
button.
 
R

rtcdoes

How about clearing any OrderBy when the form opens:

Private Sub Form_Open(Cancel As Integer)
    Me.OrderBy = vbNullString
    Me.OrderByOn = False
End Sub
Click to expand...

Allen,

Thanks for your suggestion; as I'm a amateur with regards to VBA,
expression etc I have to sit down with my Access Bible to work this
out.
Any basic help would be appreciated (such as cut and paste this
into ....)

Nevertheless I will try your tips and tricks...

Thanks again

*** Rob ***
 
R

rtcdoes

You could put a command button named cmdClose on the form; set its Click event
to [Event Procedure], click the ... icon, choose Code Builder, and edit it to

Private Sub cmdClose_Click()
DoCmd.Close acForm,me.Name,acSaveNo
End Sub

Set the Control Box property of the form to No to eliminate the builtin close
button.
Click to expand...

John,

Thanks for your tip...as I mentioned in my reply to Allen, I'm an self
thaught Access user, just sniffing at the VBA path; so I have to study
your suggestion with my Access Bible besides me to test it out.
Any basic help to implement your tip would be welcome as I already was
fighting with this problem for weeks.

Again your suggestion is very valuable for me, pointing me in a
direction to explore further...

regards

*** RTC ***
 
J

John W. Vinson

You could put a command button named cmdClose on the form; set its Click event
to [Event Procedure], click the ... icon, choose Code Builder, and edit it to

Private Sub cmdClose_Click()
DoCmd.Close acForm,me.Name,acSaveNo
End Sub

Set the Control Box property of the form to No to eliminate the builtin close
button.
Click to expand...

John,

Thanks for your tip...as I mentioned in my reply to Allen, I'm an self
thaught Access user, just sniffing at the VBA path; so I have to study
your suggestion with my Access Bible besides me to test it out.
Any basic help to implement your tip would be welcome as I already was
fighting with this problem for weeks.
Click to expand...

Create a command button using the toolbar (without using the wizard).
View its Properties.
Select the "Click" event property on the Events tab of the property window.
Click the ... icon by it.
Select "Code Builder".
Copy and paste the DoCmd.Close line above, as written, between the Sub and End
Sub lines that Access will give you for free.
Select Debug... Compile.
Close the VBA window.
 
R

rtcdoes

Create a command button using the toolbar (without using the wizard).
View its Properties.
Select the "Click" event property on the Events tab of the property window.
Click the ... icon by it.
Select "Code Builder".
Copy and paste the DoCmd.Close line above, as written, between the Sub and End
Sub lines that Access will give you for free.
Select Debug... Compile.
Close the VBA window.
Click to expand...

John,

thanks for your help...loud and clear...but a command button doesn't
seem to work in datasheet view of a form...
Darn...any workaround?

Appreciate your reply...

*** RTC ***
 
J

John W. Vinson

thanks for your help...loud and clear...but a command button doesn't
seem to work in datasheet view of a form...
Darn...any workaround?
Click to expand...

That's one of several reasons I avoid datasheets! I'd suggest using a
Continuous form; it can be made to look very like a datasheet if you wish
(snug the textboxes and other controls side by side at the very top of the
Detail section and drag the bottom of the section up to the controls), and put
the button in the form's Header or Footer.
 
D

David W. Fenton

That's one of several reasons I avoid datasheets! I'd suggest
using a Continuous form; it can be made to look very like a
datasheet if you wish (snug the textboxes and other controls side
by side at the very top of the Detail section and drag the bottom
of the section up to the controls), and put the button in the
form's Header or Footer.
Click to expand...

There are major ways in which a continuous form cannot be made to
work like a datasheet:

1. no hiding or changing widths of colums

2. arrow keys don't work in all directions from record to record,
but only within a record.

So, I'd definitely recommend not trying to make a continuous form
look like a datasheet -- that will just confuse the user about why
they can't do certain things.

Instead, you can embed the datasheet in an unbound parent form and
set the subform's properties in the command button on the parent
form, instead of using acSaveNo with DoCmd.Close. Of course, it may
be that acSaveNo applies to the subform and not just the main form,
so that might work, too. But to be certain, I'd likely set the
filter and order by properties to blank in the close.
 
R

rtcdoes

There are major ways in which a continuous form cannot be made to
work like a datasheet:

1. no hiding or changing widths of colums

2. arrow keys don't work in all directions from record to record,
but only within a record.

So, I'd definitely recommend not trying to make a continuous form
look like a datasheet -- that will just confuse the user about why
they can't do certain things.

Instead, you can embed the datasheet in an unbound parent form and
set the subform's properties in the command button on the parent
form, instead of using acSaveNo with DoCmd.Close. Of course, it may
be that acSaveNo applies to the subform and not just the main form,
so that might work, too. But to be certain, I'd likely set the
filter and order by properties to blank in the close.
Click to expand...

John and David,

points well taken...I'm struggling with both your suggestions
Davids remarks with re to the continuous forms are sound and I'm
trying the embedded option.

Please don't hesitate to send me all your suggestions...;-)

Thanks again

*** RTC ***
 
R

rtcdoes

Hai All,

A follow up on this question.

I finally got the solution...the answer came from Brian on
aswerbag.com.
This is his take:


Make sure that the Form is based on a Query and the Query is set to
sort by the field you want as the primary sort order.

Open the Form in Design View, right click the small grey box at the
top left of the Form and select Properties.

Select the Event tab of the Properties sheet and then click in the 'On
Open' input box.

Click on the Builder button that appears to the right of the 'On Open'
input box and select Macro Builder.

In the first line of the Macro Builder select 'Apply Filter' and type
the name of the query on which the Form is based where it says 'Filter
Name' at the bottom of the Macro Builder window.

Save the Macro and every time the Form is opened it will be sorted the
same way as the underlying query.


http://www.answerbag.com/q_view/1178907


So I’m more than happy now….thanks John and David for your suggestions
and input…
All credits to Brian on Answerbag.com


*** RTC ***
 
Top