Take another look at my original reply. You'll see that what you need is a
Company form and then 3 subforms within it so the parent Company form doesn't
include the Personnel or Capacities table; its based solely on the Company
table. Rather than simply making the Companies table the form's
RecordSource, however, its better to first create a query which sorts the
companies in name order and then use the query as the RecordSource. The
query is a very simple one:
SELECT *
FROM [Companies]
ORDER BY [CompanyName];
Save the query, as qryCompanies say, and then base a form on it. You can
use the form wizard to create the form and then amend it later when you add
the subforms. The form must be in single form view, though.
You now need to create three separate subforms, but as they'll be identical
apart from their RecordSource properties you can just create one and then
copy and paste it under new names twice to create the others. You just have
to change the RecordSource property of the two copies.
First create the three queries on which the subforms will be based:
For directors:
SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Director";
For shareholders:
SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Shareholder";
And for officers:
SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Officer";
Now create the first form which you'll use as the directors subform. This
will be a form in continuous form view based on the first of the above
queries.
Each subform would be in continuous form view (you can use the wizard to
create it) and have just one visible control, a combo box set up as follows:
ControlSource: PersonnelID
RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM
[Personnel] ORDER BY [Last Name], [First Name];
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
Each subform should also have a text box bound to the Capacity field, but
with its Visible property set to False (No) and its DefaultValue property set
to Director, Shareholder or Officer as appropriate in each case.
So if you use the form wizard to create the first subform include the
PersonnelID and Capacity fields, but then hide the latter by setting its
Visible property to False (No) in form design view.
In answer to your question you'll find ControlSource as a property of the
combo box on the data tab of its properties sheet.
Once you've created the first subform copy and paste it twice, giving it a
different name each time, so you might end up with three identical forms
called sfrDirectors, sfrShareholders and sfrOfficers for instance (what names
you use is entirely your choice, but it helps if the names are meaningful).
Open each of the two copies in design view and change the form's RecordSource
property to the appropriate one of the three queries.
You can now embed the three forms in the original Company form as subforms.
Open the Company form in design view and size the window so there's some
spare space and then drag the edge of the form design area to create some
empty space on it to place the subforms. You can add each subform either by
adding a subform control from the toolbox and setting the SourceObject
property to one of the three subforms you created, or by dragging and
dropping the source forms from the database window onto the design area of
the company form.
Move the subforms around to position them on the main parent form how you
want and size them as appropriate. You'll probably find that putting the
three side by side at the bottom makes sense.
To link with the parent form the LinkMasterFields and LinkChildFields
properties of each of the three subform controls should be CompanyName,
though you'll very likely find that Access has already done this for you on
the basis of the relationships you created.
You should now find that as you moved from company to company through the
main form the subforms will change to show the directors, shareholders and
officers for the current company.
Finally I explained in my first reply how to include unbound combo boxes on
the main company form to search for a company or person, but I'll repeat what
I said (make sure the field an table names in the code match what you've used)
To search for a company you can use the built in 'Find' facility or you can
add an unbound combo box to the main form with a RowSource of:
SELECT [CompanyName] FROM [Companies] ORDER BY [CompanyName];
and with the following code in its AfterUpdate event procedure:
Dim rst As Object
Dim ctrl As Control
Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl
With rst
.FindFirst "[CompanyName] = """ & ctrl & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
To find a person, whatever their capacity add another unbound combo box to
the main form set up in the same way as those in the subforms:
RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
In its AfterUpdate event procedure put the following code:
Dim strFilter As String
Dim ctrl As Control
Set ctrl = Me.ActiveControl
strFilter = "[CompanyName] IN" & _
"(SELECT [CompanyName] " & _
"FROM [Capacities] " & _
"WHERE [PersonnelID] = " & ctrl & ")"
Me.Filter = strFilter
Me.FilterOn = True
Note that, unlike the combo box for finding a company, this filters the
form. This is because more than one company might be associated with the
selected person. You can clear the filter using the built in button on the
toolbar, or you can add a 'Show All' button to the main form with the
following in its Click event procedure:
Me.FilterOn = False
One thing you might like to do before you do any of this is check that the
tables, now that you've got all the data in, do give the correct results when
joined. You can do this with a query which will list all companies, with
their directors, officers, shareholders. You don't have to save the query if
you don't wish to, just create it in SQL view and then switch to datasheet
view to see the results:
SELECT [Companies].[CompanyName], [Capacity], [FirstName], [LastName]
FROM [Companies], [Capacities], [Personnel]
WHERE [Companies].[CompanyName] = [Capacities].[CompanyName]
AND [Personnel].[PersonnelID] = [Capacities].[PersonnelID]
ORDER BY [Companies].[CompanyName], [Capacity], [LastName], [Firstname];
Ken Sheridan
Stafford, England
pupkiss1965 said:
Where do the 3 queries come in (director, shareholder and officer)? When I
made my Company form I selected the Personnel Table and Capacities Table but
I didnt see where I had to put "ControlSource: PersonnelID
RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
From the instructions you gave, I think I missed an important step here.
