Go Premium for a chance to win a PS4. Enter to Win


MS Access Forms Back Button to Delete Input

Posted on 2014-12-02
Medium Priority
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
  • 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 2000 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 Dell.com
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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: …
Suggested Courses

772 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