Stopping automatic save in a table

Posted on 2011-10-04
Last Modified: 2012-05-12
I have a form called “Scheduling-Add”. When the user wants to add a new “Study” that is not in the system they click the “Studies” button on the form which opens another form called “Studies-Add” . When the user is filling the details on this form for the new studies, the record is automatically populated with a “studyAutoNbr” which is also the primary key in the upper right field of the form next to the “Close” button. I don’t want this new “studyAutoNbr” to be automatically created by the system until the user starts to input data for the new studies. The reason why I want this measure in place is because if a user unintentionally opens that form and decides it was a mistake, a “studyAutoNbr” is already created in the table and we could end up with a whole lot of “studyAutoNbr” with blank fields. I am open to any suggestions that could fix this problem. How can I accomplish this goal. Attached is a copy of my database with the form. Thanks in advance.
Question by:Chrisjack001
    LVL 2

    Accepted Solution

    Hello Chrisjack001,

    I downloaded your database and had a look at your problem. Just as I had suspected, the "studyAutoNbr" field is of an autonumber datatype. This will automatically increment everytime a new record is to be added before the user can begin making any data entries.

    One way of stopping this is to make sure that the user is absolutely sure that he/she wants to add a new Study data in the Studies-Add field. Therefore, in the Scheduling-Add form, make the Click event of the "Studies" button look like the following code to obtain user verification that he/she is sure that new study data needs to be added:

    Private Sub cmdStudyId_Click()
    On Error GoTo errHandler

    Dim decision As Integer

    decision = MsgBox("This action will add new Studies data." _
                     & vbCrLf & vbCrLf _
                     & "Do you want to continue?", vbQuestion + vbYesNo)

    If decision = vbNo Then
        GoTo exitSub
    End If

    DoCmd.OpenForm "Studies-Add", , , , acFormAdd, acDialog
    'code waits until Studies-Add form is closed

        Exit Sub
        MsgBox Err.Number & vbrclf & vbCrLf & Err.Description
        Resume exitSub
    End Sub

    I have amended the above code to include the funtionality that the above click event currently has.

    Hope this helps.

    LVL 26

    Expert Comment

    You MUST not use studyAutoNbr for human purposes.
    When an app starts there are these nice sequential numbers you are tempted to use for many things.


    They don't stay sequential for eternity, and you'll fight a losing battle trying to make them so.
    Every record that gets deleted--one autonumber gone
    Every record that someone starts and then hits escape on the keyboard - another gone.

    <The reason why I want this measure in place is because if a user unintentionally opens that form and decides it was a mistake, a “studyAutoNbr” is already created in the table and we could end up with a whole lot of “studyAutoNbr” with blank fields.>

    You have to decide if you want a bound form--in which case that studyAutoNbr gets created when fields start getting filled in, and while you can try with a form BeforeUpdate event to catch the users input and validate it


    Use an unbound form to get the record started.  The main form would be set to AllowAdditions = False so the users CAN'T start a new rcord there.  You then provide them with a button to open another small form.  This form has some unbound controls the user fills in, and a button to fire code that creates the new record and moves the main form to that new record.

    I much prefer the second approach.  It is more work--but bulletproof.
    LVL 2

    Expert Comment

    I agree with nick67.

    Remember in my comment I said "One way of stopping this is .... "  and I continued to explain the first way.

    Well the other way of solving the probelm that I wanted to explain was what nick67 told you. The only reason I didn't say it was because like nick67 correctly pointed out, it is a longer way of getting things done, and because you already had the bound form in place - I thought it would be a little crude for me suggest all those changes. However, the second way is a more sturdy way of doing things.

    Therefore, either get user confirmation before addition of records OR do it the more sturdy way :)

    Hope that helps.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Read about achieving the basic levels of HRIS security in the workplace.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now