I'm new to access. I would like to draw a chart in a form. the chart should represent data from different projects. each project has its own ID.
My query is:
SELECT [Time_data].[ID], [WeekConv].[F2] AS week, [Time_data].[discipline], Sum([Time_data].[MHrs]) AS Mhrs
FROM Time_data, WeekConv
WHERE ((([Time_data].[week])=[WeekConv].[F1]) And (([Time_data].[ID])=[Forms]![frmAnalysis]![cboProject]))
GROUP BY [Time_data].[ID], [WeekConv].[F2], [Time_data].[discipline];
The query works but when i try to draw the chart i get the message
"The Microsoft Jet Engine doesn't recognize [Forms]![frmAnalysis]![cboProject] as a valid field name or expression.
(the form where i try to draw the chart is a diffrent one from the one where the combo box is situated if that is of any importance??)
I have also thought about storing the project in a global variable called SelectedProject via VBA and using something like
WHERE ((([Time_data].[week])=[WeekConv].[F1]) And (([Time_data].[ID])=[SelectedProject])). Can this be done?
Well, I admit I have no idea really what is causing the problem, but you could try a few things that may or may not help...
* Does the chart show OK in a report rather than a form?
* Maybe try referencing the combo as [Forms]![frmAnalysis]![cboProject].column(x)
where x is the column that contains the bound column - shoudn't make any difference but Access is a bit funny at times...
* Is your SQL string in a saved query or just set as the recordsource direct? Maybe try the other way round?
* Maybe try drawing the chart on the same form to see if this works. Of course it shouldn't make any difference but you never know
* Try hard coding the TimeData.ID field in the where clause, ie setting it = 1 or something in the SQL string. If everything works nicely then at least you know it's the combo that is the root of the problem
* If the last point is true then try a different combo, or just putting a value into a text box instead to see if you can get that working
As you can see I'm clutching at straws but you never know... Good luck
i found a way around the problem.
instead of writing [Forms]![frmAnalysis]![cboProject] in the query i created a function called Project() and put this one in the query instead.
Project() is defined in the module "global" as:
Function Project() As String
Project = [Forms]![frmAnalysis]![cboProject]