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

 

[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.