Can anyone see what's wrong with this snippet 1

[ToyFox]

Can someone spot anything wrong with this code.
I build a query on the fly...but sometimes it gets blown away. I am trying to find the criteria that causes it....

code:

Dim Criteria As String, TableName As String, QueryName As String
TableName = "EQWDC"
QueryName = "PrimeVoters"
Criteria = "SELECT * FROM " & TableName & " WHERE " & Me.Filter
Criteria = Criteria & " Order by street"
'"Select * from eqwdc where " & Me.Filter

Dim WorkDB As Database, WorkQuery As QueryDef, WorkSet As Recordset

Set WorkDB = DBEngine.Workspaces(0).Databases(0)
WorkDB.QueryDefs.Delete QueryName
Set WorkQuery = WorkDB.CreateQueryDef(QueryName, Criteria)
Set WorkSet = WorkDB.OpenRecordset(QueryName, DB_OPEN_DYNASET)

WorkSet.MoveFirst

h

 

[dhookom]

I wouldn't delete and then create the querydef. You can just set the SQL property:

Dim Criteria As String, TableName As String, QueryName As String    
TableName = "EQWDC"    
QueryName = "PrimeVoters"    
Criteria = "SELECT * FROM " & TableName & " WHERE " & Me.Filter    
Criteria = Criteria & " Order by street"   
'"Select * from eqwdc where " & Me.Filter        
Dim WorkDB As Database, WorkSet As Recordset    
Set WorkDB = DBEngine.Workspaces(0).Databases(0)
WorkDB.QueryDefs(QueryName).SQL = Criteria
Set WorkSet = WorkDB.OpenRecordset(QueryName, DB_OPEN_DYNASET)
If Not WorkSet.EOF Then
    WorkSet.MoveFirst
End If

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya ToyFox . . .

The following is an example of better logic flow:

[blue]   Dim db As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   Set qdf = db.QueryDefs("PrimeVoters")
   SQL = "SELECT * " & _
         "FROM EQWDC " & _
         "WHERE (" & Me.Filter & ") " & _
         "Order by [Street];"
   qdf.SQL = SQL
   Set rst = qdf.OpenRecordset(dbopendyaset)
   
   [green]'
   'Your Code Here!
   '[/green]
         
   Set rst = Nothing
   Set qdf = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Woops!

dbopendyaset should be dbOpenDy[purple]n[/purple]aset

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]