Ken Snell (MVP) said:
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 here: 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
ExitHere:
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
ExitHere:
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
ExitHere:
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in
Form_CustomerFindInformationEdit.optChoose_AfterUpdate"
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
ExitHere:
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
