Preventing Auto Add

  • Thread starter Thread starter dave h
  • Start date Start date
D

dave h

Hi,

On one hand, I'd like to create a standard form that is tied to a table and
use it for data entry. On the other hand, I'd like to control the actual
adding of the record to the DB. Is there a way to prevent the automatic
addition of the record to the table when the user exits the form or moves to
another? On the property sheet, if I check "yes" for data entry and "no"
for additions then none of the DB field controls show on the form. I had
hoped to have an "ADD" button on the screen and then do my own INSERT in VBA
code.

Thanks, Dave H
 
Hi,

On one hand, I'd like to create a standard form that is tied to a table and
use it for data entry. On the other hand, I'd like to control the actual
adding of the record to the DB. Is there a way to prevent the automatic
addition of the record to the table when the user exits the form or moves to
another? On the property sheet, if I check "yes" for data entry and "no"
for additions then none of the DB field controls show on the form. I had
hoped to have an "ADD" button on the screen and then do my own INSERT in VBA
code.

Thanks, Dave H
Click to expand...

The "Data Entry" property isn't what you want in this case: it
restricts the form to allow only new records to be entered, and
prevents viewing of previously entered ones. Set it to No, and the
Allow Additions to No as well; but set Allow Edits to Yes. This will
let you add new records in code. You will need to Requery the form to
see the new additions.

John W. Vinson[MVP]
 
Hi Dave

You could always create an unbound form (one where the form has no
recordsource and the controls have no controlsource) to capture the data,
then write your own code behind your ADD button.

But why? One of the nice things about Access is that it looks after all
this stuff for you! Wouldn't it be easier to put code in the form's
BeforeUpdate event to check whether or not the user really wants to save the
record? Your ADD button couold set a module-level flag to suppress the
confirmation message in BeforeUpdate.
 
Thanks for the response - much appreciated.

Your BeforeUpdate event advice is right-on. I've set flags to handle the
logic of extra messages and all seems to work well. One side issue is that
after I use a SAVE button to save the data to the DB, the form fields retain
the data. The auto number field that is the primary key of the table is a
particular issue. I can close and open the form to clear the fields, but
this does not seem very elegant. I tried to use "refresh" with no luck.
Any suggestions?

Thanks again, Dave H


Graham Mandeno said:
Hi Dave

You could always create an unbound form (one where the form has no
recordsource and the controls have no controlsource) to capture the data,
then write your own code behind your ADD button.

But why? One of the nice things about Access is that it looks after all
this stuff for you! Wouldn't it be easier to put code in the form's
BeforeUpdate event to check whether or not the user really wants to save the
record? Your ADD button couold set a module-level flag to suppress the
confirmation message in BeforeUpdate.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

dave h said:
Hi,

On one hand, I'd like to create a standard form that is tied to a table
and
use it for data entry. On the other hand, I'd like to control the actual
adding of the record to the DB. Is there a way to prevent the automatic
addition of the record to the table when the user exits the form or moves
to
another? On the property sheet, if I check "yes" for data entry and "no"
for additions then none of the DB field controls show on the form. I had
hoped to have an "ADD" button on the screen and then do my own INSERT in
VBA
code.

Thanks, Dave H
Click to expand...
Click to expand...
 
One side issue is that
after I use a SAVE button to save the data to the DB, the form fields retain
the data. The auto number field that is the primary key of the table is a
particular issue. I can close and open the form to clear the fields, but
this does not seem very elegant. I tried to use "refresh" with no luck.
Any suggestions?
Click to expand...

Me.Undo will erase all changes;

DoCmd.RunCommand acGoToRecord acNewRecord

will put the user on the blank new record.

John W. Vinson[MVP]
 
Hi Dave

Just to detail further what John said, you can't use Me.Undo after you have
saved a record, but you can use it in your Form_BeforeUpdate procedure to
*avoid* saving the record should the user choose top discard it.

Using:
DoCmd.GoToRecord Record := acNewRec
will go to a new, blank record with a fresh autonumber key, and will have
the side effect of attempting to save the current record if it is dirty.

Therefore, in the Click event of your ADD button, all you need is:
m_fAddClicked = True
DoCmd.GoToRecord Record := acNewRec
and you will kill two birds with one stone.

(Note than m_fAddClicked is the module-level boolean flag to tell
BeforeUpdate that it's OK to proceed)
 
Back
Top