Display list of records entered and allow edits - MS Access

I have an Access form (Form1) for adding records to MyTable. It contains textboxes and comboboxes for data entry and a command button for adding records.

MyTable is not bound to the form.

There are restrictions on what can be entered into some of the textboxes and comboboxes. Some of the restrictions are based on what was entered in other textboxes and comboboxes.  As an example, if textbox1 contains a value of ‘Cash’, then combobox1 cannot contain a value of ‘Not Applicable’. Another  example is that the date entered into textbox2 cannot be greater than the current date, etc, etc.
All of the various checks of the textboxes and comboxes are done through code when the ‘Add Record’ command button is clicked.  If everything is ok, then a record is added to MyTable.

What I’d like to be able to do is:

After the user has entered all of the records (s)he wants to in one sitting, have him/her click a command button that will display a list of all of the records (s)he entered. This will give him/her the opportunity to to review what was entered and to make any necessary record edits or deletions before printing the list of records entered (which will serve as finalization of that batch of records).

The problem:
1.      How do I display the records that were entered in a list (datasheet?) format? For space purposes (Form1 is too crowded), I’m planning
        on using a second form (Form2) for the review/edit process.
2.      How do I allow the user to select a particular record for editing?  If (s)he just goes to the list (presuming I’m displaying a list of all of
        the records entered into the table) and edits the record there, then none of the error/validation checks that Form1 is used for will be
        performed.  So, I guess what I’m asking is how do I allow a user to select a particular record (on Form2) and once (s)he does, switch
        back to Form1, have all of the boxes on Form1 populated with the field values from the selected record.  That way, any necessary
        changes can be made and then an ‘Update Record’ button (on Form1) can be clicked to modify the record.

I wrote a lot but all I’m really asking is after a user has entered a batch of records, how do I allow him/her to review a list of the records
(s)he entered and give one last chance to make any edits/corrections before finalizing.
Who is Participating?
Nothing in your description precludes the use of bound forms.  Once you convert to using a bound form, it is easy enough to create a second read-only  form in datasheet view that selects all records entered "today" or for a "batch" or however you are defining a particular set.  Then a single line of code in the double click event of one of the controls will open the single record form for editing.  The ds view form should probably not allow edits since it sounds like your validation rules are pretty complex and you wouldn't want to have to code them for two forms (although there are ways to reuse code).  In the double-click event of a form control -

docmd.OpenForm "yourFormName",acNormal,,"SomeKeyField = " & Me.SomeKeyField

and that opens the specified form to the id specified by Me.SomeKeyField.

Access is a Rapid Application Development tool.  Using unbound forms defeats the purpose of using a RAD tool.  If you understand how form and control events work, everything you are currently doing can be handled by a bound form.  You have ABSOLUTE control over whether or not a record gets saved as long as you understand the use of the Form's BeforeUpdate event.

If you insist on sticking with your unbound form, Good Luck.  I promise that you will end up hating Access with a purple passion because you will be constantly fighting with it and you cannot win.  Access will always defeat you.  Learn to use the tool as it was intended and you will be amazed at the ease with which you can create robust applications.
dbfromnewjerseyAuthor Commented:
There was a reason why I specifically didn't use bound forms.  I can't recall what is was though but when I first started this project, it was producing some sort of behavior I didn't like, so I went a different route.  Anyway, I'll review and attempt what you route and report back.  Thank you.
dbfromnewjerseyAuthor Commented:
The absolute most important event in an updateble bound form is the Form's BeforeUpdate event.  Think of the BeforeUpdate event being the plug at the bottom of a funnel.  If the record is OK, it goes through the hole into the database.  If the validation rules fail, the plug stays shut and the table is not updated.  Most people flail around and stuff validation code in multiple events and can't ever quite control data.  Somehow bad data always slips through but NOT if you use the CORRECT event(s) for validation.  Occasionally, you might want to put validation code closer to the point of entry to prevent the user from leaving a control if he entered an invalid value.  Then you would use the control's BeforeUpdate event.  But, since control events only fire if the control ever gets the focus, you cannot use ONLY the control's BeforeUpdate event since you cannot validate for empty or relationships to other fields which may or may not yet be entered.  So, most validation code should go into the form's BeforeUpdate event with very little going into the controls' BeforeUpdate event.

Remember - ALL of your problem list is solved by using bound forms and no additional code except the one line to open the edit form from the list form.
dbfromnewjerseyAuthor Commented:
OK. Thank you.
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.

All Courses

From novice to tech pro — start learning today.