Filtering through ComboBox and Option Group

[doddy88]

I have a form that would like to filter results in order to open a report.

On the form you will select a course from the combobox and then select the report you'd like to open from the option group and finally click a button to open the report.

The form is called 'Main', the option group is called 'Frame5' and the Combobox is called 'MyCategory'. At present I have two reports named rptStudents and rptStaff.

The On Click code for the button currently is:

Private Sub Open_Report_Click()

Dim stDocName As String
Dim stLinkCriteria As String

If [Frame5].Value = 1 Then
stDocName = "rptStudents"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
If [Frame5].Value = 2 Then
stDocName = "rptStaff"
DoCmd.OpenReport stDocName, acPreview
End If
End If
End Sub

When you click this it displays the appropriate report based on what option button I have selected but it doesn't filter by the course.

Any ideas? Do I need to add something to the recordsource on the query builder?

 

[mdlueck]

I have a form that would like to filter results in order to open a report.

I would suggest you bind your report to a temporary answer table verses the full main table.

Query the records you wish to display and place them into the temp table.

Then open the report (bound to the temp table) and you need not worry about further filtering... just allow the report to report on "SELECT *".

 

[doddy88]

I was using a query rather than the main table.

I have found the problem, a very silly and basic one!!

DoCmd.OpenForm stDocName, , , stLinkCriteria

Should be

DoCmd.OpenReport stDocName, , , stLinkCriteria

Thanks for your help!

 

[doddy88]

Hi All,

So yesterday I solved the problem of been able to use a combobox and an option group on a form to filter the results of a report by clicking a button.

I have now placed this form into a navigation form and it doesn't work as it displays [Forms]![ReportHub].[MyCourse] with the option to enter something.

So I believe for this to work I need to change the criteria in the query. The current criteria is:

[Forms]![ReportHub].[MyCourse]

The navigation form it needs to work on is [PlacementsDashboard]. Any solutions?

[ReportHub] is the current form that holds the combobox and option groups and [MyCourse] is the command button that takes you to the filtered report.

Regards

 

[boblarson]

Use the method I describe here to get the correct path:
http://www.btabdevelopment.com/ts/refer2sfrms
Navigation forms use a bit different reference. Not too much difference than subforms but a little.

 

[doddy88]

Thanks for your help there. I am unable to test it as I'm not at work, but would I be correct in thinking this is how it should look with [PlacementsDashboard] on the front:

=[PlacementsDashboard].[Forms]![ReportHub].[MyCourse]

Regards

 

[doddy88]

I have tried

=[PlacementsDashboard].[Forms]![ReportHub].[MyCourse] OR

=[PlacementsDashboard].[Form]![ReportHub].[MyCourse]

Neither can I get to work. Am I missing something?

 

[doddy88]

Issue resolved. I finally foundout how to find the subform control name.

Correct code:

[Forms]![PlacementsDashboard].[NavigationSubform].[Form].[MyCourse]

 

[doddy88]

Yet another problem!!!

I have a report hub which will be the central navigation for opening reports. So far on the report hub I have a combo box with a list of courses, I can select one of these and then select the appropriate report from the option group and select ok.

I now want to add another combobox on the same report hub but totally unrelated to the other combobox and option group. This combobox is linked to a lookup wizard field in a table. The combobox stores information on a health and safety form, with the options 'Sent(1)','Sent(2)' and 'Received'. When I make a selection in the combobox and then select ok it displays a blank report, if I select the report on the left hand side Access Objects it displays a parameter box and if I type 'Received' it brings the appropriate results.

Any ideas? My combobox is called 'HealthandSafety', my 'OK' button has the code:

Docmd.OpenReport "rptHealthandSafety", acViewPreview

In my query builder for the report, in the criteria section for the combobox field it displays:

[Forms]![PlacementsDashboard].[NavigationSubform].[Form].[HealthandSafety]

As the report hub is part of a navigation form called PlacementsDashboard.

It appears I can search using the parameters for the report, it seems the OK button isn't registering what has been selected in the combobox and thus I am getting a blank report.

 

[doddy88]

To add, combobox filtering reports seem to work when the combobox is taking data that has been typed in as a record. In the instance above, I have used a lookup wizard and entered my own values without selecting them from a field in another table. I'm not sure if this is where the problem stems from. Any help would be appreciated.