How to OpenRecordset(queryName refering to form)

[Fjordur]

Hi,
I have the following code
Public Function f(strSql As String, ...
...
r= CurrentDb.OpenRecordset(strSql)
I call f with the name of queries as arguments.

Calling function f with the name of a query works only when the query
doesn't include a where clause referring to an open form
(

.field=[Forms]![form]![control]).
Is there a way I can refer to the form in the query, so that I can use my
function f with only the name of the query as an argument? BTW some of the
queries have such a where clause, others don't.
This is rather crucial to the project, and I hope there's a way I can
circumvent this (hopefully a shortcoming of mine regarding SQL-VB syntax)
Thanx, all

 

[Nikos Yannacopoulos]

Though you are not painting the whole picture here, I suspect the
problem is in the creation of the strSQL string, wherever you do that
(the code that calls the function).
That code, should pass on to the function the *current values* of the
form controls in the strSQL string, instead of references to the form
controls, which I suspect is what you are trying to do.
Please post that part of your code, so I (or someone else) can provide
more specific feedback. To that end, it will also help if you specify
the data type of the fields to which each referenced control is bound
(i.e. text vs. numeric vs. date etc).

HTH,
Nikos

 

[Fjordur]

Though you are not painting the whole picture here, I suspect the
problem is in the creation of the strSQL string, wherever you do that
(the code that calls the function).

I don't create the query by code. I call a plain Access query .

That code, should pass on to the function the *current values* of the
form controls in the strSQL string, instead of references to the form
controls, which I suspect is what you are trying to do.

Right, that's what I'm trying to do.

Please post that part of your code, so I (or someone else) can provide
more specific feedback. To that end, it will also help if you specify
the data type of the fields to which each referenced control is bound
(i.e. text vs. numeric vs. date etc).

Please note that I need this function to work with any query. Therefore I
can't imagine it's a problem of data type.

OK, more about this: I have several forms that have a button "Export". The
data to be written to file is different for each form. For each form I build
a specific query to extract the data that will be written to file. Some of
these queries refer to a form control, others don't. I want to pass the name
of a query to my general purpose function f that will do the actual file
writing.

The 1st query I wrote for a form called "Mission" is called "debutMission"
and it goes like this
SELECT [O-Periodes].RefPeriode AS [fdfd fd], [O-Periodes].Au,
[O-Periodes].ClausesParticulieres,
(...) many more fields, about 30, dates, strings, numbers
WHERE
((([O-Periodes].IDAffectation)=[Forms]![Mission]![ID_Affectation]));
and it works perfectly when run by hand with form "Mission" open.
Code goes:When I call f("debutMission") above the code goes on error on the
OpenRecordset statement.
However, it works if the query does not refer to the form in its WHERE
clause.

 

[Nikos Yannacopoulos]

OK, so argument strSql receives the name of a saved query. When the
query has reference(s) to a form, is the form open at the time you call
the function? What is the exact error message you get? Also, could you
post the code behind the command button, which calls the function?

Nikos

 

[Fjordur]

Thanks for your help, Nikos

OK, so argument strSql receives the name of a saved query. Right

When the query has reference(s) to a form, is the form
open at the time you call the function?

Yes. Other form(s) may be open behind the form.

Also, could you post the code behind the command button,
which calls the function?

OnClick: =f("debutMission") in the properties of the btn

What is the exact error message you get?

function f code enters correctly with strSql = "debutMission" (cheked this
in debug mode) and goes
On Error GoTo exitA
Set r= CurrentDb.OpenRecordset(strSql)
it jumps straight from there to
exitA:
Debug.Print Err.Number
which prints 3061

I just tried again to trace the same code with the modified query, WHERE
clause removed, and this runs smoothly throught the OpenRecordset. Same with
WHERE ((([O-Periodes].IDAffectation)=18));
I take it the reference to the form is a problem.

 

[Nikos Yannacopoulos]

Error 3061 is "too few parameters". Regrettably, you can't open
parameter queries as recordsets - I admit I didn't know this, I'd never
tried it. The workaround is to use the command button's Click event to
run code which will construct the strSQL string, and pass it on a a
parameter to the f function, instead of the query's name. Suppose the
saved query's SQL is:

SELECT Field1, Field2 FROM Table1
WHERE Field3 = Forms![Form1]![Control1]
AND Field4 = Forms![Form1]![Control2]
(where Field3 is numeric and Field4 is text)

Then, the code in the command button's Click event should be something like:

strSQL = "SELECT Field1, Field2 FROM Table1 WHERE " _
& "Field3 = " & Forms![Form1]![Control1] " AND " _
& "Field4 = '" & Forms![Form1]![Control2] & "'"
Debug.Print strSQL
f(strSQL,...)

Notice the difference in the syntax around text vs. numeric fields, and
make sure you don't omit the spaces! The Debug.Print is just so you
confirm the SQL string is correct, you can comment it out or delete it
when you get it right.

I know this means quite some work if you have many different
forms/queries, but I'm afraid I can't think of an easier way.

HTH,
Nikos

 

[Fjordur]

Error 3061 is "too few parameters". Regrettably, you can't open
parameter queries as recordsets - I admit I didn't know this, I'd never
tried it. The workaround is to use the command button's Click event to
run code which will construct the strSQL string, and pass it on a a
parameter to the f function, instead of the query's name.

Bad news indeed.

As the queries are rather long, I'd rather not have to maintain them in VB
code.
Is there a way I can get, from VB, the SQL from a query? My idea is to
- write a query with a constant parameter so that the query itself is
written and maintained in Access,
- pass the function f both the name of the query and the ID which is the
WHERE criteria
- in f code, get the SQL code from the query, substitue the ID for the fake
constant parameter
- and runOpenRecordset.
?

Thanks anyway for your help

 

[Nikos Yannacopoulos]

CurrentDb.QueryDefs("QueryName").SQL

will return the SQL expression of the query.

Now here's an idea: suppose you remove all the filter references to
forms from the saved queries, so in essence they just join tables and
return all records. In addition, you create a table with two text
fields, one storingeach form's name (the forms with command buttons for
export), and a second one storing the WHERE clause for each, as VBA
would need it. The idea is to pass two arguments to the f Function,
which will construct the SQL expression locally based on query name and
calling form name. Further to my previous example, suppose the saved
query Query1 is:

