Double Click Open Form Filtered

[ServiceEnvoy]

I need to be able to create a button (or a double click function) to
open a main form and filter it to show only the main form records with
the subform records matching a particular ID #.

Main form: FRM_Tickets-Active (pulls data from the table
"TBL_Tickets" (filtered by a query to show only active tickets)
Sub form: SUB_EXP_Tickets (pulls data from the table
"TBL_Expenses" (linked to the main form by the field "WorkOrderNum"
Field in subform to filter on: VendorID

Can anyone help me figure out how to do this?

 

[Klatuu]

All you really need to do is use the Link Master Field(s) and Link Child
Fields(s) properties of your subform control. It will then automatically
show only the records in the subform that relate to the current record of the
main form. Link Master should contain the name of the key field in the main
form's recordset used to relate child records. In Link Child, you put the
name of the field in the subform's recordset that relates it to a record in
the main form's recordset.

 

[ServiceEnvoy]

Thank you for responding, unfortunately, I may not have been clear.
The main form already shows the correct information in the subform
based on the correctly linked child fields. What I need to do is
basically the reverse. I need to be able to filter the main form
based on the subform. How do I do that?

 

[Klatuu]

Not sure what you are trying to do. All the records in the subform belong to
the record in the main form, so any filtering would probably only return the
current main form record, unless, perhaps, there is a field in your subform
recordset that would bring up a different record.

Can you give a bit of detail, please?

 

[ServiceEnvoy]

Thank you for responding, unfortunately, I may not have been clear.
The main form already shows the correct information in the subform
based on the correctly linked child fields. What I need to do is
basically the reverse. I need to be able to filter the main form
based on the subform. How do I do that?

Anyone out there have an answer?

 

[Klatuu]

To get an answer, you really need to provide a good description of what you
want to do.

Since the records in the subform belong to the record in the main form, you
do you propose filtering the subform to get a different record in the main
form?

 

[ServiceEnvoy]

To get an answer, you really need to provide a good description of what you
want to do.

Since the records in the subform belong to the record in the main form, you
do you propose filtering the subform to get a different record in the main
form?

--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

Ok, what I need the user to be able to do is to be able to search &
display all the records of the main form to find all the main form
records that have a certain matching field in the subform. The main
form is the standard ticket information such as the customer name, the
problem type, etc. The subform has the information about all the
expenses associated with that call such as who is assigned to the call
(labor expense) or who we have bought parts from (parts expense) and
what has been bought. There are times we need to show all the main
form records that one tech has been assigned to or find all the main
form records that have a particular part number purchase.

 

[Klatuu]

I would suggest creating a search form that would allow the user to select
the field they want to search on with a combo bx. When a field is chosen
from the subform, set the row source of the combo to a query that will search
the main form recordset for the value in the chosen field, then, perhaps, set
the main form's filter based on the selection.