Filter a report based on two criteria in a navigation form

[rrbest]

I have a navigation form with various tabs on the top for topic areas. When a user clicks a topic they can browse the various reports contained in that table. A user browsing one topic would type a company ticker and have a drop down list for the available reports to view. When a user selects a date, the report is displayed within the navigation. I believe this is a simple task, but I could be wrong. I do not know how to link these fields so that the correct report is displayed.

In summary: A user selects a topic through the tabbed navigation and is presented with two fields, a combo box entry for "ticker" and a date drop down which displays the each record available for that ticker. When selecting a date, the report is displayed on the navigation form.

Thanks in advance.


EDIT:

I should add that I am currently producing the results by manually creating a report for each record. I simply copy one of my existing reports and change the filter so that it matches the ticker navigation button:

For example, topic = Quarterly Review. Ticker = ABC
(((QuarterlyReview.Ticker="ABC"))) And (QuarterlyReview.Quarter Is Not Null And QuarterlyReview.Quarter<>"")

As you can imagine, creating these reports manually is not ideal when the only change I make is the filter reference and provide a navigation button in the form to access it.

 

[rrbest]

I have successfully set up a query which properly filters the reports, so I have accomplished a big part of this question. One thing that remains.

When I enter the form (which is a navigation form that includes the report) it asks me to type in the ticker. Rather than have this window pop up asking for the ticker I would like to have the report field blank and the user type in the ticker in a combo box that includes a drop down. When the user changes this I would like the form to update immediately. Any thoughts? My navigation form uses the "Horizontal Tabs" template. I would like the report and the drop downs to be included in a particular tab.

 

[PNGBill]

Are you using "report" when you mean "form" ?

If you want a report to display all sorts of different data in different ways, sometimes it is best to use an Unbound Main Form and have your data display form as a subform.

This way you can have controls on your main form (unbound) that can do a lot with the subform you would not normally be able to do if it was just one form.

 

[rrbest]

I am using forms and reports. The forms use the navigation tabs and I can drop in actual reports that create additional tabs. When I click a tab it loads up the relevant report within the form.

I have set up a query that causes a pop-up when a tab is clicked asking for the ticker "Enter Parameter Value". This option does what I functionally want, only the combo box that it is associated to does not update the reports at all.

Can you think of a way to apply the filter when a user changes the combo box?

I have tried this code on an "On change" event procedure for my combo box.

Private Sub Combo278_Change()
 ' If the combo box is cleared, clear the form filter.
  If Nz(Me.Combo278.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
 
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo278.ListIndex <> -1 Then
    Me.Form.Filter = "[Ticker] = '" & _
                     Replace(Me.Combo278.Text, "'", "''") & "'"
    Me.FilterOn = True
 
  ' If a partial value is typed, filter for a partial company name match.
  Else
    Me.Form.Filter = "[Ticker] Like '*" & _
                     Replace(Me.Combo278.Text, "'", "''") & "*'"
    Me.FilterOn = True
  End If
 
  ' Move the cursor to the end of the combo box.
  Me.Combo278.SetFocus
  Me.Combo278.SelStart = Len(Me.Combo278.Text)
End Sub

It still pops up the box required parameters (which, when entered, produce the results I am looking for) But I believe it is suppose to just be automatic with no pop-up

 

[Pat Hartman]

1. Always give your controls a proper name - Combo278 doesn't cut it.
2. The Change event fires for EVERY keystroke so its usefulness is very limited and you would not use it for this purpose. Plus, you need to use the .text property to capture the keystroke before it gets to the control buffer. You can only use the .text property when the control has the focus which is why you're having to fiddle with focus in the code.
3. If you are using a combo to select a value, you have the entire value and so you want to use the "=" operator NOT the "Like" operator. LIKE prevents the use of indexes and so should be avoided unless it is actually necessary.

The following code is closer to what you need. Put it in the combo's AfterUpdate event or in the Click event of a button if you prefer.

If Me.cboTicker & "" = "" Then
    MsgBox "no item selected - some message", vbOKONLY
    Me.Filter = ""
    Me.FilterOn = False
    Exit Sub
Else
    Me.Filter = "Ticker = '" & Me.cboTicker & "'"
    Me.FilterOn = True
End If