Filtering using drop down list

[Sandy Nuts]

I have a drop down list which I'd like to use to filter the records within
the form. The drop down list contains a list of managers who are responsible
for certain buildings.

I'd like to select a managers name, then the records shown within the form
are those which are related to said manager.

I have a table called tblProperty which contains the building names. There
is also a table called tblManagers for each manager. tblProperty has a field
ManagerID, as does tblManagers. I have setup a relationship between the two
ManagerID fields. Correct?

What I'd like now, is to select the manager and perform a query which will
display the records related to this manager.

I'm thinking that some form of event code is needed for After Update?

Any hints or tips would be much appreciated.

 

[Jeff Boyce]

I'm envisioning a form with an unbound combobox in the header. This
combobox is based on a query of the Manager table, and returns the ManagerID
and ManagerName. The form itself is based on a query of the Property table,
and includes a Criterion under the ManagerID field (in the Property table)
of something like:
Forms!YourFormName!cboYourManagerComboboxName

In the combobox's AfterUpdate event, you could use:
Me.Requery

This would have the form open without records (because no Manager has been
selected). Once a Manager is selection, the form re-queries the Property
table, looking for properties with a Manager matching the one selected.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Sandy Nuts]

I'm envisioning a form with an unbound combobox in the header. This
combobox is based on a query of the Manager table, and returns the
ManagerID
and ManagerName. The form itself is based on a query of the Property
table,
and includes a Criterion under the ManagerID field (in the Property table)
of something like:
Forms!YourFormName!cboYourManagerComboboxName

In the combobox's AfterUpdate event, you could use:
Me.Requery

This would have the form open without records (because no Manager has been
selected). Once a Manager is selection, the form re-queries the Property
table, looking for properties with a Manager matching the one selected.

Hi Jeff,

Thanks for the response. Half way there. I really am an Access newbie (more
a PHP / MySQL fan) and have inherited this database from a colleague. At the
top of the window there is two bars; Form Header and Detail. Everything in
the form is contained under the Detail bar, with the Form Header one just
sitting directly above Details.

When the form is opened, the entire recordset is loaded and you can scroll
through the records via the record navigation buttons at the bottom. I
suspect this isn't the done method?

 

[Jeff Boyce]

It sounds like the form is bound to the entire recordset, rather than, as it
sounded like you want, bound to a single record. The description I provided
was a suggestion of how it could be.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Sandy Nuts]

It sounds like the form is bound to the entire recordset, rather than, as
it
sounded like you want, bound to a single record. The description I
provided
was a suggestion of how it could be.

Yup. Thanks for that. Have messed about and got the filtering working great.
Thanks for your help.

As a side note, I have an 'embedded form', if you wish, which allows images
to be saved against said property. This doesn't appear to remember the image
when you exit the record and go back to it, but when you view
frmPropertyImages, there is a list of images I've tried to save. Odd?