Problem Opening Report from VBA

[dccjr]

I am trying to open a report and populate it from a cpmmand button. The user selects criteria, which is used to build the query dynamically. However when I press the report button, I am getting "The OpenReport action was cancelled." Below I have given the Sub that creates the SQL, the click Sub, and the Sub that populates the report.

Public Sub cboConValue_AfterUpdate()
strEventSQL = ""
Select Case Me.fraReport.Value
    Case 1
        Select Case Me.cboConstraint.Value
            Case "Work Order"
                strEventSQL = "SELECT * FROM RCAData1 WHERE RCAData.WorkOrderNo = '" & Me.cboConValue.Value & "';"
            Case "Quality"
                strEventSQL = "SELECT * FROM RCAData1 WHERE RCAData1.QualityNo = '" & Me.cboConValue.Value & "';"
        End Select
    Case 2
        Select Case Me.cboConstraint.Value
            Case "Event Type"
                strEventSQL = "SELECT * FROM RCAData1 WHERE Type = '" & Me.cboConValue.Value & "';"
            Case "Event Category"
                strEventSQL = "SELECT * FROM RCAData1 WHERE Category = '" & Me.cboConValue.Value & "';"
            Case "Work Area/Cell"
                strEventSQL = "SELECT * FROM RCAData1 WHERE AreaCell = '" & Me.cboConValue.Value & "';"
        End Select
End Select
End Sub

Public Sub cmdOpenReport_Click()
Select Case Me.fraReport.Value
    Case 1
        DoCmd.OpenReport "EventReportExisting", acViewReport
        DoCmd.Close acForm, "frmRptGen", acSaveNo
    Case 2
        DoCmd.OpenReport "RCASummaryReport", acViewReport, strEventSQL
        DoCmd.Close acForm, Me.Name, acSaveNo
    Case 3
        Call ExportRecordsetToExcel
    Case 4
        Call ExportRecordsetToExcel
End Select
End Sub

Private Sub Report_Open(ByVal strEventSQL)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strEventSQL)
With rs
        Me.rtxAreaCell = !AreaCell
        Me.rtxEmployee = !Employee
        Me.rtxDefectDate = !DefectDate
        Me.rtxPartNo = !PartNo
        Me.rtxDescription = !Description
        Me.rtxWONo = !WorkOrderNo
        Me.rtxQNNo = !QualityNo
        Me.rtxOtherID = !OtherID
        Me.rtxDisposition = !Disposition
        Me.rtxNotes = !Notes
        Me.rtxImmediateAction = !ImediateAction
        Me.rtxContainment = !Containment
        Me.rtxWhy1 = !Why1
        Me.rtxWhy2 = !Why2
        Me.rtxWhy3 = !Why3
        Me.rtxWhy4 = !Why4
        Me.rtxWhy5 = !Why5
        Me.rtxWhyAdditional = !WhyAdditional
        Me.rtxRootCause = !RootCause
        Me.rtxActionPlan = !ActionPlan
        Me.rtxActionTaken = !ActionTaken
        Me.rtxAssignedTo = !AssignedTo
        Me.rtxDueDate = !DueDate
        Me.rtxStatus = !Status
    End With
End Sub

I am not sure what I have done wrong. Anyone see something that I am missing?

[June7]

Cannot populate report textboxes with code. Bind the textboxes to fields of report RecordSource.

[dccjr]

I am not sure how to go about doing that. Can you give a line of code as an example?

[June7]

Haven't you already built reports that have bound textboxes? There is no code. Set the ControlSource property of textboxes in the Properties Sheet. Access Help has guidelines on building reports.

What you can do in code is set the ControlSource property. This is not the same as populating textbox with a value (setting the Value property). Example:

Me.textboxname.ControlSource = "fieldname"

The fieldname would be a field of the report's RecordSource and it would be included within quote marks. This is setting the ControlSource property, basically the same thing done manually on the Properties Sheet.

[dccjr]

Thank you very much. Much to my chagrin, I have not used this method. The few reports that I have created have been summary reports showing multiple records in a table-like format. You just opened up a whole new area for me, not to mentioned making the next reports much easier.

You don't know how much I appreciate your time, and the sharing of your knowledge. Thank you.