Debugging a Select Case Statements (Still)

[dccjr]

I am still having problems debugging a the procedure below. I have tried setting a break point. My select-case is based on a combo box. How do I give the combo box a value OnChange in order for it to determine which items are suppose to be added to the next combo box? It only seems logical that the combo box needs a change to take place in order to execute the procedure, thus the Select portion.

Private Sub cboRptType_Change()
    Select Case cboRptType.Value
        Case "Pareto Chart"
           cboConstraint.AddItem "Event Type"
           cboConstraint.AddItem "Event Category"
           cboConstraint.AddItem "Work Area/Cell"
           'cboConValue.Enabled = False
           'txtStartDate.Enabled = False
        Case "RCA Event Report"
            cboConstraint.AddItem "Work Order #"
            cboConstraint.AddItem "Quality #"
            'cboConValue.Enabled = True
            'txtStartDate.Enabled = False
        Case "RCA Event Summary List"
            cboConstraint.AddItem "Specific Date to Present"
            cboConstraint.AddItem "Work Area/Cell"
            cboConstraint.AddItem "Event Type"
            cboConstraint.AddItem "Event Category"
    End Select
End Sub
Private Sub cboConstraint_Change()
    Dim strEventSQL As String
    Dim strSummarySQL As String
    Dim strParetoSQL As String
    Dim RstCount As Integer
    Dim dtmStart As Date
    Dim qdf As DAO.QueryDef
    
    Me.txtStartDate.Text = dtmStart
    strEventSQL , strSummarySQL, strParetoSQL, strConValue.RowSource = ""
      
    Select Case cboRptType.Value
        Case "RCA Event Summary List"
            MsgBox "This option generates a Summary Report based on non-exclusive criteria. All records meeting this non-exclusive criteria will be displayed."
            Select Case Me.cboConstraint.Value
                Case "Specific Date to Present"
                    Me.txtStartDate.Visible = True
                    Me.cboConValue.BackColor = RGB(255, 255, 0)
                    Me.cboConValue.Text = "Multiple Records Report"
                    Me.cboConValue.Locked = True
                    strSummarySQL = "SELECT (*) FROM RCAData1 WHERE RCAData1.StartDate > '" & Me.txtStartDate.Value & "' ORDER BY RCAData1.DefectDate DESC;"
                Case "Part Number"
                    strSummarySQL = "SELECT RCAData1.PartNo FROM RCAData1 ORDER BY RCAData1.DefectDate DESC;"
                    Me.cboConValue.RowSource = strSummarySQL
                    Me.cboConValue.Requery
                Case "Work Area/Cell"
                    strSummarySQL = "SELECT DISTINCT RCAData1.AreaCell FROM RCAData1 ORDER BY RCAData1.AreaCell DESC;"
                    Me.cboConValue.RowSource = strSummarySQL
                    Me.cboConValue.Requery
                Case "Event Type"
                    strSummarySQL = "SELECT DISTINCT RCAData1.Type FROM RCAData1 ORDER BY RCAData1.Type DESC;"
                    Me.cboConValue.RowSource = strSummarySQL
                    Me.cboConValue.Requery
                Case "Event Category"
                    strSummarySQL = "SELECT DISTINCT RCAData1.Category FROM RCAData1 ORDER BY RCAData1.Category DESC;"
                    Me.cboConValue.RowSource = strSummarySQL
                    Me.cboConValue.Requery
            End Select
        Case "RCA Event Report"
            Select Case Me.cboConstraint.Value
                Case "Work Number #"
                    strEventSQL = "SELECT RCAData1.WorkOrderNo FROM RCAData1 ORDER BY RCAData1.DefectDate DESC;"
                    Me.cboConValue.RowSource = strEventSQL
                    Me.cboConValue.Requery
                Case "Quality #"
                    strEventSQL = "SELECT RCAData1.QualityNo FROM RCAData1 ORDER BY RCAData1.DefectDate DESC;"
                    Me.cboConValue.RowSource = strConstraintSQL
                    Me.cboConValue.Requery
            End Select
        Case "Pareto Chart"
            Select Case Me.cboConstraint.Value
                Case "Work Area/Cell"
                    strParetoSQL = "SELECT RCAdata1.AreaCell, COUNT(*) as 'Number of Events' FROM RCAData1 GROUP BY RCAData1.AreaCell;"
                Case "Event Type"
                    strParetoSQL = "SELECT RCAdata1.EventType, COUNT(*) as 'Number of Events' FROM RCAData1 GROUP BY RCADAta1.EventType;"
                Case "Event Category"
                    strParetoSQL = "SELECT RCAdata1.Category, COUNT(*) as 'Number of Events' FROM RCAData1 GROUP BY RCAData1.Category;"
            End Select
    End Select
    
End Sub

I would really appreciate any advice, or maybe an explanation as to what is (and should be) going on. I feel that the further I get, the more I am in over my head.

