opening a form but changing the where part of the sql

[Ruegen]

I open up a form called "frmSchoolsFound" using an instance that loads schools with a saved query as a record source that has a field criteria of null on a date field.

This works great until I try to open a single school that isn't in the form because the filter has excluded it (and creates an error).

So far I have replace() on the sql string and make that the row source (I pull in the sql from the query then replace the row source with the modified sql)



My question is can you change the where statement of a set query without having to use replace() on a string as being able to tell it to open up the filtered form, release the criteria (if you get what I mean) and set a new criteria without modifying the original query .

Sort of like docmd.openform with a where criteria.

[June7]

Not that I know of. Don't set the Is Null parameter in the query. Set the filter criteria when the form opens.

[Ruegen]

Not that I know of. Don't set the Is Null parameter in the query. Set the filter criteria when the form opens.

Yeah, see that's my problem. I have 4 forms using the same one query (for efficiency reasons) however I want to able to filter the form but have the knowledge that I can set the filter even though it is "not is null".

Generally I'd like to keep the parameter as "is null" and still be able to filter the form...

[Ruegen]

to show a bit of code how I currently do it

 Dim frm As Form
    Set frm = New Form_frmSchoolsFound
    
    Dim strNewSQL As String
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs(frm.RecordSource)
    
    strNewSQL = qdf.SQL
    Set qdf = Nothing
    
    strNewSQL = Replace(strNewSQL, "WHERE " & Chr(40) & Chr(40) & Chr(40) & "tblSchools.Removed" & Chr(41) & " Is Null" & Chr(41) & Chr(41), "")
    


    With frm
        .RecordSource = strNewSQL
        .Filter = strWhere
        
        .FilterOn = True

by the time I get to filtering I have slowed down everything. I'd like it to open then filter faster.

To break down what is happening

I make an instance of the form with the recordsource from a saved query

I then grab that recordsource and stuff it into a string using a querydef

then replace the parameter in the string with nothing

then put the instance recordsource as the modded string

then set a new filter

[June7]

Have you considered not using a query object as the RecordSource for each form? Can put SQL statement in the RecordSource, without filter parameter. Then set the form filter as needed.

[Ruegen]

Have you considered not using a query object as the RecordSource for each form? Can put SQL statement in the RecordSource, without filter parameter. Then set the form filter as needed.

Yes but then I'd have to have the sql a permanent string that doesn't refer back to the original saved query.

[June7]

I don't understand why you need to refer to saved query.

[Ruegen]

I don't understand why you need to refer to saved query.

There's about 4 very similar looking forms that serve different purposes and have slightly different functions however they all feed back to the one query which means less work for me when updating the query. However this one form opens up records that might happen to be excluded by one particular field.

[June7]

Even if the forms all use the saved query as RecordSource, I would not set parameters in it. I would set filter criteria in the form when it opens.

But go with what works best for you. I think you know the options.

[Ruegen]

Yeah :-/ but now I have to go put a where statement everywhere the forms are opened...

I'll have to do it when I get back from my holiday in Europe