Lexy:
You'll need to base the parent form on a query which restricts the rows
returned by means of a subquery. You can use the EXISTS predicate in the
query's WHERE clause and correlate the query with the subquery on CustomerID
(or whatever the key is). The query will then return only those companies
where there is at least one row in the Contact Log table relating to the
current company which has a value of TRUE in the Boolean (Yes/No) column to
which the check box is bound. So, assuming that Company and Contacts are
related on CompanyID and Contacts and Contact Logs are related on
ContactLogID, and that your Boolean column in Contact Logs is called
MyYesNoField the query would be along these lines:
SELECT *
FROM [Company]
WHERE EXISTS
(SELECT *
FROM [Contact Logs] INNER JOIN [Contacts]
ON [Contact Logs].[ContactID] = [Contacts].[ContactID]
WHERE [Contacts].[CompanyID] = [Company].[CompanyID]
AND [MyYesNoField] = TRUE)
ORDER BY [CompanyName];
You'll need to create the query in SQL view. If you are not familiar with
creating a query in SQL then what you do is open the query designer in the
usual way; don't add any tables; from the View menu select SQL View; type or
paste in the SQL as above, and amend it to use your real table and column
names.
If you want to be able to toggle the form between all companies and those
restricted as above, then save the above query as qryCompaniesRestricted say,
and create another query, qryCompaniesAll say, as follows:
SELECT *
FROM [Company]
ORDER BY [CompanyName];
Set the form's RecordSource property to qryCompaniesAll to show all
companies by default. Then add an unbound check box to your form,
chkRestrictCompanies say.
In its AfterUpdate event procedure put the following code:
If Me.chkRestrictCompanies Then
Me.RecordSource = "qryCompaniesRestricted"
Else
Me.RecordSource = "qryCompaniesAll"
End If
Me.Requery
In the form's Open event procedure set the unbound check box's value to
False with:
Me.chkRestrictCompanies = False
If you are unfamiliar with entering code in event procedures, to do this
select the form or control in form design view and open its properties sheet
if its not already open. Then select the relevant event property in the
properties sheet. Click on the 'build' button; that's the one on the right
with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines already
in place. Enter the line(s) of code between these two existing lines.
Ken Sheridan
Stafford, England
Lexy said:
Hi
My database is relational, with several tables (1) Company, (2) Contacts and
(3) Contact Log.
It therefore allows the user to store and view numerous Contactees per
Company and numerous Contact Logs per Contact and the main Company Form is
set up with subforms for Contacts and Contact Logs, so you can see all the
pertinent information quickly (i.e. how many contacts there are per company
and the pertinent contact log information per contact therein).
In the Contact Log table there is a tick box. I would like to be able to
view all companies that have this tick box ticked via the form view (with the
relevant subforms). Does anyone have any ideas on how this could be done
please?
Thank you.
