LeLeBrown
asked on
Access 2010 for loop skips a record on delete
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_rptfilterAddre
cd.QueryDefs.Delete ("dynamicqry_rptfilterAddr
Else
If qd.Name = "dynamicqry_rptfilterBeat"
cd.QueryDefs.Delete ("dynamicqry_rptfilterBeat
End If
End If
End If
Next
Thanks
For Each qd In cd.QueryDefs
If qd.Name = "dynamicqry_formfilter" Then
cd.QueryDefs.Delete ("dynamicqry_formfilter")
Else
If qd.Name = "dynamicqry_rptfilterAddre
cd.QueryDefs.Delete ("dynamicqry_rptfilterAddr
Else
If qd.Name = "dynamicqry_rptfilterBeat"
cd.QueryDefs.Delete ("dynamicqry_rptfilterBeat
End If
End If
End If
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The redefine worked great.
Thanks!
Thanks!
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.
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.
ASKER
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?
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?
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]![ShowOnlySelect
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:
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]![ShowOnlySelect
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