main form

Question

hi 

i am making a database system for auto generation of invoice in that invoice i have customer and products data i want the customer and products data auto populated on the basis of a specific field like by customer name customer data auto filled and by product name product price and rest of product related things auto populated i have made sub forms for this auto population of data for product and customer 

one thing is that in one invoice i will add up to 10 products maximum

my form will search for the last invoice made and will display last invoice+1 for this new invoice in invoice number field 

and when i click the generate button this invoice will be generated and saved in the invoice table 

i am new in access(but have studied dbms and sql) and i am totally blank that how should i proceed to do the above task 

please guide me that how can i accomplish the said task 

thanks

Answer 1

"

my form will search for the last invoice made and will display last invoice+1 for this new invoice in invoice number field

and when i click the generate button this invoice will be generated and saved in the invoice table "

---

Is this a multi-user system? If so, how will you prevent multiple people generating the same number? What will you do with the number if someone does not click 'Generate'? Will you reuse the number? How? Or, throw it away? How?

What is your database table structure?

You need to define the task better before anyone can guide you in accomplishing it.

Answer 2

this is a single user system it will work on a desktop so one user will use this system at a time

if user does not press generate discard that number and  reuse again on next generation of in voice

my table structure is 

Customer(table) having following attributes

name. phone,address

Products(table) having following attributes

line number,description, price, 

invoice(table) having following attributes

customer_name, phone,address,prod1_line_number,prod1_description, prod1_price.  prod1_orderd_quantity, prod1_weight_on_ packing,prod1_ extended_price, prod2_line_number,prod2_description, prod2_price.  prod2_orderd_quantity, prod2_weight_on_ packing,prod2_ extended_price, prod3_line_number,prod3_description, prod3_price.  prod3_orderd_quantity, prod3_weight_on_ packing,prod3_ extended_price, prod4_line_number,prod4_description, prod4_price.  prod4_orderd_quantity, prod4_weight_on_ packing,prod4_ extended_price, prod5_line_number,prod5_description, prod5_price.  prod5_orderd_quantity, prod5_weight_on_ packing,prod5_ extended_price

Answer 3

invoice(table) having following attributes

customer_name, phone,address,prod1_line_number,prod1_description, prod1_price.  prod1_orderd_quantity, prod1_weight_on_ packing,prod1_ extended_price, prod2_line_number,prod2_description, prod2_price.  prod2_orderd_quantity, prod2_weight_on_ packing,prod2_ extended_price, prod3_line_number,prod3_description, prod3_price.  prod3_orderd_quantity, prod3_weight_on_ packing,prod3_ extended_price, prod4_line_number,prod4_description, prod4_price.  prod4_orderd_quantity, prod4_weight_on_ packing,prod4_ extended_price, prod5_line_number,prod5_description, prod5_price.  prod5_orderd_quantity, prod5_weight_on_ packing,prod5_ extended_price

The [Invoice] Table as posted has 5 repeating groups of Fields which violate the First Normal Form of Relational Database Design and will create many problems later. 

Suggest you revise the Table Structure of your database.  If you are not familiar with Relational Database Design and Database Normalization or need a refresher, please investigate the following short articles from Microsoft.

Microsoft Knowledge Base 283698: Understanding Relational Database Design

Microsoft Knowledge Base 283878: Description of the database normalization basics

 

In addition, please look at the sample database NorthWind that comes with your Access software which has the "Order" Form that is similar to the "Invoice" Form as a starting point for you.

 

---

Van Dinh

Answer 4

thanks for your kind advised 

i have made a separate table for products_purchased having invoice number as foreign key in that 

now what next 

i have seen northwend database but it has a lot of forms and queries that to find out the required data is very much difficult 

Answer 5

i have seen northwend database but it has a lot of forms and queries that to find out the required data is very much difficult 

You might find it easier to follow the design of the InvoicePDF demo in my public databases folder at:

 

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

 

This file's primary purpose is to illustrate how to generate reports as PDF files, but it happens to be based on a simple hypothetical invoicing application.  The model is as in the image below:

---

Ken Sheridan, Stafford, England

Answer 6

>>
i have made a separate table for products_purchased having invoice number as foreign key in that 
<<

Show us the Entities you have identified, their Relationships and then the important details (PK/FK) of each of Tables representing the identified Entities.

>>
i have seen northwend database but it has a lot of forms and queries that to find out the required data is very much difficult
<<

NorthWind, actually.  This is a TINY sample database you need to understand and apply to your database.  Look at the [Orders] Form and [Order Details] Form and the underlying Queries / Tables that supply the data to these Forms.

There is no avoidance of learning efforts if you want to develop a proper database. Have you read any Access book?

---

Van Dinh

Answer 7

thanks a lot for your kind support

yes i have studied the dbms book 2 year later but this relationship part just skipped out of my mind i have gone through  relational database book again to refresh the ideas and i have made the relationship of customer table with order table (one to many) order table  with invoice details table (one to many) invoice detail with products table(one to many)

when i make a form  through form wizard the invoice form gets generated but in that when i enter customer name details of the customer like address etc don't auto fill in the same way if i only enter the product name rest of the details of product don't auto fill how to implement this part 

stay blessed and keep smiling 

with regards

Zulqarnain

Answer 8

............but in that when i enter customer name details of the customer like address etc don't auto fill in the same way if i only enter the product name rest of the details of product don't auto fill how to implement this part

If you take a look at the InvoicePDF demo to which I referred your earlier, you'll see that the main invoice form is based on a query which joins all of the relevant tables like this:

Invoices----<Customers----<Cities----<CountiesOrRegions----<Countries

In the form a combo box is bound to the CustomerID column in Invoices, from whose drop-down list the customer can be selected by name.  Below this are text box controls bound to the relevant columns from the other tables in the query.  When a customer is selected in the combo box the customer's address data automatically shows in these controls.  The Locked property of each of these controls is set to True  (Yes) and the Enabled property to False (No) to make them read-only.

---

Ken Sheridan, Stafford, England

Answer 9

>>
...when i enter customer name details of the customer like address etc don't auto fill in the same way if i only enter the product name rest of the details of product don't auto fill how to implement this part ...
<<

That's the more reason to look at the sample Forms in the NorthWind database.  In earlier versions of NorthWind, you can see 2 different techniques of populating:

1. The customer's address (using the Row fix-up feature, also know as AutoLookup feature) which is demonstrated in Ken's sample database.

2. The shipping address which can be different from the Customer's address using the CustomerComboBox_AfterUpdate Event.  The AfterUpdate Event occurs when you select a Customer in the CustomerComboBox.

See the Microsoft Knowledge Base article Example of row fix-up in the Northwind orders form in Access 2002.

Later versions of the NorthWind sample database may not have both techniques, IIRC.

 

   

---

Van Dinh