How do I workaround query being too complex

[wordbrew]

I thought I was done with my database, everything was working great, but after going into the query that my subform is based on and simply changing a field from ascending to descending, then closing that and trying to open my form, I got the "query is too complex" error and then everthing went south. Access closed itself and the file was unrecoverable. Luckily I had saved this file with 2 other backups! I tested again. Everything works fine in the form if I don't touch anything else, but just by opening and closing the query (without trying to change anything), the same "too complex scenario" happens again.

The query has 13 fields, with 8 of them having had criteria similar to this typed in them:

[Forms]![frmRevisionAudi t]![cboRevisionAudi t] Or [Forms]![frmRevisionAudi t]![cboRevisionAudi t] Is Null.

One of the fields had the criteria [Forms]![frmRevisionAudi t]![cboYear] Or [Forms]![frmRevisionAudi t]![cboYear] = "All".

Once I close this, if I open it back up, the query has restructured itself by filling the rows with every possible combination of criteria to return my results to the main form based on 7 combo box filters.

Is there a way for me to word this or structure this differently or use different phrasing to get my desired results without bloating my query sql a mile long?

I really appreciate any help or suggestions. I'm still an amateur at Access and am fully willing to be called a dunderhead if this is a common mistake to avoid. :) I only care about learning, improving, and getting to the final working end result.

Thanks so much!

[TheSmileyCoder]

I made some changes to the code, and where I made changes I left a short comment. If any of those are unclear, just ask.

From what I could test it works now (except your Clear buttons, but you only need to add a refreshDocDispl ay line to those to make em work.)

The reason why I prefix my controls with certain names is:
1) Because then I know if im dealing with a combobox or a textbox, and how to handle each case in code
2) Because if I im writing code, and trying to remember the name of my combobox, I can start by typing me.cmb.... and then VBA will automatically show me the list of comboboxes on my form.

[wordbrew]

Smiley this is incredible! Thank you so much. You went far above and beyond what I could have ever asked. It's so amazing to have a community of programmers so willing and able to give their time to help make the rest of us better.

Lol. And thank you for the comments in the VBA, this way I can try to understand some of the key concepts so I can grow in my understanding of VBA and improve in the future. At least I was on the right track as far as trying to implement your changes, so I don't feel like such a dunderhead. It looks like the only 2 things I'll have left is trying to make the cmb_Year combo work to filter for it's "All" selection. That's the only thing that is causing a bug at this point. But all the separate year selections work perfectly.

The other thing is my button to generate my rptRevisionAudi t that is based on the same query that my subform is. Since the subform is now filtered via VBA (thank you truckloads!) I'll have to see how to do the same thing for the report. So that once they have fitered their selection on the main form, all they have to do is hit the generate report button, and only their selection appears in the report. This was working before, but since the whole way my subform is being filtered had to be reworked, this obviously now returns all results since it's query doesn't have anything under criteria anymore. It's so funny, I originally had thought my database was done until I realized I needed to be able to filter for year, in order to make the database perpetual, and not have to have old data erased just to make room for the new year. And that extra bit of information was what pushed the query over the edge. Well here's to learning something new!

Again, thank you so much for everything you have done! I just hope I can keep improving to the point that I'm able to pay all this forward, and be able to help someone else on bytes in the future.

[TheSmileyCoder]

Your welcome.

If you change the code of your report button to include the filter string, it will open with the filter applied. Example below:

stDocName = "rptRevisionAudit"
        If Me.frmRevisionAuditSubform.Form.FilterOn = True And Me.frmRevisionAuditSubform.Form.Filter <> "" Then
            DoCmd.OpenReport stDocName, acPreview, , Me.frmRevisionAuditSubform.Form.Filter
        Else
            DoCmd.OpenReport stDocName, acViewPreview
        End If

I will go far in helping someone if they satisfy some simple criteria:
1) Its obvious they have used effort themselves, both in solving the problem, but also in making a good thread. People who don't even bother to read through their own post before pressing submit, and thus leaving half-finished or unclear sentences just ******* me off. In my oppinion a preview should be mandatory before you could hit submit.
2) They make a well described problem/question
3) They have thought enough about their question/problem, to supply all the relevant facts.
4) Its something they NEED, or are truly interested in solving for learning. I dislike people showelling their homework onto forums like this, without even having looked at it themselves first.

You satisfied all of the above.

[wordbrew]

Smiley you are too kind. Gentleman and a scholar truly. And I'm grinning bigtime because I inserted your code into my call button for the report and it is working beautifully again. So thank you thank you. You rescued my database. It's too bad the regular query couldn't handle more information or I would have been fine in the first place. But, then again, if it had worked, I wouldn't have found a better workaround via your method of using the Filter in VBA. So now I have those tools to dissect and understand and use in the future. So thank you again. Whenever you have time, I would love if you could explain the logic in the code you gave me for the report. I understand the "If Me.frmRevisionA uditSubform.For m.FilterOn = True" part, that if the condition is met then return these results, I'm just curious about the <> "" part for the And Me.frmRevisionA uditSubform.For m.Filter <> "". It all works great, I just want to know why it works. :)

My other question is if you could offer your insight to my "All" selection problem in the year combo box and what route you might have gone to solve it. I tried to include an OR statement along the lines of strFilter = strFilter & "(([Year]=" & Me.cmb_Year & "))" Or strFilter & "(([Year]=" & Me.cmb_Year ="All" & "))" , but that didn't fly at all lol.

Hey, at least I'm getting to the point where I can look at VBA and it doesn't look a Martian scribbled a cypher on some toilet paper. But I have a long way to go. I solved my problem by going ahead and using the null properties already built into the filter string. So where I had an AddItem "All" set for the cmb_Year combo box, I changed that to AddItem "". And this is working great. And I could keep it this way and simply add text on the main form along the lines of "Leave any selection box blank to return all results" or some such wording.

