Solved

How to create a form to enter information into multiple tables

Posted on 2013-06-20
2
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 85

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

627 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