If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Limit change to one record, not entire database
Access 2000
The database is working correctly and stores data except when editing. I created an InformationTable with all variables. Then used the Analysis Wizard to create 5 specific tables. The Wizard then created the Query, CustomerInformationTable. I previously had 15 items in the InformationTable when added additional variables. In Forms, the problem is when I edit one item, say contact name, and save the record, all the records in the database are updated with the new contact name instead of just the specific record. The individual table, ContactName only shows one record; the new record added after adding the ContactName variable. The Query, CustomerInformationTable, has added the new contact name to all records including the original 15 records instead of just the record that was changed changed. What needs to be changed to eliminate this problem? Thanks. |
#2
|
|||
|
|||
Limit change to one record, not entire database
The Control Source for the contact name is the variable,
CustomerContactName1. The variable, CustomerContactName1, is in the Detail section of the form and in CustomerContactTable The relationship to the main table, CustomerCompanyTable, is infinity on the main table and 1 on the CustomerContactTable. The Record Source for the form is the Query, CustomerInformationTable. Ken "Ken Snell (MVP)" wrote in message ... Hard to say without knowing more about your form's setup, but my guess is that the control in which the contact name is being entered is not part of the Detail section for a continuous forms view format of the form. Sounds as if it's in the header section. Give us more details. -- Ken Snell MS ACCESS MVP "Ken" wrote in message ... Access 2000 The database is working correctly and stores data except when editing. I created an InformationTable with all variables. Then used the Analysis Wizard to create 5 specific tables. The Wizard then created the Query, CustomerInformationTable. I previously had 15 items in the InformationTable when added additional variables. In Forms, the problem is when I edit one item, say contact name, and save the record, all the records in the database are updated with the new contact name instead of just the specific record. The individual table, ContactName only shows one record; the new record added after adding the ContactName variable. The Query, CustomerInformationTable, has added the new contact name to all records including the original 15 records instead of just the record that was changed changed. What needs to be changed to eliminate this problem? Thanks. |
#3
|
|||
|
|||
Limit change to one record, not entire database
I am new at this so I do not know the terminology.
See below: "Ken Snell (MVP)" wrote in message ... Let's go through what you've posted so that I can be sure that I'm understanding correctly. See inline.... -- Ken Snell MS ACCESS MVP "Ken" wrote in message ... The Control Source for the contact name is the variable, CustomerContactName1. There are no variables on a form. Do you mean that the control name is CustomerContactName1? or is the field named CustomerContactName1, and the control is bound to this field? What is the name of the control itself, and what type of control is it? I hope this answers your questions. From properties Text Box: CustomerContactName1 Name: CustomerContactName1 Control Source: CustomerContactName1 I do not understand Type of Control? CustomerContactName1 is in CustomerContactTable The variable, CustomerContactName1, is in the Detail section of the form and in CustomerContactTable CustomerContactName1 is a field in the CustomerContactTable, is this Yes, CustomerContactName1 is in CustomerContactTable correct? A field is not actually "in" any part of a form. However, a control is in a part of a form, and a control can be bound to a field (i.e., the Control Source of the control is the field). The relationship to the main table, CustomerCompanyTable, is infinity on the main table and 1 on the CustomerContactTable. OK. What are the linking fields? What are the fields in the two tables? The linking field in the CustomerContactTable is ID which has a number 1 next to it outside the box. The linking field in the CustomerCompanyTable (master table) is CustomerContactTable_ID The Record Source for the form is the Query, CustomerInformationTable. What is this query? Can you post the SQL statement of the query? The Query which is listed under the Objects toolbar was generated by the Wizard after TOOL/ANALYZE/TABLE was run. I believe it links all the fields together???? "Ken Snell (MVP)" wrote in message ... Hard to say without knowing more about your form's setup, but my guess is that the control in which the contact name is being entered is not part of the Detail section for a continuous forms view format of the form. Sounds as if it's in the header section. Give us more details. -- Ken Snell MS ACCESS MVP "Ken" wrote in message ... Access 2000 The database is working correctly and stores data except when editing. I created an InformationTable with all variables. Then used the Analysis Wizard to create 5 specific tables. The Wizard then created the Query, CustomerInformationTable. I previously had 15 items in the InformationTable when added additional variables. In Forms, the problem is when I edit one item, say contact name, and save the record, all the records in the database are updated with the new contact name instead of just the specific record. The individual table, ContactName only shows one record; the new record added after adding the ContactName variable. The Query, CustomerInformationTable, has added the new contact name to all records including the original 15 records instead of just the record that was changed changed. What needs to be changed to eliminate this problem? Thanks. |
#4
|
|||
|
|||
Limit change to one record, not entire database
"Ken Snell (MVP)" wrote in message
... OK, let's walk through this together. (ACCESS can be a bit daunting... only way to learn it is to plunge in and swim with the fishes! grin ) You're using a textbox control on the form; this textbox is in the form's Detail section. The name of the textbox is CustomerContactName1. This textbox is bound to a field named CustomerContactName1 (the Control Source of this textbox is CustomerContactName1). CustomerContactName1 is a field in the CustomerContactTable table. Correct! There are two tables of interest he CustomerContactTable and CustomerCompanyTable. These tables are related by these two fields: CustomerContactTable.ID (ID field in CustomerContactTable table) and CustomerCompanyTable.CustomerContactTable_ID (CustomerContactTable_ID field in CustomerCompanyTable table). Correct! The CustomerContactTable is the parent ("one" side of the "one-to-many" relationship between the two tables) table, and the CustomerCompanyTable is the child ("many" side of the "one-to-many" relationship between the two tables) table. That is the way it is set up, but it may be set up wrong. The idea is to have the basic company info, name address etc, (parent?) in CustomerCompanyTable and the up to three contacts, person's name, telephone number, etc in CustomerContactTable (child?). Seems like it should be one on the CustomerCompanyTable and many on CustomerContactTable. Do I need to change the one-to-many relationship? I choose option #1 Only include rows where the joined fields from both tables are equal. You're using a query named CustomerInformationTable as the form's Record Source. This query was generated by a wizard. Is this information correct so far? Correct! If it is, then here are questions / information that we need from you: 1) What is the "Default View" property of the form itself? Single Form 2) Post the SQL statement of the CustomerInformationTable query. You can get the SQL statement by opening the query in design view, then click on the "View" icon at top left of toolbar, select SQL View, copy the entire text that you see there, and paste that text in your reply. SELECT CustomerCompanyTable.CustomerID, CustomerCompanyTable.Active, CustomerCompanyTable.CompanyName, CustomerCompanyTable.DivisionOf, CustomerCompanyTable.Address1, CustomerCompanyTable.Address2, CustomerCompanyTable.POBox, CustomerCompanyTable.City, CustomerCompanyTable.State, CustomerCompanyTable.Zipcode, CustomerCompanyTable.[Zip+4], CustomerCompanyTable.[800TelephoneNumber], CustomerCompanyTable.[800 Ext], CustomerCompanyTable.TelephoneNumber, CustomerCompanyTable.TeleExt, CustomerCompanyTable.Fax, CustomerCompanyTable.FaxExt, CustomerCompanyTable.Webpage, CustomerCompanyTable.Email, CustomerCompanyTable.FirstName, CustomerCompanyTable.Initial, CustomerCompanyTable.LastName, CustomerCompanyTable.Title, CustomerCompanyTable.Catagory, CustomerCompanyTable.Accountnumber, CustomerCompanyTable.Year, CustomerCompanyTable.Comments, CustomerCompanyTable.CustomerOtherInfo1, CustomerCompanyTable.CustomerOtherInfo2, CustomerCompanyTable.CustomerProductTable_ID, CustomerCompanyTable.CustomerContactTable_ID, CustomerContactTable.CustomerContactName1, CustomerContactTable.CustomerContactTelephone1, CustomerContactTable.CustomerContactFax1, CustomerContactTable.CustomerContactEmail1, CustomerContactTable.CustomerContactName2, CustomerContactTable.CustomerContactTelephone2, CustomerContactTable.CustomerContactFax2, CustomerContactTable.CustomerContactEmail2, CustomerContactTable.CustomerContactName3, CustomerContactTable.CustomerContactTelephone3, CustomerContactTable.CustomerContactFax3, CustomerContactTable.CustomerContactEmail3, CustomerContactTable.ID AS CustomerContactTable_ID, CustomerProductTable.CustomerProduct1, CustomerProductTable.CustomerProduct2, CustomerProductTable.CustomerProduct3, CustomerProductTable.CustomerProduct4, CustomerProductTable.CustomerProduct5, CustomerProductTable.CustomerProduct6, CustomerProductTable.ID AS CustomerProductTable_ID FROM CustomerProductTable INNER JOIN (CustomerContactTable INNER JOIN CustomerCompanyTable ON CustomerContactTable.ID = CustomerCompanyTable.CustomerContactTable_ID) ON CustomerProductTable.ID = CustomerCompanyTable.CustomerProductTable_ID; Note there is another table, CustomerProductTable in the SQL in which six products the company sells can be chosen, CustomerProduct1...CustomerProduct6. 3) Tell us how the form is structured. Also tell us what steps you used to make the change to the one record (which then caused all the records to be changed to show the same change). To edit a record: Select the record with form CustomerFindInformationEdit Record Source: CustomerInformationTable After selecting a comapny name, PickList Cntrol Source: blank Click OK Name: cmdGo SQL: Private Sub cmdClose_Click() On Error Resume Next DoCmd.Close End Sub Private Sub cmdFind_Click() ' Find Customer Information records On Error GoTo HandleErr 'Find specific record Dim strSQL As String DoCmd.OpenForm "CustomerFindInformationEdit", acNormal, , , , acDialog ExitHe Exit Sub HandleErr: Select Case Err Case Else MsgBox Err & ": " & Err.Description, vbCritical, _ "Error in Form_CustomerInformationEdit.cmdFind_Click" End Select Resume ExitHere Resume End Sub Private Sub cmdGo_Click() ' CustomerInformationEdit form based on the selected items On Error GoTo HandleErr If Len(Me!PickList) 0 Then DoCmd.OpenForm "CustomerInformationEdit" With Forms!CustomerInformationEdit ' Construct SQL for CustomerInformationTable's Recordsource Select Case optChoose Case 1 ' Company Name mstrSQL = "SELECT * FROM CustomerInformationTable Where " _ & " CompanyName Like '*" & DoubleQuote(Me![PickList]) & "*'" DoCmd.Close acForm, "CustomerFindInformationEdit" Case 2 ' TelephoneNumber mstrSQL = "SELECT * FROM CustomerInformationTable WHERE " _ & " TelephoneNumber Like '*" & DoubleQuote(Me![PickList]) & "*'" DoCmd.Close acForm, "CustomerFindInformationEdit" Case 3 ' CustomerID mstrSQL = "SELECT * FROM CustomerInformationTable WHERE " _ & " CustomerID like '*" & DoubleQuote(Me![PickList]) & "*'" DoCmd.Close acForm, "CustomerFindInformationEdit" Case Else End Select .RecordSource = mstrSQL End With Else MsgBox ("Select a Company Name, Telephone Number or Customer ID for search") End If ExitHe Exit Sub HandleErr: Select Case Err Case Else MsgBox Err & ": " & Err.Description, vbCritical, _ "Error in Form_CustomerFindInformationEdit.cmdGo_Click" End Select Resume ExitHere Resume End Sub Private Sub optChoose_AfterUpdate() ' Populate rowsource of PickList Dim strSQL As String On Error GoTo HandleErr Select Case optChoose Case 1 ' Company Name strSQL = "Select Distinct CompanyName from CustomerInformationTable " _ & "Order By CompanyName" Case 2 ' TelephoneNo1 strSQL = "Select Distinct TelephoneNumber from CustomerInformationTable " _ & "Order By TelephoneNumber" Case 3 ' Customer ID strSQL = "Select Distinct CustomerID from CustomerInformationTable " _ & "Order By CustomerID " Case Else End Select With Me!PickList .Value = Null .RowSource = strSQL .Requery .Value = .ItemData(0) End With ExitHe Exit Sub HandleErr: Select Case Err Case Else MsgBox Err & ": " & Err.Description, vbCritical, _ "Error in Form_CustomerFindInformationEdit.optChoose_AfterUp date" End Select Resume ExitHere Resume End Sub Private Function DoubleQuote(strIn As String) As String Dim i As Integer Dim strtemp As String For i = 1 To Len(strIn) If Mid(strIn, i, 1) = "'" Then strtemp = strtemp & "''" Else strtemp = strtemp & Mid(strIn, i, 1) End If Next i DoubleQuote = strtemp End Function ------- The new data is entered into form "CustomereInformationEdit" in the various Textbox controls. CompanyName, ... Contact#1name, telephone... To save the data, "Save Change" button is clicked. Then message "Do you want to save changes?" is opened and then clicked, yes or no. VB: Private Sub Cancel_Click() ' Close Form do not save changes On Error GoTo Err_Cancel_Click Me.Undo DoCmd.Close Exit_Cancel_Click: Exit Sub Err_Cancel_Click: MsgBox Err.Description Resume Exit_Cancel_Click End Sub Private Sub cmdFindCustomer_Click() ' Open the CustomerFindInformationEdit form records depending on user's last action On Error GoTo HandleErr ' Find specific record Dim strSQL As String DoCmd.OpenForm "CustomerFindInformationEdit", acNormal, , , , acDialog ExitHe Exit Sub HandleErr: Select Case Err Case Else MsgBox Err & ": " & Err.Description, vbCritical, _ "Error in Form_CustomerFindInformationEdit.cmdFind_Click" End Select Resume ExitHere Resume End Sub Private Sub cmdSave_Click() On Error GoTo Err_cmdSave_Click If MsgBox("Do you want to save the changes?", vbYesNo, "Save Change") = vbYes Then DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 DoCmd.Close DoCmd.OpenForm "CustomerFindInformationEdit", acNormal Else Me.Undo End If Exit Sub Err_cmdSave_Click: MsgBox Err.Description ' Resume Exit_cmdSave_Click() End Sub Private Sub DeleteCustomer_Click() DoCmd.RunCommand acCmdDeleteRecord DoCmd.Close DoCmd.OpenForm "CustomerFindInformationEdit", acNormal On Error GoTo Err_Continue Err_Continue: End Sub ---------------------------------------------------- The form also has "Cancel" and "Delete Customer" buttons Ken, I really want to know how to correct the problem and not just have the problem corrected. If you prefer, I can send you the file. There is nothing confidential in the file at this time. File size: 3.19 MB Ken K Ken Snell MS ACCESS MVP "Ken" wrote in message ... I am new at this so I do not know the terminology. See below: snipped "Ken" wrote in message ... Access 2000 The database is working correctly and stores data except when editing. I created an InformationTable with all variables. Then used the Analysis Wizard to create 5 specific tables. The Wizard then created the Query, CustomerInformationTable. I previously had 15 items in the InformationTable when added additional variables. In Forms, the problem is when I edit one item, say contact name, and save the record, all the records in the database are updated with the new contact name instead of just the specific record. The individual table, ContactName only shows one record; the new record added after adding the ContactName variable. The Query, CustomerInformationTable, has added the new contact name to all records including the original 15 records instead of just the record that was changed changed. What needs to be changed to eliminate this problem? Thanks. |
#5
|
|||
|
|||
Limit change to one record, not entire database
Ken,
FYI I have been doing some experimenting. I think the problem is in the Access program not the configuration. I added a few new records into the database for testing the program. The remaining records are from as early as 1988, most are from 1990s. When I edit the new records, there is no problem. When I edit the old records, the CustomerProduct and CustomerContact field flow through the other records. Looks like I have to reenter the old data. I tried to import them into a new database, but there was no difference. Ken "Ken Snell (MVP)" wrote in message ... Did not get a chance to review this tonite. I will post a reply tomorrow. -- Ken Snell MS ACCESS MVP "Ken" wrote in message ... "Ken Snell (MVP)" wrote in message ... OK, let's walk through this together. (ACCESS can be a bit daunting... only way to learn it is to plunge in and swim with the fishes! grin ) snipped |
#6
|
|||
|
|||
Limit change to one record, not entire database
Thanks for the help.
Ken "Ken Snell (MVP)" wrote in message ... Sounds as if you have "found" a solution. Not sure why the new records are handled differently from the old records unless the data type or format of the old data doesn't match what you're using for the new data. -- Ken Snell MS ACCESS MVP "Ken" wrote in message ... Ken, FYI I have been doing some experimenting. I think the problem is in the Access program not the configuration. I added a few new records into the database for testing the program. The remaining records are from as early as 1988, most are from 1990s. When I edit the new records, there is no problem. When I edit the old records, the CustomerProduct and CustomerContact field flow through the other records. Looks like I have to reenter the old data. I tried to import them into a new database, but there was no difference. Ken "Ken Snell (MVP)" wrote in message ... Did not get a chance to review this tonite. I will post a reply tomorrow. -- Ken Snell MS ACCESS MVP "Ken" wrote in message ... "Ken Snell (MVP)" wrote in message ... OK, let's walk through this together. (ACCESS can be a bit daunting... only way to learn it is to plunge in and swim with the fishes! grin ) snipped |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access Mail Merge to Word.doc files ? | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 18th, 2005 06:31 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Prevent Blank Records being written. Need Help. | Robert Nusz @ DPS | Using Forms | 4 | December 29th, 2004 05:15 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |