• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

How to create a form to enter information into multiple tables

When I try to create a form based on most of the tables in this databse, I get tons of error messages.  Even if i manage to get the form into datasheet view, I can not enter any information through it.

The idea is to create a database to replace an Excel workbook.  The form in the database should allow me to enter information into most of the tables created preferably in a format that looks like the Excel spreadsheet to minimize user training.

Any and all help is greatly appreciated!
2 Solutions
You cannot do this  in the way you have described.

In principle, an Access form is based on a single table or single query.  In general, you cannot create a query that allows you to enter data into more than one table.  
(A query can be based on multiple tables, but in most cases this comprises a main table for displaying/entering data and other 'lookup' tables which allow values to be looked up using the ID values held in fields the main table.)

To enter data into multiple tables using the same form, you would either have to use unbound controls and write code to save the data, or else you would have a main form for entering data into the main table and then use subforms - probably one for each table- to enter data into the other tables.  
There is no possibility that this will look like an Excel spreadsheet.  If that's what you require then you should probably think about using Excel.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If that's what you require then you should probably think about using Excel.
That's probably the best piece of advice you'll get, but if you insist on doing this in Access the only way to get close to this is by using temporary tables:

1) Create a table that includes all the columns you need.
2) Build a form based on that table
3) Use VBA to move the user-entered data into the correct tables.

This is typically a pretty big task, and one fraught with "gotchas" if you're not well versed in managing data on your own.
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now