[nyzil]

I have a simple application for taking Customer orders and their Products. I am using Visual Studio 2008 Express Edition and MS Access 2007.

My application uses multiple forms. One of the forms is an Add form from where a New Customer along with his/her details like address, phone, city, state etc can be added. Another form is for the Product Details to be added.

I want to know how to retrieve a CustomerID from an MS Access 2007 database for either the newly added Customer or an existing one and insert multiple rows or records for the same CustomerID, as a Customer can order multiple products. I am using the following code for the Add button:

 Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click

        MyBindingSource.EndEdit()
        DialogResult = Windows.Forms.DialogResult.OK

    End Sub

This code adds a new Customer into the datatable and later calling update() to enter into the database with an AutoIncremented CustomerID. I have a different code for AutoIncrementing the CustomerID so the user does not have to add a new CustomerID himself and makes duplicate entries or silly mistakes. When the Add button is clicked, a Add New Record form opens with an AutoIncremented ID.

Now I need to know how to add 2 or more Product Details or in other words rows/records for this same CustomerID.

How can I do this ?

Can anyone help please ?

This post has been edited by smohd: 14 November 2011 - 12:59 AM
Reason for edit:: Code tags added. Please use [code] tags when posting codes

[Martyr2]

Ok, first of all you don't need to actually be creating the new incremented ID yourself I hope you know. Access has this as the field type "autonumber". In any queries you make to the database, you leave out this field value and when inserted will take the next value in sequence.

Second, for existing customer IDs you look up the customer, select their ID (with a select statement) and use that in inserts in the Order details table. Your order details should have a foreign key relationship back to the main orders table. For instance, it should have a primary key for the detail row and then a column like "OrderID" which will link to the ID field of the Orders table. This will allow you to insert multiple rows into this field and have them link back to the orders the details belong to.

Lastly, for retrieving the customer id of the newly inserted customer you can use a special select query called "Select @@Identity". The following link shows you some code on how you can use this query to fetch the newly minted ID... HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET.

I believe that is everything. Enjoy!