populate chart category from a combobox

[RipIT]

Hello
I’m using VB 6 as a front end to Access 03 database to populate the MSChart control.
I have a combobox for defect categories. I would like to populate the chart with a single category selected from the combobox.

Not sure what to put in place of the * as shown in the statement below. I would like to display the date (Weeks) and the category with the number of defects.

Private Sub CategoryView()
        
    '  Display the category of chart data
    Set cmd.ActiveConnection = cn
    cmd.CommandText = "SELECT * FROM tbliface WHERE [Weeks] = '" & cboCategory.Text & "'" _
    & " ORDER BY [Weeks]"

thanks for any help
Steve

[Phil Weber]

What are the columns in your database? It's impossible for us to guess. ;-)

[Hack]

Exactly.

First, there is seldom, if ever, a reason to do a SELECT * - the field names that you want should always be listed out, in my opinion.

And, the field name that holds your categories is what you are after.

[RipIT]

Yes, sorry for the confusion. The fields are...
Weeks Analyzer Humphrey Accumulator Pump Regulator Card NDF Solenoid.

This case definitely don't want all the fields returned. I guess I get lazy and use * when I want to cut down on the code.

Is this even in the ballpark?...

cmd.CommandText = "SELECT '" & cboCategory.Text & "' FROM tbliface WHERE [Weeks] = '" & cboCategory.Text & "'" _
    & " ORDER BY [Weeks]"

I want to return only two fields keying on the selection from the cboCategory combobox. The date field and the category chosen. Currently this isn't returning anything.

Steve

[Hack]

What is in cboCategory?

When you actually run the code, what does the query look like?

[RipIT]

These are in the drop down...
Analyzer Humphrey Accumulator Pump Regulator Card NDF Solenoid

When I set a breakpoint just after the cmd.CommandText statement and run this I cursor over '" & cboCategory.Text & "' and get the choice made from the combobox. But nothing shows in Weeks when cursored over.

I think (hope) I'm on the right track.

Steve

[Hack]

So, give me a "fer instance".....what would the actual query going back to the database look like?

[Hack]

And, just in case I will want to take a look at what you currently have, in addition to answering my last question, you might as well zip up what you currently have and attach it.

I may not need it, but, if I do, it will save time.

[RipIT]

I changed the query to...

cmd.CommandText = "SELECT '" & cboCategory.Text & "' FROM tbliface & " ORDER BY [Weeks]"

This seems to make more sense since I want to return all selected defects from category and return ordered by Weeks.

the rs.recordset shows it finds 11 records which sounds about right but it errors with my error handler routine message.

Enclosed is the attachment.

[Hack]

Ok, I looked back through this thread and if you posted it already, I couldn't find it.

Errors are good (sometimes )....they provide a starting point. What is the error that you are getting?

[RipIT]

the error is...
"You must choose a valid category" This is my error handler.
Then I get a blank chart

[Hack]

Remove your error trapping so that it will generate a runtime error, and tell me what that error is.

(BTW: Never add error handling until you are pretty sure there are no more errors. I know that sounds strange, but it is a general rule of thumb. While developing, you want errors to occur, in their natural habitate, so you can fix them. Once you have all of the errors, that you know about fixed, then add your error handling to deal with the errors you don't know about.)

[RipIT]

Sounds like good advice to me..
There is no error without the err handler just a blank chart control.
So maybe I'm not displaying it correctly.

On my way home I'll check this further in a while
Thanks for the help!

[Hack]

Let me know what you find and where you are with this.

[Phil Weber]

I wouldn't think that a field named Weeks would contain category names. Try something like:

"SELECT '" & cboCategory.Text & "' FROM tbliface WHERE [Weeks] BETWEEN #2/1/2008# AND #2/29/2008#"

Assuming that Weeks contains dates, that will return all the selected category values for the month of February.