SELECT Field1, Field2 FROM Table1

The new table, say tblQueryFilters has two fields, FormName and
WhereClause; for the particular combination, the values would be:

FormName: Form1 (or whatever)
WhereClause: " WHERE Field3 = " & Forms![Form1]![Control1]
& " AND Field4 = '" & Forms![Form1]![Control2] & "'"
(the expression should be in a single line but the newsreader will wrap it).

That way, you call your function as:
f("Query1", [Name])
([Name] will return the form's name if used in a control property in the
form design - provided you don't have a control by that name!)

and modify your function code to:

Public Function f(strQueryName As String, strFormName As String, ...)
Dim db As DAO.Database
...
Set db = CurrentDb
strSQL = db.QueryDefs(strQueryName).SQL
strWhere = DLookup("WhereClause", "tblQueryFilters", _
"FormName = '" & strFormName & "'")
strSQL = strSQL & strWhere
r= CurrentDb.OpenRecordset(strSql)
.....
r.Close
Set r = Nothing
Set db = Nothing
End Function

HTH,
Nikos

 

[Fjordur]

Nikos,
I'm still scratching my head about this, but gotta leave, I'll get back to
this in 2 days. In the meantime, thanks a lot

 

[Fjordur]

Hi, Nikos,
I'm afraid i have a problem with your suggestion below:

The new table, say tblQueryFilters has two fields, FormName and
WhereClause; for the particular combination, the values would be:
FormName: Form1 (or whatever)
WhereClause: " WHERE Field3 = " & Forms![Form1]![Control1]
& " AND Field4 = '" & Forms![Form1]![Control2] & "'"

This can't be inserted in a db field, can it? it's not a string.
Forms![Form1]![Control1] will not be evaluated.
Do you mean:
WhereClause: "WHERE Field3 = [Forms![Form1]![Control1] etc"

That way, you call your function as:
f("Query1", [Name])
([Name] will return the form's name if used in a control property in the
form design - provided you don't have a control by that name!)
and modify your function code to:
Public Function f(strQueryName As String, strFormName As String, ...)
Dim db As DAO.Database
Set db = CurrentDb
strSQL = db.QueryDefs(strQueryName).SQL
strWhere = DLookup("WhereClause", "tblQueryFilters", _
"FormName = '" & strFormName & "'")
strSQL = strSQL & strWhere

OK but then I'd get a long string, the same that I have now, with references
to the form is the string, but these won't be evaluated, back to square one

r= CurrentDb.OpenRecordset(strSql)

Or did I miss something?

 

[Fjordur]

Nikos (and all),
in case you're interested to follow-up on this, I found THE solution on
http://www.mvps.org/access/queries/qry0013.htm

 

[Nikos Yannacopoulos]

I'm afraid you didn't miss anything, I did! Yes, you can store the where
clause in the table field, but when it's read in the code it will indeed
be treated a string - stupid of me.
It is still possible to do it in a similar fashion, though more
complicated: you would need a separate table with a one to many
relationship to the forms, one record per field to filter on with fields
for FormName, FieldName, FormCOntrolName and type (numeric vs. text);
then you would need to open this table as a recordset in your code,
filtering on the form name, and loop through the records constructing
the Where clause. workable, though not very neat.
Alternatively, you could use a convention like: no additional table, all
the info for fields to filter on comes from the form itself: for all
fields you wish to filter on, name the corresponding control on the form
with a perfix followed by the field name (like fltrFlieldX for FiledX),
then loop in your code through the form controls and add to the where
clause string for those controls starting fltr, extracting the field
name from the control name (as in Right(ControlName,
Len(ControlName)-4)...). If all the fields you filter on are one kind
(either text or numeric) then things are simple, otherwise you might
rely on the filter value to determine, checking the value with the
IsNumeric function (tricky if you have text fields with numerals only!).
Another approach to that end might be to use an extra letter in the
prefix to idenntify the type, like fltrnField1 for numeric vs.
fltrtField2 for text, extracting that with Mid(ControlName, 5, 1).
Again, not very neat, but probably preferable over the previous one.

I'll post back if I come up with any other idea.

HTH,
Nikos

Hi, Nikos,
I'm afraid i have a problem with your suggestion below:

The new table, say tblQueryFilters has two fields, FormName and
WhereClause; for the particular combination, the values would be:
FormName: Form1 (or whatever)
WhereClause: " WHERE Field3 = " & Forms![Form1]![Control1]
& " AND Field4 = '" & Forms![Form1]![Control2] & "'"

This can't be inserted in a db field, can it? it's not a string.
Forms![Form1]![Control1] will not be evaluated.
Do you mean:
WhereClause: "WHERE Field3 = [Forms![Form1]![Control1] etc"

That way, you call your function as:
f("Query1", [Name])
([Name] will return the form's name if used in a control property in the
form design - provided you don't have a control by that name!)
and modify your function code to:
Public Function f(strQueryName As String, strFormName As String, ...)
Dim db As DAO.Database
Set db = CurrentDb
strSQL = db.QueryDefs(strQueryName).SQL
strWhere = DLookup("WhereClause", "tblQueryFilters", _
"FormName = '" & strFormName & "'")
strSQL = strSQL & strWhere

OK but then I'd get a long string, the same that I have now, with references
to the form is the string, but these won't be evaluated, back to square one

r= CurrentDb.OpenRecordset(strSql)

Or did I miss something?

 

[Nikos Yannacopoulos]

Cool!