Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to create a form to enter information into multiple tables

Posted on 2013-06-20
2
Medium Priority
?
389 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 1000 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 1000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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