Gemme søgning/filter

Not quite sure what your trying to do but if necessary you could make a query and alter this vi code.
How doe sthat sound?

Function MakePassThru(rstrSQL As String, ByVal vstrPassThruQueryName As String) As Integer
    Dim db  As Database
    Dim i  As Integer

    Set db = DBEngine.Workspaces(0).Databases(0)
   
    i = 0
    Do While db.QueryDefs(i).Name <> vstrPassThruQueryName
        i = i + 1
    Loop

    db.QueryDefs(i).SQL = rstrSQL
    db.Close

    MakePassThru = True
   
End Function


You can then call this function to alter the query

sSQL is the SQL you want to change and qry i sthe name of the query

MakePassThru(sSQL, "qry")

Terry>> Når der er returneret et filter vil der i egenskabsarket i designvisning blive skrevet den streng jeg har filtreret, det er denne streng jeg vil gemme evt. som en forespørgsel som jeg så senere kan vælge på en liste

You could have a table which contains the names of these queries and then use code to create them.

Okay, det lyder lidt avenceret, men jeg prøver, og vender tilbage

>Terry: how does this go... (untested by me, but I think you are doing a bit of overkilling)

Function MakePassThru(rstrSQL As String, ByVal vstrPassThruQueryName As String) As Integer
  Dim DB As Database
  DB.QueryDefs(vstrPassThruQueryName).SQL=rstrSQL
  MakePassThru = True
End Function

and of cause:

  Dim DB As Database
  Set DB=CurrentDB
...

proaccess>> hvis jeg laver din løsning, hvordan kalder jeg funktionen via formen en knap??

Jeg mener: når filret/søgningen er udført skal brugeren så kunne vælge at gemme søgningen?

Hi proaccess, what is overkill?

Its just a piece of code I have used for the alst fe wyear, perhaps it can be easier, but it works!

Har prøvet, men det køre ikke rigtigt, jeg larder spørgsmålet stå lidt for at se om der kommer felre indlæg.ses efter frokost*S*
/Anders

>Terry: wasn't ment to hurt your feelings... ;0)  I always aim against having the least of loops etc. You are looping through all queries in the DB, to find the one needing a change, and then you loop through the rest after the change...  I tend to go direct to the query, that needs changing and then make the change!

>aandersen: Det var IKKE en løsning, men mere en kommentar til min ven Terry!
Du skal som han siger oprette en tabel til at "holde" din Filter-info...

Du kan få fat i filteret via Me.Filter, hvor du også skal huske at Me.FilterOn angiver om der er filter aktivt eller ej...

hi proaccess, no I didnt think you were trying to hurt my feelings, that piece of code is actually something I dug upp somewhere and I have never bothered to change it. I am sure it can be done much easier, just never seem to get time.

This example can be used to delete a specific query and also create a new one.

Dim MyQry As QueryDef
Dim MyDB As Database
      Set MyDB = CurrentDb()
       
        'Refresh QueryDefs collection.
        MyDB.QueryDefs.Refresh
        ' If QryTemp query exists, delete it.
        For Each MyQry In MyDB.QueryDefs
            If MyQry.Name = "QryTemp" Then
                MyDB.QueryDefs.Delete MyQry.Name
'and you would exit here  EXIT for or what ever
            End If
        Next MyQry
   
        'Create a new querydef
        Set MyQry = MyDB.CreateQueryDef("QryTemp")
        MyQry.SQL = "Your SQL Goes here"
        MyQry.Close

Hej igen tilbage efter frokost. Jeg har kikket på jeres forslag og forstår den overordnet stradegi, men er det nu det jeg har brug for. Mit første senarie var nok ikke helt præcist, så jeg prøver igen*S*S.:

brugeren danner et filter i formularen, på formularen skal der være en knap "Gem filter", når brugeren klikker på knappen skal der komme en boks op hvor brugeren kan navngive sit aktuelle filter med et sigende navn og derefter klikke på ok. Det filter brugeren har gemt skal så kunne indlæses igen ved at bugeren vælger det på en liste.Listen vil efterhånden indeholde diverse brugerdefineret filtre som man kan vælge. Det er simpelthen det jeg skal lave, om så det aktuelle filter bliver gemt som en forespørgsel, eller noget andet er egenligt ligemeget.
Forløbig tak for alle indlægende. For god ordens skyld skal det lige nævnes at jeg arbejder i  access2000.
/Anders.

You can also save the SQL statement in the table along side th ename you give it. (TEXT Fields 255 characters) or use a memo if you think it is longer.


Then when the user selects it from a combo you can use Dlookup to find it again and replace it with the filter.


Is thatwhat you arae after?

Terry>> Ja det lyder som et godt forslag, kan du uddybe det

OK you first need to make a table for saving your filter info. These fields should be enough.
ID Autonumber (primary key)
FilterName    text
FilterSQL    memo

You will need a combo box which selects the ID and FilterName from the new table.

When you select from the combo you can use soemthing like this.

Me.filter = Dlookup("FilterSQL", "TheNameOfTheTable", "ID = " & Me.TheNameOfTheCombo )
Me.filterOn = True

You could also have a toggle button to remove the filter if needed.


Now the only problem is saving a filter to the table.
If I have understood your question correctly then you already have some way of changing the filter and it is this filter you want to save to the dB.

Lets say you have a button to start the cod efor saving a new filter.

Docmd.setwarnings False
Docmd.Runsql "INSERT INTO YourFilterTable (FilterName, FilterSQL) VALUES ('" & Me.ThefieldContainingQueryName & "', '" & me.filter & "')"
Docmd.setwarnings True

Then you need to refresh your combo

Me.TheNameOfTheCombo.Requery

Note I have NOT checked this AT ALL

YES Terry that's what i'm looking for, can you help me *SS*

/Anders

sounds good, i will try it right away, and will be back soon.

/Anders.

Hi Terry
Det virker, dog med lidt tilpasning, men den overordnede ide var lige det der skulle til, du skal have mange tak for hjælpen, og points er nu på vej til dig. Nogen gange er løsningen faktisk meget enkel, bare man for den korrekte indgangsvinkel. Endnu engang tak for hjælpen and, Have a nice weekend.

*SSSS* Anders.

Yes I would think some alterations were necessary, not easy to give an error free answer when its not possoble to see your dB. Anyway, you got it working, thats what it sall about :o)

Thanks to you too and have en god weekend også!
Terry