Enter data from a form onto two tables.

[johnseito]

Hi everyone,

I created a form that allows users to enter data into the fields and populates the table.
But can I have it do that and populate instead of one table, two tables?

Thank you

 

[Mihail]

Yes, you can. Not only one table. One thousands and more. But no reason to do this.

 

[Rabbie]

Yes, you can. Not only one table. One thousands and more. But no reason to do this.

There is often a good reason for this if your DB is properly normalized. I do this when entering an order which will make an entry in my order table and several entries in the order detail table.

Just use a query as the record source for your form/subform

 

[johnseito]

Ok how do you do this?

I need to do this, so I don't creat two forms
to input two tables.

 

[johnseito]

any examples? Thx

 

[Rabbie]

Try looking at the Northwind sample db. It should be part of the Access package. Look at the orders screen

 

[johnseito]

Ok, thanks. you meant Access 2010, the Northwind Sales web database ?

And which form and table should I look at? Thanks

 

[johnseito]

Hi,

I don't have Microsoft office 2003, I used to have it installed on my computer and
also the file to download it, now I don't.

I only have 2007 and 2010. Would 2007 work?

 

[johnseito]

I have MS office 2003 software installed on my old computer, do you know how and if I could extract the software and installed it on my new computer.

Do know if this is possible. Thanks

 

[johnseito]

I couldn't find the CD to install it on my new lab top, but I have installed it on my old one. Without the software I wonder if I could extract it from the old lap top to the new one.

Thanks!!!

 

[Pat Hartman]

There is nothing difficult about using a query as a RecordSource for a form or report. Just create a query that joins the tables and selects the columns you need from each. Save the query. Open the form an on the Data tab, replace the table name with the query name.

I will add that it is not typical that one form would be used to update multiple tables. Generally, one table is the "main" data source for the form and the other tables or queries in the RecordSource query are "lookups". So, on an Order entry form, you would use a query that joins the Order table to the customer table. You would use the form to populate the order fields but the Customer fields would be used to lookup customer name and address. There would typically be a subform that you would use to enter items purchased and that too would be based on a query that joins the Order Item table to the Product table so you can "lookup" product description, etc.

 

[johnseito]

Ok Pat, thanks for your advice.

I wish I am an Access expert like yourself, but I am not, I am just getting to be familiar with it. What I am trying to do is to build a database from scratch and create forms that
is both convenient for the user as well as efficient without any redundancy and I am still investigating and thinking of how best to do it.

Since I don't have in-debt knowledge of this I guess I should take it one step at a time.

 

[Mihail]

I wish to turn your attention to this:
It is not possible to design a database without having a FULL FULL FULL image and understanding of the business:

1) At what questions the database must ANSWER ?
2) In order to answer to this questions, what is the MINIMAL set of the input data?
3) How manually answer to this questions starting from the input data ?

The computers NEVER do something that you can't do manually.
The single difference is the speed.

I'm sure that the experts have other words to describe this, but this approach have worked for me every time until now.

I write this post because I have seen a lot of guys, smart guys with no doubt, that first start to design a database, then, starting from the already designed database, trying to accomplish the 1), 2), 3) blue points.

 

[johnseito]

Ok, I am not a database design expert either, although I wish I am.

I have a design that was already set up and so I am just trying to expand upon it
and built forms to filled the tables of the database and then creating queries etc.

I do this for the purpose of get use to having the mind set of how a database can be started from the ground up and how it can be improve upon it and also learn access at the same time.