Can you make access work more like a spreadsheet

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.......?
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
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.
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

NorieVBA ExpertCommented:
Access forms can be shown in datasheet view where the user can enter the data row by row, left to right, column by column.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
That was helpful 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.