Stopping automatic save in a table

Posted on 2011-10-04
Medium Priority
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
  • 2

Accepted Solution

ref-IT earned 2000 total points
ID: 36913662
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

ID: 36913905
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.

Expert Comment

ID: 36916006
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

809 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