Solved

Access 2010 for loop skips a record on delete

Posted on 2013-11-27
6
356 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

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

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

786 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