Queries based on a Select Case

[dccjr]

I have the following code for deciding which query to run to populate a combo box. I have tried plugging the SQL directly into the Row Source on the property sheet of the combo box and they work fine. When I try to do this in the procedure itself, it seems not to query at all (the combo box stays empty). Syntactically, everything seem to work fine.

Private Sub cboConValue_AfterUpdate()
strPatretoSQL As String
strEventReportSQL As String
strEventSummarySQL As String
strParetoSQL = ""
strEventReportSQL = ""
strEventSummary = ""
Me.cboConValue.RowSource = ""
Me.txtStartDate.Visible = False
Select Case Me.fraReport.Value
Case 1
Select Case Me.cboConstraint.Value
Case "Work Order #"
strEventReportSQL = "SELECT RCAData1.WorkOrderNo FROM RCAData1 ORDER BY RCAData1.DefectDate;"
Me.cboConValue.RowSource = strEventTypeSQL
DoCmd.strEventType.Requery
Case "Quality #"
strEventReportSQL = "SELECT RCAData1.QualityNo FROM RCAdata1 ORDER BY RCAData1.DefectDate;"
DoCmd.strEventType.Requery
End Select
Case 2
Select Case Me.cboConstraint.Value
Case "Event Type"
strSummaryReportSQL = "SELECT DISTINCT RCAData1.Type FROM RCAData1;"
DoCmd.OpenReport "RCASummaryReport", , , strSummaryReportSQL
DoCmd.Close acForm, Me.Name, acSaveNo
Case "Event Category"
strSummaryReportSQL = "SELECT DISTINCT RCAData1.Category FROM RCAData1;"
DoCmd.OpenReport "RCASummaryReport", , , strSummaryReportSQL
DoCmd.Close acForm, Me.Name, acSaveNo
Case "Work Area/Cell"
strSummaryReportSQL = "SELECT DISTINCT RCAData1.AreaCell FROM RCAData1;"
DoCmd.OpenReport "RCASummaryReport", , , strSummaryReportSQL
DoCmd.Close acForm, Me.Name, acSaveNo
End Select
Case 3
Select Case Me.cboConValue.Value
Case "Specific Date to Present"
Me.txtStartDate.Visible = True
strParetoSQL = ""
Case "Work Area/Cell"
strParetoSQL = ""
Case "Event Type"
strParetoSQL = ""
Case "Event Category"
strParetoSQL = ""
End Select
End Select
End Sub

I do know that the SQL statments for the Pareto chart are not in. I am not trying to use them at this time. I am not sure where I am going wrong. Suggestions?

[pbaldy]

Looking only at the first:


Case "Work Order #"
strEventReportSQL = "SELECT RCAData1.WorkOrderNo FROM RCAData1 ORDER BY RCAData1.DefectDate;"
Me.cboConValue.RowSource = strEventTypeSQL

See anything off?

[dccjr]

Thanks. I'll just hang my head in shame now.

[pbaldy]

We've all done it.