Stopping automatic save in a table

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mudasir NooraniSenior Analyst ProgrammerCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Mudasir NooraniSenior Analyst ProgrammerCommented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.