Re: How to OpenRecordset(queryName refering to form)

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


Fjordur wrote:
Hi, Nikos,
I'm afraid i have a problem with your suggestion below:
"Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@xxxxx> wrote in message
news:uU5zVWWXGHA.1348@xxxxxxxxxxxxxxxxxxxxxxx
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?
.

Relevant Pages

  • Re: Search Form Results Error
    ... Are the control sources for the text boxes on the form valid? ... listedn in the Field List match the names in the text boxes on the form? ... Build up the criteria string form the non-blank search ... and apply to the form's Filter. ...
    (microsoft.public.access.formscoding)
  • RE: Filter by dialog
    ... The way that I like to do it is to build a filter string in a textbox on the ... writes it out to a textbox control. ...
    (microsoft.public.access.forms)
  • Re: The mystery of the missing (compound) variables.
    ... its line (so I can recover it as a number) and a following space- ... separated text string line which can be PARSEd. ... to the second last END clause. ... other than READY  the THEN LEAVE clause passes control to the second ...
    (comp.lang.rexx)
  • Re: Filtered records in subform to report
    ... > Private Sub Search_Click ... > Dim strFilter As String ... >>>how many textboxes that the user filled in, to filter. ... >> control contains anything, ...
    (microsoft.public.access.gettingstarted)
  • Re: combining filter fields
    ... Only 3 lines of codes per text box and the Control string. ... You just get left with Filter: ... Private Sub CID_Change ...
    (microsoft.public.access.forms)