[orange]

Please describe WHAT you are trying to do in plain English.

You might give us a sample with any values that are involved to show what you expect IN and OUT.

PS I really don't think you should be dealing with the ONChange event....

[June7]

I wouldn't use Change event but After_Update instead. With Change the code runs with each character typed in the combobox.

The Case "RCA Event Report" code in cboConstraint references the wrong variable:
Me.cboConValue.RowSource = strConstraintSQL

Also Case "Pareto Chart" code in cboConstraint does not set the RowSource property.

You can cut out some lines of code by not repeating under each Case:
Me.cboConValue.RowSource = strSummarySQL
Me.cboConValue.Requery

[pbaldy]

To add to what the others have said about the change event, I wouldn't use it either, as it fires with every keystroke. I'd use the after update event. If you really need the change event, you have to use the .Text property of the combo, as the .Value property will not yet be updated.

[dccjr]

I have corrected the observations made above, but still cannot get the cboConstraint to add the items form the Select Case in the procedure below, much less the cboConstraint_AfterUpdate procedure. Any other observations?

Private Sub cboRptType_AfterUpdate()
Me.cboConstraint.RowSource = ""
    Select Case Me.cboRptType.Value
        Case "Pareto Chart"
           Me.cboConstraint.AddItem "Event Type"
           Me.cboConstraint.AddItem "Event Category"
           Me.cboConstraint.AddItem "Work Area/Cell"
           'cboConValue.Enabled = False
           'txtStartDate.Enabled = False
        Case "RCA Event Report"
            Me.cboConstraint.AddItem "Work Order #"
            Me.cboConstraint.AddItem "Quality #"
            'cboConValue.Enabled = True
            'txtStartDate.Enabled = False
        Case "RCA Event Summary List"
            Me.cboConstraint.AddItem "Specific Date to Present"
            Me.cboConstraint.AddItem "Work Area/Cell"
            Me.cboConstraint.AddItem "Event Type"
            Me.cboConstraint.AddItem "Event Category"
    End Select
End Sub

I am lost.

[pbaldy]

I've never used AddItem, as I always use tables for the source, but what is the row source type of the combo?

[dccjr]

It does not have on currently. Should it be Value List or Field List (since it obviously isn't Table/Query). Likewise, should I set the others to Table/Query, even though I am not putting the SQL statement in there (as it is determined by the Select Case)?

[pbaldy]

I assume if you want to use AddItem it would have to be value list. What others?

[orange]

I'd like to go back to my post #2 and get a brief description in plain English of WHAT exactly you are trying to do.

I think you are using the wrong event for something, but I'm not clear of what you really are trying to do. Please help clarify the objective.

[June7]

If the code is setting the RowSource to an SQL statement then the Type is Table/Query. If the code is setting the RowSource to a series then the Type is ValueList. Just as the RowSource can be set by code, so can the Type.

[dccjr]

Here is what I am trying to do. When the user select a report type, the cboConstraint will populate with the appropriate options for that report type. The user will then select the Constraint type. This selection will then populate the cboConValue based on the appropriate SQL statement with data from the table. Basically each decision cascades to the next option. I guess I could use nested If statements instead of select-case, but I am not sure that it would make a difference.

As for the properties, I changed them to Value List with no change in inoperability. It is really perplexing to me. I also changed the Row Source for cboConValue to Table/Query. In so far as Properties are concerned, this is their current settings.

Limit to List YES
Allow Value List Edits NO
Inherit Value List YES
Show Only Row Source NO

I have tried various combinations of these (both the logical and the illogical).

[orange]

You're telling us how you've set something up.

It seems you want to run specific reports from a list, but you want to assign some constraints to the underlying sql or vba command. But it isn't clear which exactly.

Have you considered a form or forms to select some parameters and construct the "required codes" ?

Martin Green has some tutorials/samples for using intermediate forms and some vba and sql to set up Reports
at http://www.fontstuff.com/access/acctut19.htm

[dccjr]

Orange, I think that we are missing each other here. I have a user form for generating reports, which contains three combo boxes and one textbox. The first allows the user to select the type of report to generate (they have three choices: Pareto chart, Event Report, and Event Summary Report). Pareto chart may only be generated on three criteria (Event Type, Event Category, and Work Area/Cell). An SQL for each these populates the chart.

If Event Report (this is a report based on a single event) is selected, the cboConstraint is populated with the options for locating the event (work order #, Quality #, etc). This sets the SQL to get a list of all #'s in the table of the desired type. This list is suppose to populate the cboConValue. The user may then chose the desired #.

If Event Summary Report (non-exclusive) is selected, again the user is given the option of which field to make their choice from (part #, work area/cell, etc). This populates the cboConValue with that list, via SQL.

As you can see, the user has multiple options for how to create each report. Cascading combo boxes seemed the most user friendly way to do so. Does this help any?

[orange]

Yes, and the link I gave shows some examples.