Using SQL as a control source.

[Petemush]

Correct me if I'm wrong but it is possible to use SQL as a control source isn't it?

I try to use,

SELECT Count(tblEmployeeCourses.[Employee No]) As [RecNumPeep]
FROM tblEmployeeCourses INNER JOIN tblCourseVariables ON tblEmployeeCourses.[Course Code] = tblCourseVariables.CCode
WHERE ((tblEmployeeCourses.[Course Date]) = ([Forms]![frmCourseVariables]![CoDate]));

but I get an error about Access doesn't recognise a field name, yet these are all the correct field names, I've doublechecked.

Anyone know of another reason for this error?

Cheers,

Pete

 

[FoxProProgrammer]

You can't use an SQL statement as the Control Source. You can use an SQL statement as the Recordsource. Which one did you intend? The Control Source can be a field that is returned by a query, but it can't be the query itself. dz
dzaccess@yahoo.com

 

[Rolliee]

set your SQL by using a string

SQLstr = "SELECT ..."

Then use MsgBox to show what the computer 'sees.' Show us what the computer sees. There is a diff between what we see as a variable and what the computer sees at runtime.

In the SQL me. field is not the same as ..." & me.field & "...

rollie@bwsys.net

 

[Petemush]

Sorry, I was in a rush yesterday and didn't bother checking past threads, like the numerous ones concerning NOT using SQL as a control source.

The one idea I gleaned from the other threads was to use a function which carried out the sql, stored the result in a variable and return that.

Having trouble doing that though, DoCmd.RunSQL doesn't seem to want to be stored as a variable and I can't think how to do it. Any ideas?

Cheers,

Pete

 

[valtersp]

The other solution is to create query while in run-time and use it how and when needed (as a ControlSource).
You must specify QueryName and SQL string.

Dim dbs, qdf, strSQL
set dbs=CurrentDb
set qdf=dbs.querydefs

for each qdf in dbs.querydefs
if qdf.name="QueryName" then
dbs.querydefs.delete (qdf.name)
next

set qdf=dbs.createquerydef ("QueryName", strSQL)

After you no longer need the "freshly made" query, you can simply delete it.

 

[Petemush]

Okay, this is what I'm currently doing:

Opening a recordset based on a table,

filtering the recordset with the WHERE condition from above.

Setting another recordset equal to the filtered recordset.

Returning the recordcount of the second recordset.

Can't get it to work though! Arrrrggghh!

Cheers,

Pete

 

[Rolliee]

Pete,

Send your code or even email me your mdb and I will have a look.

rollie@bwsys.net