How to stop saving on a subform.

P

pokdbz

I have a subform for a call log. When you input something into a field on
the subform and tab it saves it. I have a save button to save on the
subform. I only want the data to save when the button is hit not when the
focus is away from a field or if the database is closed

How can I go about doing this?
 
B

Biz Enhancer

Don't bind the subform to a table. Instead either have it unbound or bound
to a dummy table. On the Click event for your save button run an SQL
statement for APPEND or UPDATE. I.e. Docmd.RunSQL "your SQL string"
The same code could be run on the close event on the form (be careful about
duplicating the record) if it is shut down with out saving.

HTH,
Nick.
 
B

boblarson

Not necessary - just use the Before Update event of the form to decide
whether to keep or discard the information. If you want to discard, use

Cancel = True
Me.Undo

If you don't discard, just let it update.

In the button to save you can use

If Me.Dirty Then Me.Dirty = False

to force a save and then the before update event will fire. You can set a
public boolean variable to true to determine if your button was pushed. If
it wasn't the variable won't be set and you can discard the record.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________
 
P

pokdbz

What is happening is when you input information on the subform in a field and
then hit Tab through the rest of the fields it goes to a new record and saves
the information.

How can I use the before update event to stop this?
 
B

Biz Enhancer

Is your subform a single form or continuous form? From memory if it is a
single form you can stop it moving to a new record by setting the Cycle
property to Current Record instead of All Records. May work for continuous
forms as well.
 
P

pokdbz

Thanks the Cycle property worked great.

Any ideas on the before update to stop the saving?
 
R

Rick Brandt

pokdbz said:
Thanks the Cycle property worked great.

Any ideas on the before update to stop the saving?
Click to expand...

Automatic saving occurs whenever you leave a dirty record. The only way to stop
the saving is to stop the leaving of the record and then undo all of the chages
that made the record dirty in the first place.

If what you want is to leave the changes on the form and still be able to leave
that record without saving it then you are wasting your time. You cannot do
this with bound forms.
 
P

Pat Hartman

Changing the Cycle property isn't sufficient since you also want to stop the
save when you close the form and when you move focus from the subform back
to the main form. You MUST use the subform's BeforeUpdate event to control
this as was already mentioned by Bob. You need a public variable that is
set in your save button's click event. You then check that variable (and
reset it) in the form's BeforeUpdate event. If the variable wasn't set by
the save button's click event, you would display a message and cancel the
record save by using -
Cancel = True
 
B

Biz Enhancer

Go back to my first answer - use an unbound form or bind to a dummy table.

Access is great for saving data automatically but this is not always
practical.
Most of my work is done with a frontend linking to a SQL server therefore I
don't work on top of the table the way Access tends to in its native form.
I always update backend tables with an SQL statement. It gives me greater
control on what is put in to the database and when the user exits the form in
a dirty condition I pop up a messagebox to confirm that not saving the data
was intentional.

Nick.
 
P

Pat Hartman

Why would I want to suggest that the OP use an unbound form when that
requires substantially more code and is totally unnecessary if you
understand how form events work? I too almost always use SQL Server or some
other "real" database server as the back end. That doesn't preclude the use
of bound forms. Again, you simply need to understand a little of what goes
on under the covers. Bound forms should always be bound to queries with
selection criteria rather than bare tables whether you are using Jet or SQL
Server. Once you take that step, you don't need to resort to unbound forms.
Jet passes through your criteria so only the requested record is returned in
the form's RecordSource. How is this less efficient than all the coding and
testing you need to do to accommodate an unbound form?

In fact, the only cases where I have had to use pass-through queries is when
I needed to do bulk updates or deletes. Occasionally a report will be
complicated enough to warrant a pass-through query or stored procedure but
forms can be bound without issue as long as they select a very small
recordset.
 
B

Biz Enhancer

Pat,

My reply was not directed at you, but rather in answer to pokdbz question
prior to Rick Brandt's reply.

I use unbound forms on a regular basis due to the fact that one of my main
clients is a multinational and my applications need to work effectively over
VPN. Sending SQL traffic in a lot of small amounts works extremely well when
some of the VPN's are running at 56kbps. Pushing a single record from a bound
table can cause a failure if the amount of data is too large for the
connection to handle.

I don't suggest that you or anyone else wants to take my approach. However,
is it not reasonable to accept the fact that there are many ways to solve a
problem and a combination of approaches can be more effective than a single
person's point of view?

Considering that neither you nor I know a great deal about pokdbz's
application or his/her programming ability, we should let them choose the
solutions we present rather than getting our noses out of joint simply
because we skin cats different ways.

Nick.
 
P

Pat Hartman

Oh no! Another cat skinner :) Why don't people skin dogs instead?

I haven't had occasion to worry about running over a VPN. Does it really
make a difference whether the form is bound to a query or unbound if the
result is still one record at a time?
 
Top