Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Display list of records entered and allow edits - MS Access

Posted on 2016-10-18
Medium Priority
Last Modified: 2016-10-18
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.
Question by:dbfromnewjersey
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 39

Accepted Solution

PatHartman earned 2000 total points
ID: 41848636
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.

Author Comment

ID: 41848653
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.

Author Comment

ID: 41848654
LVL 39

Expert Comment

ID: 41848669
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.

Author Comment

ID: 41849082
OK. Thank you.

Featured Post

Industry Leaders: 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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

636 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