?
Solved

Access 2010 for loop skips a record on delete

Posted on 2013-11-27
6
Medium Priority
?
387 Views
Last Modified: 2013-11-27
I am reading through querydefs using a for loop. I am looking for 3 querydefs and deleting them if found so I can create them again later in code. I have a nested if for this inside the for loop. When I find and delete the first one, it skips the next querydef (which is another one I wish to delete). How can I get around this? Code I am using is below.
Thanks

For Each qd In cd.QueryDefs
  If qd.Name = "dynamicqry_formfilter" Then
    cd.QueryDefs.Delete ("dynamicqry_formfilter")
  Else
    If qd.Name = "dynamicqry_rptfilterAddress" Then
      cd.QueryDefs.Delete ("dynamicqry_rptfilterAddress")
    Else
      If qd.Name = "dynamicqry_rptfilterBeat" Then
       cd.QueryDefs.Delete ("dynamicqry_rptfilterBeat")
      End If
    End If
  End If

Next
0
Comment
Question by:LeLeBrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39681108
Why are you looping at all?  Why not just delete the query?

On Error goto ProcError
set db = currentdb()

db.QueryDefs.Delete "dynamicqry_formfilter"
db.QueryDefs.Delete "dynamicqry_rptfilterAddress"
db.QueryDefs.Delete "dynamicqry_rptfilterBeat"

ProcExit:
     Exit Sub
ProcError:
    If err.number <>  3265 then
         msgbox err.number & vbcrlf & err.description, vbokonly, "error deleting query"
    End if
    Resume Next

Open in new window

0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39681118
Actually, if you are going to just create them again later in code, why not leave them there and instead of recreating them, just redefine the SQL:

strSQL = "SELECT * FROM yourTable"
db.Querydefs("dynamicqry_formfilter").SQL = strSQL
0
 

Author Closing Comment

by:LeLeBrown
ID: 39681182
The redefine worked great.

Thanks!
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39681221
glad to help.

If the query name is any indication, you are making dynamic changes to the query for a number of reasons. I assume that this is mostly with regards to the criteria in the query.  If so, have you considered using a parameter query or using TempVars to store variables to be used in your queries?  That way, you wouldn't necessarily need to redefine the entire SQL, just the appropriate parameters.
0
 

Author Comment

by:LeLeBrown
ID: 39681274
Thanks again

I have very complex queries to start with and then the user wanted one more criteria added. One table in the existing queries holds a Yes/No indicator field. If the user wants to see everyone, I need to not select on that field at all and just do select existingqry* from existingqry. If they want to see only people that meet this criteria I need select existingqry* from existingqry where field = "Y".
I did try to add a parameter to the existing queries and passing in a text field value from the form but got errors saying the criteria was too complex so I had to try a different method. I was trying to pass in "Y" or in("Y","N").

When you say tempvars, you mean global variables?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39681325
Don't get me wrong, there are lots of good reasons to write dynamic SQL, but not knowing your level of experience, I wanted to toss out those as alternatives.

TempVars were new in 2007.  They are a collection, which provides the advantage that their values are not lost if you encounter an unhandled error in your code (like global variables do).  Here is an article on using tempvars.  You create a TempVar with text like:

TempVars.Add "varName", varValue

So, if you only want to display those that are checked, you could set your tempvar to -1

TempVars.Add "ShowOnlySelected", -1

If you want to display all of the records, you would use:

TempVars.Add "ShowOnlySelected", 0

Then, in your query you would use something like:

SELECT * from YourTable where Val(NZ([Yes-No-Field],0)) <= [tempvars]![ShowOnlySelected]

Another option is to simply use the Forms Filter property to do this.  I have several application where the user wanted to filter values in the continuous form to display all records or only those where a particular [Yes/No] field is set to Yes (-1).  I included a checkbox in the forms header, and use that to filter the form.  In the checkbox Check event, I reset the forms filter, something like:
Private Sub chk_ShowOnlySelected_Checked

    if me.chk_ShowOnlySelected then
        me.filter = "[Yes-No-Field] = -1"
        me.filteron = true
    Else
        me.Filter = ""
        me.filterOn = false
    End If

End Sub

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question