Kim:
You are missing the point which Dorian was making. As each contact can
presumably only work for one company, once you know who is the contact you
then know the company. It is said that contact 'determines' company. So if
you have columns in the Jobs table for both the company and the contact, the
company column introduces redundancy into the table, so it is not correctly
normalized as it includes a 'transitive functional dependency', Job---
Contact---->Company. This leaves the table at risk of inconsistent data as
there is nothing to stop a row in the table including a contact and company
for whom the contact does not work (as you have found out). So you should
only have a column for the contact in the Jobs table, not the company; the
integrity of the data is then protected
It is still possible when entering a row in the Jobs table to do so by
selecting a company first, then a contact, by using correlated combo boxes,
the one to select the company being unbound.
As regards the correlated combo boxes how you set these up depends on whether
the form is in continuous forms view or single form view. If the former, and
you are using 'surrogate' numeric keys for the tables (ContactID in the
example below as names are unsuitable as 'natural' keys being legitimately
duplicated) then you cannot use combo boxes alone as controls in rows will go
blank if you select a different higher level value (Company in the example
below in the other). The solution is to use hybrid controls by superimposing
a text box on each combo box so that it looks like a single combo box control
to the user. Single forms are much simpler and don't require the hybrid
controls, merely the combo boxes
A. For continuous forms here's an example of a Jobs form which includes
correlated combo boxes for Company and Contact, while maintaining the Jobs
table in Third Normal Form by having an ContactID column, but not a Company
column:
1. An unbound cboCompanies with a RowSource property of:
SELECT Company
FROM Companies
ORDER BY Company;
and an AfterUpdate event procedure of:
Private Sub cboCompanies_AfterUpdate()
' requery Contacts combo box to
' show Contacts with selected Company
Me!cboContacts.Requery
' clear Contacts combo box
Me!cboContacts = Null
End Sub
2. A bound cboContacts combo box with a ControlSource property of ContactID
and a RowSource property of:
SELECT ContactID, Contact
FROM Contacts
WHERE Company =Form!cboCompanies
ORDER BY Contacts.Contact;
The ColumnCount property is 2 and the ColumWidths property is 0cm;8cm to hide
the first column.
Note the use of the Form property in the above rather than a full reference
to the form. This is possible as both controls are ion the same form.
3. An unbound text box txtCompany superimposed of cboCompanies, with a
ControlSource property of:
=GetCompany([cboContacts])
4. An unbound text box txtContact superimposed of cboContacts , with a
ControlSource property of:
=GetContact([cboContacts])
Along with the other code the form's module in total would thus be:
''''module starts''''
Option Compare Database
Option Explicit
Private Function GetCompany(varContactID)
' get Company for current value of ContactID field
If Not IsNull(varContactID) Then
GetCompany = DLookup("Company", "Contacts", "ContactID = " &
varContactID)
Else
GetCompany = Me.cboCompanies
End If
End Function
Private Function GetContact(varContactID)
If Not IsNull(varContactID) Then
GetContact = DLookup("Contact", "Contacts", "ContactID = " &
varContactID)
End If
End Function
Private Sub cboCompanies_AfterUpdate()
' requery Contacts combo box to
' show Contacts with selected Company
Me!cboContacts.Requery
' clear Contacts combo box
Me!cboContacts = Null
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
Me!cboCompanies = Null
Else
Me!cboCompanies = GetCompany(Me!cboContacts)
End If
Me!cboCompanies.Requery
Me!cboContacts.Requery
End Sub
''''module ends''''
Basically the way it works is that when you move focus to one of the combo
boxes by clicking on its arrow its text box part becomes visible and its list
drops down. When you move focus off the control the superimposed text box
becomes visible. The functions get the text values for these by looking them
up from the relevant table on the basis of the corresponding key value which
is the hidden value of the bound cboContacts combo box.
B. For single form view you can dispense with the two text boxes txtCompany
and txtContact and the module is simpler:
''''module starts
Option Compare Database
Option Explicit
Private Function GetCompany(varContactID)
' get Company for current value of ContactID field
If Not IsNull(varContactID) Then
GetCompany = DLookup("Company", "Contacts", "ContactID = " &
varContactID)
Else
GetCompany = Me.cboCompanies
End If
End Function
Private Sub cboCompanies_AfterUpdate()
' requery Contacts combo box to
' show Contacts with selected Company
Me!cboContacts.Requery
' clear Contacts combo box
Me!cboContacts = Null
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
Me!cboCompanies = Null
Else
Me!cboCompanies = GetCompany(Me!cboContacts)
End If
Me!cboCompanies.Requery
Me!cboContacts.Requery
End Sub
''''module ends''''
Watch out for any lines which your newsreader might have split over two lines
in the above.
If you'd like a copy of the demo file from which the above code is adapted,
mail me at:
kenwsheridan<at>yahoo<dot>co<dot>uk
For searching purposes you would simply need to base search on a query which
joins Job Numbers and Contacts (the Companies table is not needed as the
Contacts table includes a Company column as a foreign key). The search can
be via an unbound dialogue form with combo boxes cboCompany and cboContact.
The RowSource for the former would be:
SELECT Company FROM Companies ORDR BY Company;
The RowSource for the latter:
SELECT ContactID, Contact FROM Contacts WHERE Company = Form!cboCompany OR
Form!cboCompany IS NULL ORDER BY Contact;
In the AfterUpdate event procedure of cboCompany set to Null and requery
cboContact with:
Me.cboContact = Null
Me.cboContact.Requery
From the dialogue form open a form or report based on a query which
references the two combo boxes on the form as parameters, testing for Null in
each case to allow a search to be made by a company, a contact or a contact
selected from a restricted list after first selecting a company:
SELECT [Job], [Company], [Contact]
FROM [Job Numbers] INNER JOIN [Contacts]
ON [Job Numbers] INNER JOIN [Contacts]
ON [Job Numbers].[ContactID] = [Contacts].ContactID]
WHERE ([Contacts].[ContactID] = Forms![YourDialogueForm]![cboContact]
OR Forms![YourDialogueForm]![cboContact] IS NULL)
AND ([Company] = Forms![YourDialogueForm]![cboCompany]
OR Forms![YourDialogueForm]![cboCompany] IS NULL);
Note that the parentheses in the above query's WHERE clause are crucial to
force each OR operation to evaluate independently of the AND operation. You
can of course return any other columns you wish in the table and could also
join the Companies table in the query if there are other columns from that
table you'd want returned.
Ken Sheridan
Stafford, England
kim said:
Dorian, the "Jobs" refer to inspections that we perform. I was trying to
include both the Company and the individual Contact (or contacts as the case
may be) associated with each job. Typically we do a Job search based on the
Job # and/or Company and/or item inspected. However, there are cases in
which we do a search based on the Contact. I was running into a problem in
which the Job table was able to give me the option of including a Company and
a Contact. However, ALL my Contact names were listed, instead of simply
those associated with the Company. I have had a difficult time explaining
the situation and I have an Access "How-To" book and it sort of addresses
this issue, but then drops off. I may have found a way around my problem. I
will keep working on it. Thanks for the input.
Unless a contact can be shared between companies, the contact already defines
the company so there is no need for the job to link to both.
[quoted text clipped - 13 lines]
see all Contacts with their associated Company by clicking on the plus (+)
sign beside each company, but can't see this in the Job Numbers table.
