Problem with charts

[PrinceNai]

Dear Sirs,

I have a form with two charts on it. Those charts are based on two queries. When designing the form I made those two queries with explicit criteria (ID). It worked as expected, charts were drawn. Next thing I did was to add a combo box, where I pick a certain surname and this feeds 3 textboxes. txt_ID, txt_Name, txt_LastName. Then I changed the query criteria to [forms]![frm_Yearly]![txt_ID]. I get the message "The Microsoft Access database engine does not recognize '[forms]![frm_Yearly]![txt_ID]' as a valid field name or expression." when the form opens and both charts are blank. I click ok to that message and get " An error occured while sending data to the OLE server (the application used to create the object)." Queries execute ok if I fire them manually when txt_ID gets populated from the combo box. Any idea?

I'm using Access 2010 on the development machine, back end data is on the MSSQL server.

 

[June7]

I don't put parameter in query object, I do it in chart design.

Example from my chart RowSource:

SELECT M, D, FD, ZD, ZDM FROM GraphProctor WHERE LabNum=[tbxLabNum];

Will probably need code to refresh the chart after combobox selection. I use Me.Refresh.

 

[PrinceNai]

Thanks a lot for your answer, will try to do it. In the meantime I went the "dumb" route. Changed those two queries from select to maketable and based my charts on those new tables. Far from elegant, but it works

 

[Pat Hartman]

The make tables are a solution but they come with other issues. Access doesn't have good cleanup on certain operations and deleting and remaking tables is one of them so each time you run the chart, Access uses NEW space to create them and the old space is abandoned so the only way you can fix the problem is by running the compact utility.

The better solution is to figure out what is wrong with the queries. The obvious error is that the names are spelled incorrectly or the form reference is incorrect. Is the control you are referencing on a subform, if so, you need to include the mainform name in the reference string. Forms!mainformname.Form!subformname!controlname. In some situations a query needs to have arguments specifically defined so I would start with that.

 

[PrinceNai]

As I said, the solution is clumsy at best. There is nothing wrong with query names or form and control references. The reason I think so is that when I went from select to maketable query I only changed two things. Query name was qry_YearlyLoss and it now creates tbl_YearlyLoss. Nothing was changed on the form, nothing in the query criteria. The second thing I changed was the source data for the chart, so three letters. From qry to tbl. For two charts. I could've of course made a typo and have had my share of typing errors, but this isn't one of those things (Intellisense made sure of that. Correct form, correct control. No subforms.) . Wish it were. On the upside, those queries return maximum 30 records so new tables are very small and I have a piece of code that shuts down and compacts the application after 30min of inactivity.

 

[The_Doc_Man]

I have a piece of code that shuts down and compacts the application after 30min of inactivity.

Be VERY careful. Automatic Compact & Repair can cause problems. Things could happen that you don't really want to happen. And if the C&R fails, you have a corrupted DB. Which is why I always copy the DB before I start brain salad surgery on it.