Can't change recordset criteria after DB open?

[hozo]

Hey guys.. How do I change what the filter criteria is on a DB connection (jet3.5) after it's already open?

Here is form_load
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.3.51; Data Source=C:\x\Customer.mdb;"
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select * from TroubleTicket where AssignedTo = '" & UCase(WhoAmI) & "' Order by TicketID ", db, adOpenStatic, adLockOptimistic

Later on I want to do something like this...
adoPrimaryRS.open "select * from TroubleTicket"
but I get errors. If I close and open the adoproimaryrs obj I also get errors. do I have to completely close the DB connection to get what I want? Can't you juse execute another command to change the recordset?

[Clunietp]

why are you using the Shape provider? You do not appear to be doing any data shaping. This is probably your issue here....

[hozo]

No clue what your talking about :)

[hozo]

Ok I took that out and still doesnt work..
What I'm doing is creating a ticket system. The default load is to SELECT * from the table and show everything..
I want nice drop down options that let them change the recordset paramaters, ie to closed tickets, open only tickets, only tickets asigned to specific usres, etc.. which is easy to do with the sql commands.. It's the ado part that doesnt work.. I'm gettig "Conncetion already open" yadadada problems.
I can't find an easy way to just change the recordset without diming a new connection, new recordset, using a new open command and then refreshing all the text boxes.. which is just annoying.. Anyone know what I'm saying?

[Edneeis]

Have you tried ADO.Filter="TicketInfo='Closed'"?
Or whatever you are filtering by. It will take the recordest and filter or show only the ones matching the criteria almost like Select. Try something along that lines.