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

 

[pbaldy]

I wouldn't open a query, I'd open a form or report (most of us only let users see forms and reports). To open either, you could use this technique:

http://www.baldyweb.com/wherecondition.htm

 

[alan_mitchell]

Thanks Paul,

The reason I want to open a query is so I can export it to Excel. I don't believe this is possible with a report.

Thanks for the link, but my problem is the bit in bold:

DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

Where ControlName is the name of the control on the current form that contains the value to be shown on the second form.

How would I reference "Me.ControlName" in a subform?

Cheers,
Alan

 

[pbaldy]

The syntax on referring to subforms:

http://www.mvps.org/access/forms/frm0031.htm

but if you want to export the query, you'll probably have to add a criteria to it. Version previous to 2007 could export a report, 2007 can't.

 

[pbaldy]

You may also consider using the method this person is using:

http://www.access-programmers.co.uk/forums/showthread.php?t=162806

using a recordset restricted to the appropriate records.

 

[alan_mitchell]

I tried using the syntax on that link to reference Staff_Name, but keep getting syntax errors.

For the OnClick property of Action_Count in Subform_1 I have:

DoCmd.OpenQuery "Query_1", acViewNormal, acEdit
DoCmd.ApplyFilter , "[Staff_Name]= Forms!Form_1!Subform_1.Form!Staff_Name"
DoCmd.OutputTo acOutputQuery, "Query_1", acFormatXLS, , -1, , , acExportQualityPrint

Can you see what I'm doing wrong? I think I'm almost there so don't want to try a different method just yet.

Cheers,
Alan

 

[pbaldy]

Try this shot in the dark:

DoCmd.ApplyFilter , "[Staff_Name]= '" & Forms!Form_1!Subform_1.Form!Staff_Name & "'"

 

[alan_mitchell]

Thanks Paul - works a treat!