Run-time error 2342 - A RunSQL action requires an argument consisting of an SQL Statement (1 Viewer)

[Ashfaque]

My db contains a table called T_JobOffer where in 10s of job offer letter are available with diff data like departments, ProfessionName, ProfessionCode, Nationality etc.
I want to count the number of Expatriates worker and Local National Worker by Profession wize and Department wize. So we know which dept is lacking of national worker.

And save the generated summary in a table called T_Graph.

like below:

CDept	CNationalityExp	ProffGroupName	ProffGroupCode	Sum Of Count Of T_JobOffer
Administration	Expats	Engineer	1​	1​
Administration	Saudis	Engineer	1​	1​
Inspection	Expats	Engineer	1​	6​
Inspection	Saudis	Engineer	1​	1​
Trading	Expats	Engineer	1​	2​

To do this task, I have this code lines which is giving me Run-time error 2342 - A RunSQL action requires an argument consisting of an SQL Statement

Dim strSQL1, strSQL2 As String
strSQL1 = "SELECT DISTINCT T_JobOffer.CDept, T_JobOffer.CNationalityExp, Count(*) AS [Count Of T_JobOffer], T_JobOffer.ProffGroupName, T_JobOffer.ProffGroupCode " & _
"FROM T_JobOffer " & _
"GROUP BY T_JobOffer.CDept, T_JobOffer.CNationalityExp, T_JobOffer.ProffGroupName, T_JobOffer.ProffGroupCode, T_JobOffer.CNo, T_JobOffer.CLastWorkingDate, T_JobOffer.CCrNumber, T_JobOffer.CDept " & _
" HAVING ((Not (T_JobOffer.ProffGroupName) Is Null) AND ((T_JobOffer.ProffGroupCode)=1) AND ((T_JobOffer.CNo)>0) AND ((T_JobOffer.CLastWorkingDate) Is Null) AND ((T_JobOffer.CCrNumber)=2051223412) AND ((T_JobOffer.CDept)<> 'Operations' And (T_JobOffer.CDept)<> 'Top Management'));"
DoCmd.RunSQL strSQL1

strSQL2 = "UPDATE T_Graph " & _
"SET [CDept]= strSQL1.CDept, [CNationalityExp]=strSQL1.CNationalityExp, [ProffGroupName]=strSQL1.ProffGroupName, [ProffGroupCode]=strSQL1.ProffGroupCode"

DoCmd.RunSQL strSQL2

So please let me know if I doing right or there is any other way to get the summery of selected ProffGroupCode from the T_JobOffer table.

The T_Graph is tbl based on which I have placed graph. So each time I select different ProffGroupCode, the T_graph tbl previous data will be deleted and new data will be store to have updated graph.

Kindly support....

 

[theDBguy]

Do you really need to use a table? Can't you use a query for your graph?

 

[Ashfaque]

Thanks theDBguy,

In fact initially I tried using query to my graph but the ProffGroupCode ( Integer) changing on the form when I select different profession and my query is not supported to read the ProffGroupCode from form and make it as Criteria to generate data.

So I decided go for VBA code.

 

[theDBguy]

Thanks theDBguy,

In fact initially I tried using query to my graph but the ProffGroupCode ( Integer) changing on the form when I select different profession and my query is not supported to read the ProffGroupCode from form and make it as Criteria to generate data.

So I decided go for VBA code.

I don't see why you can't use a query. You should be able to use a form for the criteria in your query.

In any case, RunSQL only works with action queries. It doesn't work with SELECT queries.

 

[Ashfaque]

When I used query as recordsource of graph and open the form, it says
"The microsoft database engine doesnt recognize 'Form!F_SaudizatonPercent!TxtGroupCode' as valid field name or expression.

Where as when I directly runing query and giving parameter value...it is working perfectly and therefore I decided to save the data in T_Graph make it data source of the graph.

 

[theDBguy]

When I used query as recordsource of graph and open the form, it says
"The microsoft database engine doesnt recognize 'Form!F_SaudizatonPercent!TxtGroupCode' as valid field name or expression.

Where as when I directly runing query and giving parameter value...it is working perfectly and therefore I decided to save the data in T_Graph make it data source of the graph.

If you need help getting your query to work, consider posting a sample db with test data.

 

[Ashfaque]

Again Thanks theDBguy,

I have attached my db herewith with min records and objects help you to understand my requirement.

Just make first 5 check marks true one-by-one from top to bottom on the opening form and you will see 2 small graphs are appearing at the right side of the form. First circled graph is declaring overall number of category wise employees that based on the checkmarks checked unchecked.

I want the second clustered column graph too working with more details based on the checkmarks checked unchecked. Thats why I thought to generate required graph data in a table first and then make the table graph's record source.

Hope I clarified.