Solved

Access 2010 for loop skips a record on delete

Posted on 2013-11-27
6
383 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 (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 48

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
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 (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 48

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 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