Hold updating underlying table while filling Access form & update table at the click of a button

[bubai]

My Access database has 3 tables Materials, Orders and Shipments. Materials table is a list of all importable


material and this table is used as a lookup table for the Orders table which puts the name of material
according to the required order. Orders table is related to Shipments table by one to many relation.
I want to make a form by which I could scroll through all the records of Shipments and Orders table as
well as to add new records. But my requirement at this point is that, I want the form not to update the
table while I input data in each field, but to HOLD THE UPDATING UNTIL I HAVE FINISHED ALL OF THE
FIELDS AND UPDATE AT THE CLICK OF A BUTTON ON THE FORM. Side by side Shipments table has autonumbered
Primary Key which has to generate automatically in the form.
I am still a novice in the VBA arena, and I don't have any idea of ADO and DAO. Please help me out.

[June7]

Data is committed to table when:

1. close table or form

2. move to another record

3. run code

Code can prevent the record update. Otherwise, use unbound controls and code to save data. Example of one method for date/time, text, number type fields:

CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) VALUES(#" & Now() & "#, '" & Me.textboxname & "', " & Me!ID & ")"

[bubai]

Dear June7,
I think in the last line you have provided SQL syntax to transfer data from an Unbound form to table. But i'm not sure about the "&" in the 3 type of fields. However it might be better for me to use a BOUND form as then i could chek the previous data in the table as well. IF YOU COULD PROVIDE ME VBA CODE TO PREVENT RECORD UPDATE I could have make use of that.

[burrina]

Do you want to not save the data until ALL fields are filled in or only some fields?
Example:

If IsNull([CustomerID]) Then 'Require a Customer.
MsgBox "A Customer is Required" 'Optional.
[CustomerID].SetFocus
End If

HTH

[bubai]

Hi Burrina,
I'd like to hold the savings untill ALL fields in the form/sub-form are filled.

[burrina]

Here is an Example:

Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Ctrl.ControlType = acTextBox Or Ctrl.ControlType = acComboBox Then
If IsNull(Ctrl) = True Then
MsgBox "Hey...You didn't fill in all the data " & _
"fields for this record. Please do so now.", _
vbExclamation, "** Required Data Missing **"
Cancel = True
Exit For
End If
End If
Next Ctrl

[June7]

Could set fields as required in table settings. Then Access can nag the user if fields not completed, they will be returned to the record to complete. Won't save until the required fields are completed.

[bubai]

Burrina,
I guess "Cancel=True" is the line that prevents the form from saving, isn't it? AND WILL THIS AUTOMATICALLY SAVE THE FORM ONCE ALL OF THE FIELDS ARE FILLED IN?
However there is one textbox in my form which may remain empty depending on a checkbox field. In that case what should I do?

[June7]

Options:

1. Use the control Tag property. Have some indicator such as "Required" in the property then the code can be:

If IsNull(Ctrl) = True And Ctrl.Tag = "Required" Then

2. just check for the name of that control:

If IsNull(Ctrl) = True And Ctrl.Name <> "the name" Then

[bubai]

In what event of the form should i put the procedure? I tried it in the Form_current event, but in the line "Cancel=True" it highlights the Cancel in blue and says "Varialbe not defined".

[June7]

In the form BeforeUpdate procedure.

[bubai]

This time no error, but it is not doing what it is supposed to do. If i partially complete the form and close it, it doesn't refrain me and happily adds the record to the underlying table. What i need is a procedure that i can add to a Button click procedure and that then will add records to the table, not before that.

[June7]

How are you closing the form - custom button or X close? I always disable the X close and use custom button but that does complicate the process because the button event fires before the BeforeUpdate event.

Consider this example which does not use BeforeUpdate but returns user to the form to complete entry.

Private Sub Close_Click()
If Not IsNull(Me.Rate) Then
    'save record and close form
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
End If
End Sub

If you want an option to completely abort the record entry/edit, that will mean some more code. At this point, user can press ESC to undo record edits.

Is a form/subform arrangement involved?