Filter Button (Access 2003)

[robinr]

I have a form with 2 combo boxes to select records on a main form showing the company, then I have 2 subforms - one showing the Offices and the other showing the contacts.
I created a command button to filter and show the current selections. My problem is: the focus stays on the 2nd combo box and I would like it to move to the Company Name. Then if they click the Filter button I would like it to filter by the Company name - without having to click on the filter by selection button - it then opens a Report that they can print the info from that 1 company. Now when I close the report I would like the filter to be turned off instead of having to click on the Remove Filter button.
Thanks in advance.

[HansV]

Can you try to explain this again, step by step? I am completely confused by this, there may be two or three different questions here, but I'm not sure...

[robinr]

I have one form with 2 combo boxes that I can select the Company Type then select a company that belongs to that type. It displays the information for the Company on the main part of the form and in 1 subform, the offices that belong to that company and in another subform the contacts that belong to the office that you click on. I then created a filter button that will display a report . But first you have to click into the company name control on the form, then click the Filter by Selection button on the toolbar, then click my command button that previews the filtered information (Company, Offices and Contacts) of the filter by selection. When you close the report it returns to the form but then you have to click the remove filter button on the toolbar. I would like the command button I created to: Focus on the Company Name that is selected (move the forcus to it), do a Filter by Selection automatically and open the report; when you close the report to automatically remove the filter from the main Company form.

Hopefully I have explained it a little better. Thanks Hans

[HansV]

The filter by selection shouldn't be necessary at all, but it is not clear to me how the report picks up the filter from the form. Can you explain that?

[robinr]

Hi Hans - Sorry I was out for the evening. Hope you still are following this.
I set up a command button with an On Click procedure as follows:

Private Sub Command38_Click()
On Error GoTo errhandler

Dim fil As Form
Dim stDocName As String

stDocName = "Alphabetical Contact List" '**The report you wish to preview**
Set fil = Forms![Frm_Company] '**The form you have added the button to**


If iFilterType = acApplyFilter Then
DoCmd.OpenReport stDocName, acPreview, , fil.Filter '**If a filter has been set on the form then apply it to the report**

Else
DoCmd.OpenReport stDocName, acPreview '**If there is no filter then just generate the normal report**
End If
errhandler:
End Sub

[HansV]

Try this. You must replace Company Name by the actual name of the company name field:
<pre>Private Sub Command38_Click()
Dim stDocName As String, stWhereCondition As String

On Error GoTo ErrHandler

stDocName = "Alphabetical Contact List" '**The report you wish to preview**
stWhereCondition = "[Company Name] = " & Chr(34) & Me.[Company Name] & Chr(34)
DoCmd.OpenReport stDocName, acPreview, , stWhereCondition

ErrHandler:
End Sub
</pre>

If you have an ID field that uniquely identifies a company, it would be better to use that instead of the company name. Assuming it is named CompanyID and that it is a number field, the line strWhereCondition = ... in the code above would become
<pre> stWhereCondition = "[CompanyID] = " & Me.[CompanyID]
</pre>

The report will display data for the current company only, no need to set and then remove a filter on the form.

[robinr]

Thanks so much Hans - worked great.