Finding a record in a main form including a subform?

[Guest]

I have set up a database using 2 tables (one with organisations and one with
people). I have set up a relationship between the 2 tables with a
many-to-one relationship, linking people to the relevant organisation. I
created a form based on the 'organisation' table, then created a subform
based on the 'people' table within this main form.
I would like the database users to be able to search the entire form for a
specific person, using the find function (binoculours icon on the toolbar).
At the moment, this function will only find people within the currently
selected organisation.
This function works fine for finding an organisation in the entire form, but
not people.

 

[Guest]

Untested --

In the query for the main form create a ComboBox with the people table as
record source. Default the ComboBox to an "*". Look people but display
organization (column display controls this).
Have criteria for the main form for the organization field as --
Like [Forms]![YourMainForm]![YourComboBox]

 

[Guest]

Unfortunately I wasn't able to follow your instructions. The main form is
based on a table, not a query. I tried creating a combo box in the form,
however the wizard didn't give me an option to type in the "*" as default or
the criteria that you described. It just asked which table to base the combo
box on, which fields to view and in what order. Can you shed any further
light?
Thank you.

Untested --

In the query for the main form create a ComboBox with the people table as
record source. Default the ComboBox to an "*". Look people but display
organization (column display controls this).
Have criteria for the main form for the organization field as --
Like [Forms]![YourMainForm]![YourComboBox]

I have set up a database using 2 tables (one with organisations and one with
people). I have set up a relationship between the 2 tables with a
many-to-one relationship, linking people to the relevant organisation. I
created a form based on the 'organisation' table, then created a subform
based on the 'people' table within this main form.
I would like the database users to be able to search the entire form for a
specific person, using the find function (binoculours icon on the toolbar).
At the moment, this function will only find people within the currently
selected organisation.
This function works fine for finding an organisation in the entire form, but
not people.

 

[Guest]

In report design view click on menu View - Grouping and Sorting.
Select the field you want the group on. Select Yes for a footer.
In the form put the field you are grouping on in the footer and create a
text box with =Count([YourFieldToBeCounted])

Unfortunately I wasn't able to follow your instructions. The main form is
based on a table, not a query. I tried creating a combo box in the form,
however the wizard didn't give me an option to type in the "*" as default or
the criteria that you described. It just asked which table to base the combo
box on, which fields to view and in what order. Can you shed any further
light?
Thank you.

Untested --

In the query for the main form create a ComboBox with the people table as
record source. Default the ComboBox to an "*". Look people but display
organization (column display controls this).
Have criteria for the main form for the organization field as --
Like [Forms]![YourMainForm]![YourComboBox]

I have set up a database using 2 tables (one with organisations and one with
people). I have set up a relationship between the 2 tables with a
many-to-one relationship, linking people to the relevant organisation. I
created a form based on the 'organisation' table, then created a subform
based on the 'people' table within this main form.
I would like the database users to be able to search the entire form for a
specific person, using the find function (binoculours icon on the toolbar).
At the moment, this function will only find people within the currently
selected organisation.
This function works fine for finding an organisation in the entire form, but
not people.

 

[Guest]

I will get back to you later - I responded to wrong post.

Unfortunately I wasn't able to follow your instructions. The main form is
based on a table, not a query. I tried creating a combo box in the form,
however the wizard didn't give me an option to type in the "*" as default or
the criteria that you described. It just asked which table to base the combo
box on, which fields to view and in what order. Can you shed any further
light?
Thank you.

Untested --

In the query for the main form create a ComboBox with the people table as
record source. Default the ComboBox to an "*". Look people but display
organization (column display controls this).
Have criteria for the main form for the organization field as --
Like [Forms]![YourMainForm]![YourComboBox]

I have set up a database using 2 tables (one with organisations and one with
people). I have set up a relationship between the 2 tables with a
many-to-one relationship, linking people to the relevant organisation. I
created a form based on the 'organisation' table, then created a subform
based on the 'people' table within this main form.
I would like the database users to be able to search the entire form for a
specific person, using the find function (binoculours icon on the toolbar).
At the moment, this function will only find people within the currently
selected organisation.
This function works fine for finding an organisation in the entire form, but
not people.

 

[Guest]

Open the form in design view. Click on the combobox then doubleclick and
select Properties at the bottom of the list. In Default type "*" and save the
form. In the OnChange property enter UpdateOrgQuery. Set Limit to List
property to No. Save.

Create a query in design view. Pull down the fields individually. In the
criteria for the Organization field put --
= Like [Forms]![YourMainForm]![YourComboBox] & "*"
Save the query.

Create a macro named UpdateOrgQuery. Set action as Requery.

Open the form in design view. Click on menu View - Properties. Change the
record source to the query. Save.

When the for is opened the query will pull all records. Making a selection
in the combobox will change the criteria for the query. You can go back to
full selection by entering an * again.

Unfortunately I wasn't able to follow your instructions. The main form is
based on a table, not a query. I tried creating a combo box in the form,
however the wizard didn't give me an option to type in the "*" as default or
the criteria that you described. It just asked which table to base the combo
box on, which fields to view and in what order. Can you shed any further
light?
Thank you.

Untested --

In the query for the main form create a ComboBox with the people table as
record source. Default the ComboBox to an "*". Look people but display
organization (column display controls this).
Have criteria for the main form for the organization field as --
Like [Forms]![YourMainForm]![YourComboBox]

I have set up a database using 2 tables (one with organisations and one with
people). I have set up a relationship between the 2 tables with a
many-to-one relationship, linking people to the relevant organisation. I
created a form based on the 'organisation' table, then created a subform
based on the 'people' table within this main form.
I would like the database users to be able to search the entire form for a
specific person, using the find function (binoculours icon on the toolbar).
At the moment, this function will only find people within the currently
selected organisation.
This function works fine for finding an organisation in the entire form, but
not people.