Solved

How to create a form to enter information into multiple tables

Posted on 2013-06-20
2
358 Views
Last Modified: 2013-08-12
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!
Hub2.accdb
HUB.xlsx
0
Comment
Question by:fabi2004
2 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 39265276
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.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39265429
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.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

840 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