Can you make access work more like a spreadsheet

Posted on 2012-09-13
Last Modified: 2012-10-02
We have a form that is a series of detail records that enters in
Line Number - Product Code - Product Description - Weight - Lot Number

We have been back and forth with users who try to enter in the columns all at once. SO users have entered in all Product Codes in a tall stack and the Weights all at once in a columnar format. We have had the users have some success with entering it in that way but we have also had some problems with errors and codes that come back forcing the user to delete the entire record and start over. We have had much less of a problem with the users filling the records out left to right ....row by row.

Is there a way to make that part of a form allow us to build that part of a form more like filling a series of columns in a spreadsheet from top to bottom or is Access forms really not designed to do that.......?
Question by:ruavol2
    LVL 61

    Assisted Solution

    Access forms and their corresponding Form level events are designed to work record by record, rather than column by column...

    So if you are focused on a single column, enter one value and move to the same column in the next row - you have edited a record and all associated events and record validation will automatically occur.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    There can sometimes be a bit of training needed for users when switching from Access to Excel (or vice-versa). As mbizup said, Access is designed to work in a row-by-row fashion, not a column-by-column.

    If your users are more comfortable in Excel, and if the app makes more sense in the column-by-column scenario then you can always use Excel as the interface, and store the data in Access - but that is rarely needed, since if Excel is a better interface choice, then it's almost always the best way to store that data (in most cases it's "flattened" data, which doesn't store well in Access).
    LVL 33

    Assisted Solution

    Access forms can be shown in datasheet view where the user can enter the data row by row, left to right, column by column.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    Access forms can be shown in datasheet view where the user can enter the data row by row, left to right, column by column.
    They can, but they still don't act like a spreadsheet. If you enter data down a column, you're going to trigger many events each time you dirty that new record (which was, I believe, the trouble described in the original question).
    LVL 28

    Accepted Solution

    A temp table with none of the events could be used, with a 'Save' button which applies all checks before updating the final table. Just a thought.

    Author Closing Comment

    That was helpful thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    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…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    758 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

    11 Experts available now in Live!

    Get 1:1 Help Now