Solved

Access 2010 for loop skips a record on delete

Posted on 2013-11-27
6
366 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
  • 4
  • 2
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

828 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