Solved

Access 2010 for loop skips a record on delete

Posted on 2013-11-27
6
372 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

697 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