Solved

Display list of records entered and allow edits - MS Access

Posted on 2016-10-18
5
24 Views
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.
0
Comment
Question by:dbfromnewjersey
  • 3
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 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.
0
 

Author Comment

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

Author Comment

by:dbfromnewjersey
ID: 41848654
correction...wrote
0
 
LVL 34

Expert Comment

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

Author Comment

by:dbfromnewjersey
ID: 41849082
OK. Thank you.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

16 Experts available now in Live!

Get 1:1 Help Now