Button chabge reports based on a Combo

[McFestoe]

Is it possible to have a button linked to a combo box so if the combo box = A it prints report A or if the combox = B it prints repot B ans so on.

Iam up to this point at the moment but get stuck on OR and Loops

Private Sub Command109_Click()
Dim strWhere As String
strWhere = "[QuoteID]=" & Me.QuoteID
DoCmd.OpenReport "Quote", acViewPreview, , strWhere
End Sub



Any ideas.

 

[spizotfl]

i have a button on a form to open a report. i use a combo box to select between 2 reports right now. i used a wizard on the button at first, so i have the usual code, but i added this bit here to get the report i want:

If (cmbReport.Value = "A") Then
stDocName = "rptYouthProcessed"
Else
stDocName = "rptMonthlyDetail"
End If

it seems to do the trick for me. is this what you were looking for?

 

[HiBoo]

Your code is changing the filter of the report "Quote" and not the report name itself.

Is the value of the combo box a report name or a field value to be used as a filter in the report called "Quote"? If like you ask, the goal is to open one of a variety of reports (ReportA OR ReportB OR ReportC ...) you'd want to try this...

Private Sub Command109_Click()
    Dim strReport As String
    strReport = "[ReportName]='" & Me.ReportName & "'"
    strWhere = "[QuoteID]=" & Me.QuoteID

    DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

I'm assuming that me.ReportName returns the report object name.

 

[McFestoe]

Thanks Both for the reply,

spizotfl at the moment i have three reports and i need to run a report based on a value of a combo box.

HiBoo, The combo returns a feild value it is this that i want to run a different report for, like

Combo
Audible
Monitored
Update

So my print button would look at the above and run the report for, Audible, Monitored or Update.

Is that what you mean.

 

[sdk]

Hi

The original post suggested you wanted different reports, but the code indicates the same report for different records.

I use list boxes to show the different reports available to the user - very visible, where running the report by:

DoCmd.OpenReport me("lstReportNames"), acViewPreview

To add a parameter to the report to say, limit ouput to a particular quote from a combo, will read:

DoCmd.OpenReport me("lstReportNames"), acViewPreview,,"fieldQuote = ' & me("cmbQuote") & "'"

Note the names must reflect your database.

Cheers

Steve

 

[McFestoe]

I have tried this based on the above ideas but it keeps getting a error.

If (cmbSystemType.Value = "Audible") Then
stDocName = "rptAudible"
If (cmbSystemType.Value = "Monitored") Then
stDocName = "rptAudible"
If (cmbSystemType.Value = "Update") Then
stDocName = "rptUpdate"

End If


Still learning on IF statements.

 

[sdk]

Hi

- add a space and underscore after the last char on each if statement, ie:

if condition then _
oneLineArgument

- delete the "end if" statement

The underscore allows continuation to the next line for any command line.

Alternatively, use

If ... Then 
   ... 
ElseIf ... Then 
   ... 
End If

syntax:

If (cmbSystemType.Value = "Audible") Then
        stDocName = "rptAudible"
ElseIf (cmbSystemType.Value = "Monitored") Then
        stDocName = "rptAudible"
ElseIf (cmbSystemType.Value = "Update") Then
        stDocName = "rptUpdate"
end if

as only one line needs to be completed. However, I would not recommend (and advise against) hardcoding your report ruotine this way. Whenever new reports are added, someone must edit your code. The alternatives - using (dynamic) lists & x-referencing names - are better. Much better.

Cheers

Steve

 

[McFestoe]

Sdk

Thanks for the replie, i dont know what you mean by using dynamic lists & x-referencing names, could you explain a little more.

Thanks

Rich

 

[sdk]

Hi

You can build a list of report names and their descriptions that have been saved in the database, and use this in a list for the user to select from. It is pretty good, very dynamic/responsive, but difficult to program and does affect how you describe reports.

Alternatively, and my pref, is to include a list of reports in a seperate table, with report name (as it exists in the database) and a meaningful description. I use this to form the list for the user to select (in this case your source for the combo), and reference a hidden value as the report name. To add new reports to the combo, I add them to the table and everything else looks after itself.

So:
- You have a list table with the report object name and a description;
- the combo has two cols, and is bound to the col with the report name (eg rptAudible) and displays a meanginful description;
- the bound col is hidden (ie width = "")
- your code reads:
DoCmd.OpenReport cmbSystemTyp, acViewPreview

With this model, a new report won't necessitate changing your code. Please note that your where string must be addressed spearately (if required).

Lastly & FYI, I use a ValueList table in all my databases, and wherever practical use it for listing values used in combos etc. The table structure is:
ListName: of this list - eg Title/Months
Value: for reference value: eg Mr/November
AltValue: an alternative display: eg Mister/Nov
Description: to defne meaning of this item
Index: to order the list by for this ListName

Therefore if I want to update a list or find what values I am using, I "always" know where to look. The obv exception to this rule is where a combo/list is referenceing database records, eg Invoice Numbers.

Hope this helps

Cheers

Steve