How to make Access stop adding records to my tables when I switch from Form view to Design view

  • Thread starter Thread starter Mister John Doe
  • Start date Start date
M

Mister John Doe

I have a form that has fields from two joined tables. The purpose of
the form is to enter data into these fields and use that data to
create a new record in the two tables. All the data entry works OK.

However, whenever I am in Form view and I switch to Design view,
Access automatically creates a new record at that time. So if I switch
back and forth ten times, then there are ten records created in the
database tables.

This is a problem because I have to keep going into the tables to
delete these extraneous records.

Does anyone have any suggestions on how to get Access to quit creating
a record just because I switch from Form view to Design view?

Any suggestions are appreciated. Thanks in advance.
 
Mister John Doe said:
I have a form that has fields from two joined tables. The purpose of
the form is to enter data into these fields and use that data to
create a new record in the two tables. All the data entry works OK.

However, whenever I am in Form view and I switch to Design view,
Access automatically creates a new record at that time. So if I switch
back and forth ten times, then there are ten records created in the
database tables.

This is a problem because I have to keep going into the tables to
delete these extraneous records.

Does anyone have any suggestions on how to get Access to quit creating
a record just because I switch from Form view to Design view?

Any suggestions are appreciated. Thanks in advance.
Click to expand...

That can only happen if the form is being "dirtied" (i.e., its data is
being modified) in one of the events that naturally fire when you open
it -- probably the Current event, but maybe Load or Activate. Do you
have procedures attached to those events that set the value of one or
more bound controls? Maybe intended to set default values? If so, you
should probably revise that code.

Access will always save a dirty record, if it *can* be saved, when you
close the form or switch to Design view. So if you are dirtying the
form, you must "undo" that act by clicking the Undo button or pressing
the Escape key once or twice, before switching to Design view or closing
the form.
 
Does anyone have any suggestions on how to get Access to quit creating
a record just because I switch from Form view to Design view?
Click to expand...

Not really, because I've never seen Access do this!

Do you have any VBA code in the Form's Close or other events which
might be saving the record explicitly?

John W. Vinson[MVP]
 
That can only happen if the form is being "dirtied" (i.e., its data is
being modified) in one of the events that naturally fire when you open
it -- probably the Current event, but maybe Load or Activate. Do you
have procedures attached to those events that set the value of one or
more bound controls? Maybe intended to set default values? If so, you
should probably revise that code.

Access will always save a dirty record, if it *can* be saved, when you
close the form or switch to Design view. So if you are dirtying the
form, you must "undo" that act by clicking the Undo button or pressing
the Escape key once or twice, before switching to Design view or closing
the form.
Click to expand...

OK thanks - I get it now.

Yes, I do have several of the fields set with default values. For
example, the field for the date is set to default to the current date
since most of the entries will be with the current date and that will
save time.

Thanks for the tip to press ESC several times before switching to
Design view. I'll have to do that because I will want to have the form
behavior set up like it is and if I change it just for development
then I'll probably forget to change something back and it won't work
properly.

I appreciate your explanation.
 
Mister John Doe said:
OK thanks - I get it now.

Yes, I do have several of the fields set with default values. For
example, the field for the date is set to default to the current date
since most of the entries will be with the current date and that will
save time.
Click to expand...

Bear in mind that a control can have a default value, established by the
DefaultValue property of the control or the field to which it's bound,
and that will *not* dirty the form. But if you use code (or a macro) to
set the value of the field to what you consider a "default value", that
*will* dirty the form. Maybe you're doing the latter when you could be
using the DefaultValue property instead.
Thanks for the tip to press ESC several times before switching to
Design view. I'll have to do that because I will want to have the form
behavior set up like it is and if I change it just for development
then I'll probably forget to change something back and it won't work
properly.

I appreciate your explanation.
Click to expand...

You're welcome.
 
Back
Top