Add record lines to spreadsheet form

[brt]

Hi,


I want to create an access spreadsheet form in which the user can add data, which will be – after being processed – added to a database table. The spreadsheet form is for this reason not directly linked to a database table.


In the spreadsheet that I actually have, I manage to copy paste data from an Excel spreadsheet. However, only the first row is pasted. Access doesn’t add new record lines for the other rows of my spreadsheet.


How can I make Access add record lines in function of the data on that is pasted? Or if this is not possible, how can I add sufficient additional lines myself?


Thanks in advance!

 

[boblarson]

A couple of things.

Access is not a spreadsheet program. It is not intended to be a spreadsheet program. When you use Access (relational database) you need to stop thinking with a spreadsheet mentality and learn about properly normalized table structures.

You say you have a spreadsheet form. That is incorrect. What you have is one of three form views (still just a form) - Datasheet, Continuous Form, and Single Form. The datasheet view looks like a spreadsheet so it is easy to try and use it like one. That is a mistake.

A common mistake one makes when starting out with Access is thinking of the user interface before the table structure is designed and completed. Presentation should be secondary to getting the tables correct.

So I think you need to stop, take a step back, and reevaluate. We can help you design your tables (go post in the Theory and Practice of Database Design category).

Then, once you are done with that, you would then move on to interface design. You may end up importing data from Excel as the option instead of trying to copy and paste. But for the moment it doesn't matter. We prefer to suggest best practices, if possible, over just answering a question when it is clear just answering willl lead you down a path of heartache and regret.

So what are you actually using the database for? What are your intentions? Why not continue using spreadsheets if that is what you are comfortable with?

 

[brt]

Thanks for your reply Bob.


Actually, I have a properly working Access application. It’s goal is to manage the archives of a water company and the database contains references to all files in the archives, as well as the users borrowing these files and the files that are actually borrowed and by whom.


Every now and then, new files are added to the database. For the moment, I have a window in which files can be added one by one by inserting their references. What I want to do is have some kind of datasheet dialog in which multiple files can be added at once, in order to speed up this process. References are often copy pated from Excel files so it would be good if it was possible to copy multiple cells to this datasheet form.


So my question is, how can I make such a datasheet form in which the user can add and modify data without directly changing a table. Only when the user presses a save button, the application will store the new file references into the database.


Thanks in advance