Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can anyone see what's wrong with this snippet 1

Status
Not open for further replies.

ToyFox

Programmer
Jan 24, 2009
161
US
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
 
I wouldn't delete and then create the querydef. You can just set the SQL property:
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, 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
 
How are ya ToyFox . . .

The following is an example of better logic flow:
Code:
[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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Woops! [blush]

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top