Prevent table update in form until all records are entered

[keliie]

Hi,

Context:
I have a subform (datasheet view) that I want to prevent from updating
source table records until the user has completed filling out all of
the relevant records within the subform and clicks a button (e.g., Save
and Close). I've done some searching in this group and have found that
this requires some extensive coding (e.g., populate combox boxes, store
temporary records, append tables with temporary records, etc...).
However, these posts are all for Access 97, and I am wondering if there
is an "easier", less code intensive way to accomplish the same
objective?

Details:
User interfaces through a form (frmInvoiceMaster) and an embedded
subform (sbfInvoiceDetails). The form populates a table
(tblInvoiceMaster) which contains such fields Invoice_Date,
Vendor_Name, Invoice_Amount, etc... The subform populates another table
(tblInvoiceDetails) which contains fields like Item_Description,
Quantity_Purchased, Dollar_Amount, etc... The subform contains several
records (i.e., typically 10 or more) which are all associated with the
same invoice (tblInvoiceMaster).

Question restated:
Is there an easy way to prevent Access from continuously updating
tables until the user completes filling out all the records in an
embedded subform. I define "easy" as a minimal amount of coding /
recoding.

Thanks for your help

Kelii

 

[John Vinson]

I have a subform (datasheet view) that I want to prevent from updating
source table records until the user has completed filling out all of
the relevant records within the subform and clicks a button (e.g., Save
and Close).

No can do, as stated. A form/subform saves the parent record the
instant you set focus to the subform, and saves each subform record to
disk the instant you move to the next record. The data is NOT "stored"
in the form, even temporarily; each record is saved to disk as you go.

I've done some searching in this group and have found that
this requires some extensive coding (e.g., populate combox boxes, store
temporary records, append tables with temporary records, etc...).
However, these posts are all for Access 97, and I am wondering if there
is an "easier", less code intensive way to accomplish the same
objective?

No.

The only way to do what you describe is to have the Form bound to
*another table*, not the "production" table. It's not a horrendous job
to do this; you need some suitable event (a button click maybe) to run
an Append query to migrate the data from the scratch table into the
production table. If you want to be able to recall and edit old data,
you'll need similar code to populate the scratch table in the
mainform's Current event.

So it does take some code, but I wouldn't call it terribly extensive.

John W. Vinson[MVP]

 

[Van T. Dinh]

The simplest work-around is to hide the Subform when the user starts the
data entry to force the user to enter the details for the parent Record.
When finished, the user can click a CommandButton to save the Parent Record
(you code can ask for confirmation) at which time the code makes the Subform
visible so that the user can enter the details for the Child Records.

Otherwise, you will need to use temporary Tables with a fair bit of code to
get it right.

Note that the feature you described is called "AutoSave" in Access. You can
seach Google for AutoSave + Access and you probably get a number of threads
on this.

 

[keliie]

Thanks for the info guys, I appreciate the help.

Kelii