Report with a drop down. 1

[loriek]

Is it possible for a report to open with a drop down such as "Towns?" and then the user selects the correct town and the report is then produced based up on that?

 

[dhookom]

Possibly in A2007 but not in any earlier version.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

By playing around with formatting settings you could probably make a form that looks "Reportish".

 

[tigersbh]

You'll have to use a form that calls the report and put the dropdown on the form. The form would open the report, sending it a filter based on the dropdown value.

 

[loriek]

tigersbh.; I don't know how to do that... is there a white paper, perhaps, that you've seen?

 

[dhookom]

Loriek,
-Create a form and add a combo box named cboTowns that has the towns as its Row Source.

-Then use the command button wizard to create a button that opens your report.

-After the button has been created, right-click the button and choose "Build Event..."

-This will open the code window with something like:

Private Sub cmdRptCustLabels_Click()
On Error GoTo Err_cmdRptCustLabels_Click

    Dim stDocName As String
[blue]    Dim strWhere As String  'added[/blue]
    
    stDocName = "YourReportName"
[blue]    If Not IsNull(Me.cboTown) Then
        strWhere = "[Town]='" & Me.cboTown & "'"
    End If[/blue]
    DoCmd.OpenReport stDocName, acPreview[blue], , strWhere[/blue]

Exit_cmdRptCustLabels_Click:
    Exit Sub

Err_cmdRptCustLabels_Click:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume Exit_cmdRptCustLabels_Click
    
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[loriek]

I ran the code hookum said and it returned an empty report.

So I added a message box prompt in the code below and it's telling me the street address in the where clause is: StreetName = '70625726'

Thoughts??


Dim stDocName As String

'Adding this
Dim strWhere As String

If Not IsNull(Me.Combo4) Then
strWhere = "StreetName='" & TheStreet & "'"


'This is the crazy message box with the wrong address
MsgBox strWhere
End If

stDocName = "Showings"
DoCmd.OpenReport stDocName, acPreview, , strWhere

 

[dhookom]

What is the Row Source SQL view of Combo4? Does your report have a field that stores values like '70625726'? If so, is the field numeric or text?

Duane MS Access MVP
Now help me support United Cerebral Palsy