Solved

MS Access Forms Back Button to Delete Input

Posted on 2014-12-02
5
263 Views
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
0
Comment
Question by:mdstalla
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:Nick67
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.

Nick67
0
 

Author Comment

by:mdstalla
ID: 40477559
Nick:

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.
0
 
LVL 26

Accepted Solution

by:
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?
0
 

Author Closing Comment

by:mdstalla
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.
0
 
LVL 26

Expert Comment

by:Nick67
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

18 Experts available now in Live!

Get 1:1 Help Now