Best Way to Reference Subform Data

[alan_mitchell]

Hi,

I have a Form_1 which contians Subform_1.

Subform_1 appears in datasheet view and displays a the number of actions for
each staff member.

So the fields are:

Staff_Name (text)
Action_Count (number)

Now suppose Joe.Bloggs had 26 actions and Paul.Smith had 35.

If a user clicks on the cell which displays number 26, I want to run Query_1
which shows actions for Joe.Bloggs only.

If a user clicks on the cell which displays number 35, I want to run Query_1
which shows actions for Paul.Smith only.

I know how to use DoCmd.OpenQuery to run the query but am stuck how I would
use DoCmd.ApplyFilter to filter for each staff member.

I'm thinking it should be something like:

DoCmd.OpenQuery "Query_1", acViewNormal, acEdit
DoCmd.ApplyFilter , "[Staff_Name]= "the staff_name which corresponds to the
action_count the user clicked on" "

Any ideas how I could reference the staff_name which corresponds to the
action_count the user clicked on?

Cheers,
Alan

 

[Dale Fye]

Allen,

Personally, I frown on opening querys directly to users. It makes it too
easy to edit information inadvertantly. Instead of opening the query, base a
form on that query. Then you can lock the form, or set AllowEdits,
AllowAdditions, AllowDeletions all to false.

If you insist on opening the query, I think I would create another query
based on Query_1 so that it references the [Staff_Name] field of your form.
It would look something like:

SELECT * FROM [Query1] WHERE [Staff_Name] = '" &
Forms!yourForm.txt_Staff_Name & "'"

Then use the GotFocus event of your Action_Count textbox to open your query
using the docmd.OpenQuery method. You won't need to apply a filter, because
the query has the filter built in.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.