Inserting new rows into MS Access Database for a Single Record

Posted by Swampmonster under VB.NET on 11/17/2011 | Points: 10 | Views : 2101 | Status : [Member] | Replies : 0
I have an application which uses multiple forms. Am using Visual Basic 2008 Express Edition and MS Access 2007 database.
This is basically an application for Customers and Orders.

I have a Main form which has a ComboBox to select a location for the Customer, several TextBoxes, a DataGridView control and some buttons.

One of the buttons is an Add button, pressing which opens an Add New Customer form. As soon as this button is pressed, the CustomerID TextBox is displayed with an Auto Incremented CustomerID which is an Auto Number field. The user fills in all the TextBoxes like name, age, sex, address, phone, email etc. and presses the Next button to go to the next form, Order Details form which is used for entering Order Details for the Customer. The Order Details form has a TextBox which takes the Auto Incremented CusotmerID from the Add New Customer form and simply displays it on the form. Details of Orders are filled in this form and the Save button is clicked which adds the newly added Customers and their Order Details back into the database. Everything works fine here.

I have another form for adding multiple Rows for a Customer, since a Customer can order multiple products.
This form also has a ComboBox for selecting the location of the new Customer and a ListBox on the right where Customers from that particular location are displayed as soon as the Select button (for selecting the location of the Customer) is clicked.

The problem begins when I try to Add More Orders or in other words MORE ROWS for this CustomerID. As soon as the Add More Orders Button is clicked I want to :

1. Display the current CustomerID in the CustomerID TextBox on the Order Details form, so the user will be sure he/she is entering Orders Details for the current CustomerID.

2. On clicking the Save button on the Order Details form, a dialog box should appear asking the user whether he/she would like to add another Order Detail (OR IN OTHER WORDS ANOTHER NEW ROW/RECORD). If the user clicks YES, then the Order Details form should be cleared for another Fresh entry that when saved, will create another NEW ROW. If the user selects NO, only ONE NEW ROW or RECORD is added to the database.

When I click the Add Button on the Main form to add a NEW CUSTOMER, this opens up the Add New Customer form with an Auto Incremented CustomerID displayed in a TextBox control and clicking on the NEXT button opens the Order Details form with the SAME Auto Incremented CustomerID displayed in another TextBox control.

My problem is when I want to Retrieve a CustomerID from the database it displays fine in the TextBox on the Find Customer form but if I want to add another new Order Detail (Another NEW ROW) for the RETRIEVED CUSTOMER, the TextBox on the Order Details form still shows the Auto Incremented CustomerID from the Add New Customer form instead of the RETRIEVED CUSTOMERID !!!

The following code displays the RETRIEVED CustomerID in the Find Customer form and other details in TextBoxes on the form:

Private Sub cmdSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelect.Click
Me.CustomerListBox.DataSource = Nothing
Call ShowData()
Dim Table As MyDataSet.CustomerDataTable
Table = CustomerTableAdapter.GetCustomersByDistrictID(CInt(ComboDistrict.SelectedValue))
CustomerListBox.DisplayMember = "Customer"
CustomerListBox.ValueMember = "CustomerID"
CustomerListBox.DataSource = Table
End Sub

I am using the following code to display the Auto Incremented CustomerID in the Add New Customer form:

txtCustomerID.Text = (CustomerTableAdapter1.GetMaximumIndex() + 1).ToString.PadLeft(6, "0"c)
How can I achieve the above functionality in (1) and (2) above as well as the problem mentioned above ???

Can anyone please help me with this ??
Thanks a lot !


(No response found.)

Login to post response