I was curious if I had chosen to stick to trying to keep "All" as a selection, would there have been a way to use AddItem "All" but make VBA see "All" as a Null value? Just curious.

If you ever have time to offer me any of the insights I would really appreciate it. But my database is in great shape now all due to your efforts. So thanks again! It means a whole lot.

[TheSmileyCoder]

Well I wasn't 100% sure if Access could create a situation in which FilterOn would be true, but the filterstring would be non-empty. Therefore I checked for both conditions, requiring the filterOn property to be true and the Filter string to be different (the <>) from an empty string ""

I was actually a bit surprised that the emptystring you introduced into your combobox was treated as Null by the code, so I learned something too :)

I would modify the part of the code handling the year part to:

If not IsNull(Me.cmb_Year) AND Me.Cmb_Year <>"ALL" Then
    'Set string
 
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([Year]=" & Me.cmb_Year & "))"
    FilterCount = FilterCount + 1
 
End If

So now it has to be different from Null and be different from ALL (Basicly apply no year filtering if its "All".)

I also looked at your form_Load while I was in the db. A suggestion:

Dim intCurrentYear As Integer
Dim intI As Integer
Dim intFirstYear As Integer
 
    intCurrentYear = Format(Date, "yyyy")
 
'Get first year
 
    intFirstYear = Year(Nz(DMin("fldDate", "tblRevisionAudit"), Date))
 
Me.cmb_Year.RowSourceType = "Value List"
Me.cmb_Year.AddItem "All"
 
For intI = intFirstYear To intCurrentYear
  Me.cmb_Year.AddItem intI
Next
 
 
 Me.cmb_Year.DefaultValue = intCurrentYear

Now you can only select as far back as your data actually goes. No need to be able to select 2005, if you have no data for 2005.
If you do need to be able to select a date thats further ahead then the current date (for planned revision/audit) just change the For line to:
For intI = intFirstYear To intCurrentYear+ 5
to get 5 "extra" years.

[NeoPa]

I would echo Smiley's comments about the sort of poster one is happy to put a bit of extra effort in for. It's like they always say - You make your own luck. In this case you're lucky because of how you've behaved. If everyone showed your attitude we'd all be a lot happier bunnies.

Just a quick tip to throw in while I'm here pertaining to an earlier question about how to go about resolving such complicated issues in the first place :
As a general rule of thumb, start simple and build up when you have the general concepts working.

A couple of other items for debugging, that may prove useful :
Debugging in VBA

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[wordbrew]

NeoPa

Thank you for your advice. I shall definitely try it. And I myself am lucky to have the experience and flat out crazy skills of guys like you and Smiley, as well as ADezii who has helped me in the past, to call upon. I mean, you know, i just kind of imagine the kind of poster I would be happy to help, and try to follow that example. When you're calling upon other people to give up their valuable time to help you out, essentially out of kindness and charity, then you can't be anything but humble and hopeful. So thanks again guys. I just geek out and get excited when some of these concepts, especially VBA, start to gell in my head and make sense, and I can carry forward some of the tricks I've learned.

Smiley,

Lol, when you threw in a bit of extra code for me cause you just happened to be looking at it, you solved an issue that was bugging the heck out of me in a major way. I implemented your changes for the year combo box, as well as making it handle the "All" value, and it's awesome. Now there aren't superfluous years that pop into the combo box and are unneeded. Having it dynamically populate the box "only" with years that are actually used in the system is perfect, and so much cleaner to look at. You are making me look much better than I deserve.

I've created another form (frmReplacedRem oved) in the database that handles a different job function in the Flight Manuals department (I'm actually a Flight Crew Van Coordinator, I'm just helping them out cause they asked nicely. They sorely need a better system of tracking then an excel sheet lol), and happily I've been able to use the coding knowledge you've introduced me to, and I've got it filtering pretty awesome. For some reason the year box wasn't working from the VBA filter string (I renamed the value to fldYear in the query, per your suggestion to avoid using Year), so I surrounded it with single quotes, and that solved my problem. I'm not sure why this would solve it, since in the other form's VBA it isn't surrounded by single quotes, and the year boxes in both are essentially the same, but who knows? The only thing I'm having trouble with is my cmb_Gateway combo isn't working via the VBA filter. I'm unsure why since it doesn't seem to be any different from what my other combos are doing and the type of information they are pulling, but I'm determined to work the problem out for myself before I need to call on the experts lol. I shall conquer it yet!

Thanks again guys.

[NeoPa]

Good for you.

Remember, we're here if you get stuck though :)

Good luck.

[MrDeej]

I dont know if this helps, but in our system we use alot of query-steps.

For example we have query1 which filter some fields. Then on query2 we have used query1 instead of tables as recordsource. Then we can do additional filtering or adding of tables to query1. And so on.

On our biggest query which collect data from many many tables we have maybe 7 steps.

[NeoPa]

That can be helpful, but for particularly large or complex setups, subqueries (Subqueries in SQL) may work better. This is due to the optimisations. Saved QueryDefs (as you describe) have theirs saved with them, whereas new queries, whether they involve subqueries or not, must get them worked out first before running.

This often means the saved QueryDefs have an advantage (first time they're run as subqueries), but in some cases the optimisations are determined in circumstances so different from the current usage, that they're worse than useless.

In general, once they have been worked out, I would expect that subqueries done in SQL would never be less efficient than those using saved QueryDefs for their source. I hope that make sense.

Of course that can make the maintenance a little more complicated so I don't recommend strongly. I simply suggest you bear in mind.