Open Report Code Error 1

[Emblem1]

Hi.

I have 2 combo boxes. One is a type select, and the select is a location select. There is also a open report button.

The 2nd combobox and the report button dynamically change based on the value of the first combobox. The dynamic rowsource works, but I am getting a syntax error with the open report command. Can anyone see what I need to change? I think my problem is my strField syntax. Here is the whole sub.

Private Sub OpenWattReport_Click()
On Error GoTo Err_OpenWattReport_Click


If Trim(Me!cboLocation & "") = "" Then
MsgBox "Please select a location.", vbOKOnly + vbInformation
Exit Sub

Else


Dim strReport As String 'Name of report to open.
Dim strField As String 'Record to generate report for

If Me!cboLocationType = "Signalized Intersection" Then
'Sets rowsource for cboLocation
Me!cboLocation.RowSource = "Intersection Data Query"
strReport = "rptWattReportwithDataNoType"
'Defines the location of the name used
strField = "[tblIntersectionWattage]![Intersection Name]"
Else
Me!cboLocation.RowSource = "qrySigns&FlashersNameFiltered"
'Sets rowsource for cboLocation
strReport = "rptWattReportSigns&Flashers"
'Defines the location of the name used
strField = "[tblSignWattage]![Location Name]"
End If

DoCmd.OpenReport strReport, acPreview, , "strField = & Me.cboLocation & " '"
End If



Exit_OpenWattReport_Click:
Exit Sub

Err_OpenWattReport_Click:
MsgBox Err.Description
Resume Exit_OpenWattReport_Click

End Sub




Thanks!

 

[Emblem1]

Oops. Typo. Should say "One is a type select, and the second is a location select.

 

[dhookom]

I don't know what value you expect to get from "cboLocation" since you are just setting its row source prior to opening the report. It probably won't have a value. Also, I assume your fields are text values. You might want to remove the table name from the strField variable.

At a minumum, you should use

   Dim strWhere as String
   strWhere = strField & " =""" & Me.cboLocation & """"
   MsgBox "This is your WHERE CONDITION: " & strWhere
   DoCmd.OpenReport strReport, acPreview, , strWhere

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]

 

[Emblem1]

Hi, thanks for the reply.

I have 2 different tables I am accessing. The Location type dtermines which one. I have 2 different reports, one for each table.

cboLocation is a text field that the report uses to determine which record to display. There are 2 diff tables. Hence, I have 2 diff. queries that drive the rowsource for the 2nd combo box, and a diff. report for each table.

Do i need to specify which table to use based on the value of cboLocationType in another way?


Here is how I have my code now.


If Trim(Me!cboLocation & "") = "" Then
MsgBox "Please select a location.", vbOKOnly + vbInformation
Exit Sub

Else


Dim strReport As String 'Name of report to open.
Dim strField As String 'Record to generate report for
Dim strWhere As String

strWhere = strField & " =""" & Me.cboLocation & """"

If Me!cboLocationType = "Signalized Intersection" Then
'Sets rowsource for cboLocation
Me!cboLocation.RowSource = "Intersection Data Query"
'Sets the correct report to use
strReport = "rptWattReportwithDataNoType"
'Defines the location of the name used
strField = "[tblIntersectionWattage]![Intersection Name]"
Else
'Sets the othher rowsource for cboLocation
Me!cboLocation.RowSource = "qrySigns&FlashersNameFiltered"
'Sets the correct report to use
strReport = "rptWattReportSigns&Flashers"
'Defines the location of the name used
strField = "[tblSignWattage]![Location Name]"
End If

DoCmd.OpenReport strReport, acPreview, , strWhere
End If

I get a syntax error if I have cboLocationType = "Signalized Intersection" .

However, I get a request for a parameter value to run the report if cboLocationType is otherwise.

Thanks.

 

[dhookom]

You haven't selected any value in the cboLocation prior to opening your report. What do you see in the MsgBox I suggested you add to your code prior to the OpenReport?

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]

 

[Emblem1]

It gives me the text that I select in cboLocation.

 

[Emblem1]

Most of my 'locations' are text such as 'something & something', or 'something @ something'.

 

[dhookom]

This code

    Dim strWhere As String
    strWhere = strField & " =""" & Me.cboLocation & """"

must come after you have set the value for strField, not before. strField has no value until after your "If...End If" code.

Please try again and remove the table name from strField so you have something like

   strField = "[Location Name]"

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]

 

[Emblem1]

It works!

Your help is appreciated.

Here is the final code.

If Trim(Me!cboLocation & "") = "" Then
MsgBox "Please select a location.", vbOKOnly + vbInformation
Exit Sub

Else

Dim strReport As String 'Name of report to open.
Dim strField As String 'Record to generate report for


If Me!cboLocationType = "Signalized Intersection" Then
'Sets rowsource for cboLocation
Me!cboLocation.RowSource = "Intersection Data Query"
'Sets the correct report to use
strReport = "rptWattReportwithDataNoTypefor_cboLocation"
strField = "[Intersection Name]"
Else
'Sets the other rowsource for cboLocation
Me!cboLocation.RowSource = "qrySigns&FlashersNameFiltered"
'Sets the correct report to use
strReport = "rptWattReportSigns&Flashers"
strField = "[Location Name]"
End If

Dim strWhere As String
strWhere = strField & " =""" & Me.cboLocation & """"
DoCmd.OpenReport strReport, acPreview, , strWhere
End If