Adding a record only after clicking OK button (1 Viewer)

gringope24

Member
Local time
Tomorrow, 00:57
Joined
Apr 1, 2020
Messages
51
Hello folks,
the easiest way to add new record seems to be opening a bound a form with a below code and just enter data.
Code:
DoCmd.OpenForm FormName:="frmAddNewCustomer", DataMode:=acFormAdd

But this method creates a new record when just even one field will be filled.

I want to create a new record after user will fill the field/fields and with full awareness click OK button.

What is the easy way to do it? I thought about creating an unbound form and sending values from the field to the table using SQL or by AddNew Recordset, is it good approach?

Secondly (for general information), what is faster and more efficient method to add new record:
1) using SQL statement and run with command CurrentDb.Execute strSql ?
2) use Recordset ?
3) define a query in Access and fire it with QueryDef.Execute ?

Thanks for your support in advance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2002
Messages
42,981
You are in complete control over whether or not a record gets saved. If you have validation rules that define what a good record is, then put them in the FORM's BeforeUpdate event. Think of this as the flapper at the bottom of a funnel. If the flapper is open, the record gets saved. If it is closed, the save is prevented. Some code might look like the following. The code applies three rules that must be true for a record to be valid. If all three are not true, then the record is not saved and control is returned to the user. The record is still dirty so if the user does nothing but try to exit again, he'll get the same error he previously got. He has to consciously clear his edits by using the esc key twice or by fixing the error.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.SomeField & "" = "" Then        ' field is null or = ""
        Cancel = True
        Msgbox "Somefield is required.",vbOKOnly
        Me.SomeField.SteFocus
        Exit Sub
    End If
    If IsDate(Me.SalesDT) Then
        If Me.SalesDT > Date() Then
            Cancel = True
            Msgbox "Sales Date may not be in the future.",vbOKOnly
            Me.SalesDT.SetFocus
            Exit Sub
        End If
    Else
        Msgbox "Sales Date is required.",vbOKOnly
        Cancel = True
        Me.SalesDT.SetFocus
        Exit Sub
    End If'
End Sub

Access is a RAD (Rapid Application Development tool. Let Access be Access is my advice. If you don't like the way Access works natively with bound forms, use a different platform. Period.
As soon as the user types something in ANY bound control, the form becomes "dirty". Access will then be on a mission to save this record no matter what. You can stop it as I described but otherwise, the path of least resistance is to just let Access try to save when it thinks it should and allow it to happen if the record is valid and stop it if the record is invalid.

So the answer to the question is no, no, no. Let the bound form do its thing.
 

Isaac

Lifelong Learner
Local time
Today, 16:57
Joined
Mar 14, 2017
Messages
8,738
@gringope24

This may or may not be of interest to you:

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2002
Messages
42,981
Isaac,
You've developed this style because of problems with using a SharePoint BE but perhaps, for a more normal Jet/ACE/SQL Server BE, it might be better to simply not fight with Access.
 

Isaac

Lifelong Learner
Local time
Today, 16:57
Joined
Mar 14, 2017
Messages
8,738
Pat I know we've talked about this before and I thoroughly respect your approach because I feel like both of them have advantages and disadvantages. But I'm quite happy with my method of bound forms which is not to allow an automatic save except through explicit use of a save button, with occasional exceptions. It's quite easy to create these exclusions and it has worked well for me to be able to tell my users one simple instruction, that is, nothing will be saved unless you press save.

Again though, I respect your approach because you pointed out to me enough things to remind me that the different approaches have pros and cons.
 

Users who are viewing this thread

Top Bottom