Solved

How to create a form to enter information into multiple tables

Posted on 2013-06-20
2
343 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now