MS Access Forms Back Button to Delete Input

Posted on 2014-12-02
Last Modified: 2014-12-03
I have a series of forms with a ‘Next’ and ‘Back’ button on them.  Regarding the ‘Back Button…’ I was wondering if anyone could give me a code that, on click:

1.       Opens the last form that was displayed and closes the current form
2.      Deletes any input that was entered into the current form

For example:

Form1:  Q: What is your name?      Input Answer:  Matt     [Back] [Next]
Form2:  Q:  How old are you?     Input Answer:  27     [Back] [Next]
Form3:  Q:  Where do you live?     Input Answer: California     [Back] [Next]

If I’m on Form3 and click the ‘Back’ button—the answer California should get deleted, Form2 should open and Form3 should close.

Please note:  

1.      The flow of forms is not always in numeric order.  It is important that the code opens The Last Form Displayed—not the previous form in terms of its numeric value

2.      Forms and Data Inputted need to be bound to their appropriate/respective record-set, defined with the unique field:  InterviewID

For example, the code for my ‘Next’ buttons is as follows:

Private Sub Command58_Click()
DoCmd.OpenForm "form5", , , "[InterviewID] = " & Me.InterviewID
DoCmd.Close acForm, "form6"
Exit Sub
ErrTrap: MsgBox Err.Description

End Sub
Question by:mdstalla
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 26

Expert Comment

ID: 40476741
I could give you code for that, but I won't, because that's really a design train wreck.
What you SHOULD be looking at is a tab control to replace all these forms.
THAT you CAN keep track of the order of the tabs receiving the focus.

For stuff like this, it's really hideous design to bind all the controls and then worry about rubbing out unwanted data.
A much better design involves unbound controls.
The users enter text to their hearts' content.
When they are ready, they click a button to commit their choices.
YOU then validate that the values work and are acceptable and then YOU commit the data to tables.


Author Comment

ID: 40477559

My database is designed to be an Interview (Q&A) platform in which forms are used to ask questions and allow the User to input answers.  The ‘flow of forms’ is dynamic in that the answer to one question dictates the next question to be asked. For example:

Form1:  Do you have a favorite color?  Yes/No
Form2:  What is your favorite color?
Form3:  Why don’t you have a favorite color?

If you’re on Form1 and answer ‘Yes,’ then the next form to be displayed is Form2.  If you answer ‘No,’ then the next form to be displayed is Form3.

My database is also linked to a Mail-merge Word Document that is pre-programed to delete certain sentences based on certain conditions met in my Access Tables.  For example:  

Sentence1:  Matt’s favorite color is [BLUE]
Sentence2:  Matt does not have a favorite color because [Matt is color-blind]

Again, if you answer ‘Yes’ on Form1, Sentence2 will be deleted on the Word Document (Mail Merge will place the answer to Form2 in Sentence1).  Or, if you answer ‘No’ on Form1, Sentence1 will be deleted on the Word Document (Mail Merge will place the answer to Form3 in Sentence2)

The problem is, if a User ‘goes crazy’ with the Back Button on the forms—he can begin providing answers to questions that no longer need to be asked.  For example:

Form1:  Do you have a favorite color?  Yes/No
Form2:  What is your favorite color?
Form3:  Why don’t you have a favorite color?
Form4: Has this always been your favorite color? Yes/No
Form5:  What is your favorite shape?

Imagine if I go from Form1 (Yes) – Form2 (Blue) – Form4 (Yes) – Form5…

Okay, that's fine... But now use the back button to go back to Form1 (I want to change my answers).  

This time I go from Form1 (No) – Form3 (I’m color-blind) – Form5…

The problem is… my answer to Form4 is still recorded as Yes, even though this Question is no longer relevant.  Thus, my Word report is including comments related to Form4 that should not be included.
LVL 26

Accepted Solution

Nick67 earned 500 total points
ID: 40477589
The ‘flow of forms’ is dynamic in that the answer to one question dictates the next question to be asked.
It is the use of Forms, open & closing, and being bound, that I see as the train wreck, not the idea itself.

When you close a form, ALL the data from it gets flushed.
So you are looking -- or needing -- to store all the order data in global variables of some sort.
Which are notorious for getting flushed if an error -- even an error handled by an error handler -- occurs.
And because you are using bound controls, you will be creating records, and then trying to get rid of them -- which usually leads to trouble.

Stuff like this is usually much better handled by a pair of unbound textboxes and a command button.
A question appears in the one textbox
An answer is entered into the second, and a button is clicked.
The answer is validated and stored in either a variable, a hidden control, or sometimes a table of temporary data
The next question appears.

If a back button is supplied, the answer from the previous question is hauled out of storage, and the question reloaded.
It is easy enough to code a 'WentBack' condition that, if true, would cause the temporary data to be discarded and back up again.

And with a Tab control you can break the questionnaire up into topics, using the same idea.
And in the end, there's a final button that commits ALL the answers to tables from their temporary locations.
But tracking forms & deleting data & getting it all right, everytime -- that would be a nightmare.

"My database is also linked to a Mail-merge Word Document"
Do you have a need for an editable document that you wouldn't do the final output as an Access report?

Author Closing Comment

ID: 40477668
Yup, I'm starting to see your point  As you can tell this is my first major Access Project and I'm very much learning as I go along.

The way I have it set up right now (single forms linked by code and bound to a table) certainly works… but I have encountered my fair-share of technical glitches, missing data, error messages and crashes.  I think asking access to delete data already save in a table at the click of a button would only compound these headaches.  

To answer your question… the output-reports that are generated are broken down into about 70 questions… and each question must be manually copied/pasted into my Company's Database.  You can't exactly copy/paste entire sections of an Access Report into a database like you can with a Word Document… and trying to convert an Access Report into Word just has too many formatting issues.  

The way I'm doing it right now is as pretty as I can make it (and I have to say, it's not too shabby).  Mail merge 'fills in the blanks' where data needs to be dropped, and the Macro knows which sentences to keep and which to delete.  In addition, it shifts all remaining sentences upward to eliminate any major gaps thus displaying a nice orderly,  formatted Word document with only the necessary information intact.
LVL 26

Expert Comment

ID: 40478780
Go to
Misconfigure a server for purchase
Have a look at how the notifications that you have an invalid configuration selected are handled.
You could do something similar, throwing a notice into a label control that:
"because you answered question 3 as yes, your answers in question 7-10 are invalid"
The hard part is the logic of the questions, and building a function to validate input.

70 questions.

That's a lot of hidden controls, or a very big array.
I am thinking I would create a table, tempResponses, that would hold QuestionID's and responses.
(QuestionID1, Response1: QuestionID2, Response2 ...QuestionID70, Response70)
Load it up at the beginning of an interview.

The navigating button would change the ControlSource of the textboxes to the required row in tempResponses and requery them.  You'd have the validating function behind a command button that would feed in the QuestionID, and check for validity.  At the end, you'd have a complete button, that would check all answers for validity, and if it was good, write everything from tempResponses to the permanent location, and clear tempResponses for the next interview.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

756 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