Relationships, Forms and Subforms 1

[JustSara]

I am very, very new at databases. I'm trying to create a database in Access and every time I think I've got it figured out... I don't.

If you want to see the database it is posted in a zip file here:

http://www.sweeperhead.com/database.htm

What I am trying to do is have a form where I can type in Customer Information (I have a customer table) Invoice Information like the date of purchase(I have an invoice table) and Product Information (what they bought - product table). I'm trying to put a subform inside a form so that I can list multiple products purchased to one customer but I keep getting errors. Right now I keep getting the error that the field can't be updated and also the error message that a link isn't found in a recordset.

I'm ready to just go sit in a dark corner and talk to the wall. Any help would be appreciated and if you want to speak directly to me my ICQ number is 23165750

 

[randysmid]

Hi,
First, you will want to create the master form where you can pick the customer number. So, that means you should have a customer table that has a customer id, the customer name, address, etc. You will need to build another table that contains the invoice master information, such as customer id, invoice #, invoice date. Then, you will need another table that contains the detail information. This table will contain multiple product records for that particular invoice. This all gives you the capability of having multiple invoices for a customer in one day. The detail table should have customer id, Invoice ID, ProductId, quantity, unit price (for this invoice only).

This should give you a good idea on how to start. When you are ready, please reply back to this post and I will show you how to link the master form to a subform (which contains your detail information).
HTH,
Randy Smith
California Teachers Association

 

[JustSara]

So I won't be confused I created a whole new database. In my database I now have 3 tables called Customer, Product, and Invoice. That's all I have in there. There aren't any queries or forms yet.

Sara

 

[randysmid]

Hi,
You almost have it. But, you are missing the proper design image in your mind. You need two invoice tables!!! The first one will contain the master invoice information, such as InvoiceID, CustomerID, InvoiceDate. This table should be called Invoice.

1) Next, you need another table (probably called InvoiceDetail). It will contain these fields:
InvoiceID
ProductID
ProductQTY
ProductUnitPrice
The InvoiceDetail table will contain one or more rows depending on the size of the invoice. There will be a one-to-many relationship between Invoice (one invoice record) to InvoiceDetail (which may contain hundreds of detail records for that particular invoice). You will see that the InvoiceDetail doesn't need to have the CustomerID for each detail line, since it is kept in the Invoice master table. (This is one of the concepts of relational database design ----> NEVER have duplicate data!! - presumably, there is ONLY one customer per invoice).

2) Once you have created both of these tables as I have indicated, you need to create a query that joins the Customer table to the Invoice table (so that you can display CustomerName, etc.). You need to make sure there is a line connecting the CustomerID field from the Customer table to the CustomerID in the Invoice table (all from within the query object). Make sense?

3) You need another query that joins the Product Table to the InvoiceDetail (so you can display ProductDescription, ProductPrice, etc.). You need a line here that joins the two tables together, as I indicated with paragraph 2.

4) Now you can now create a form for just the master invoice (let's call it frmInvoiceMaintenance). Leave plenty of room near the bottom for the subform which will contain the InvoiceDetail records. Now you can create the subform based on the query you created in paragraph 3. Let's call this frmSubInvoice.

5) Now you can go back to frmInvoiceMaintenance, and click on the Subform icon on the toolbar. Here is where you will insert frmSubInvoice. The program will ask how these two forms are being linked. This is linked via InvoiceID.

Please reply if you need any more help with this, ok? By the way, since you are a beginner, be sure to check out my FAQ's. I wrote them for the entry level Access developer. Just click on my user id (randysmid), then scroll down to see all the FAQ's I wrote). HTH,
Randy Smith
California Teachers Association

 

[randysmid]

Sara,
If you want, please send me your email address, and I will send you a sample database that I created. I will add a form that contains Master/Detail processing, so you can see how it all works. Unfortunately, Tek-Tips doesn't provide anyplace for us to post sample databases. :-(
Here is my email address:
rsmith@cta.org HTH,
Randy Smith
California Teachers Association