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.

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.

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.

"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.

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

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