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]

Try posting your full SQL syntax here. Are you working only internally in Access, or does the access database run of a backend (and if so, what kind?)

[wordbrew]

Thanks for replying Smiley!

All of this is done in Access alone. Unfortunately, what seems simple when entering into the criteria section of the query design view, bloats into a huge sql statement. I'm going to only post a snippet (which is still big) because posting the entire statement would too cumbersome. Hopefully this will give you the idea.

SELECT tblRevisionAudit.RevisionNumberLookup,
       tblRevisionAudit.BagNumberLookup,
       tblRevisionAudit.RevisionAudit,
       tblRevisionAudit.fldDate,
       Format([fldDate],"yyyy") AS [Year],
       tblRevisionAudit.Sort,
       tblRevisionAudit.[Initials 1],
       tblRevisionAudit.[Initials 2],
       tblRevisionAudit.P1,
       tblRevisionAudit.E1,
       tblRevisionAudit.P2,
       tblRevisionAudit.E2,
       tblRevisionAudit.[CommentsOnAudit:]

FROM   tblRevisionAudit

WHERE  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2]))
   OR  (((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null))
   OR  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null))
   OR  (((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null))
   OR  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR  (((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR  (((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR    .....etc.

The sql statement runs on for about 5 or 6 times this length. I just wish I new how to restate everything so this crazy cascade doesn't happen. Like I said, right now in the query design view, under, say the field RevisionAudit, that is pulling from tblRevisionAudi t, in the criteria section I am putting [Forms]![frmRevisionAudi t]![cboRevisionAudi t] Or [Forms]![frmRevisionAudi t]![cboRevisionAudi t] Is Null, so that of course when my combo box on my main form is empty, then the subform will display all records for that combo box. But since I have 8 other fields I'm doing something similar with, when I close the query, Access is creating a slew of rows to deal with every situation.

Ultimately I just want my 7 combo boxes on my main form to be able to filter my subform in a bunch of combinations, including returning all results for a blank combo box.

I just hope there's a simple solution that I'm overlooking. The database is working fine if once I close the query I don't touch it again. But I do NOT want a database tetering on the edge of disaster with one false move lol.

[TheSmileyCoder]

Well I would a different approach.
To each of your textboxes on the form, I would add:

Private Sub Filter_tb_Title_AfterUpdate()
    RefreshDocDisplay
End Sub

Where RefreshDocDispl ay is a procedure on the same form, building and applying a filterstring to the subform.

For each of the textboxes in question, it will go through it, and decide whether or not to add it to the string (for instance it won't add it if textbox/combobox is null). When the build is complete, it will apply the string.

In case your wondering the FilterCount is used to keep track of whether or not to add the " AND " part. I hope this can help you!

Private Sub RefreshDocDisplay()
'Writes the Filter string to be used on subform
        
'Init variables
    Dim strFilter As String
    Dim FilterCount As Integer
    FilterCount = 0

If IsNull(Me.Filter_tb_DocID) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([tx_DocID] like '*" & Me.Filter_tb_DocID & "*'))"
    FilterCount = FilterCount + 1
    
End If

If IsNull(Me.Filter_cmb_Binder) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([ID_Binder]=" & Me.Filter_cmb_Binder & "))"
    FilterCount = FilterCount + 1
    
End If

If IsNull(Me.Filter_tb_Title) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([tx_Title] like '*" & Me.Filter_tb_Title & "*'))"
    FilterCount = FilterCount + 1
    
End If

If IsNull(Me.Filter_tb_Version) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([tx_Version] like '*" & Me.Filter_tb_Version & "*'))"
    FilterCount = FilterCount + 1
    
End If


If FilterCount > 0 Then
    'One or more filter criterea set
    Debug.Print strFilter
    Me.subFrm_ShowDocsFiltered.Form.Filter = "(" & strFilter & ")"
    
    Me.subFrm_ShowDocsFiltered.Form.FilterOn = True
    Else
    Me.subFrm_ShowDocsFiltered.Form.FilterOn = False
    
    
End If

End Sub

[NeoPa]

Definitely the way to go :)

I generally add the " AND " bit at the front in all circumstances, then simply use Mid(strFilter, 6) just before applying it (wherever that may be in the code).

[wordbrew]

Thanks so much Smiley, I'm going to try and dig into this today. I'm glad it's a VBA workaround cause I really need to keep honing my skills on it (which are minimal). To make sure, I keep my subform still based on the query I created, and I still put the criteria in the fields in the query design view, such as Forms!frmRevisi onAudit!cboRevi sionAudit (so that they still take their direction from the combo box on the main form) but I don't worry about all the other Null business. I let the VBA code handle all of that?

Thank again. I'll get crackin. And thanks NeoPa for chippin in too!

[NeoPa]

You're welcome.

I'm not sure I understand your clarification question too well, but as a general rule, the design of the query itself would still contain any references that are generally required (not dependent), whereas the code would be used for adding items that depend on things.

IE. If you had a table of animals, but you had a report that showed all elephants between the largest and smallest values entered on a form, you would design a query, based on the table, which had designed selection criteria of Type='Elephant', but the form's code would build and add a filter of something like Between Forms!YourForm. Smallest And Forms!YourForm. Largest, possibly adding it only if both entries had values entered.

[wordbrew]

I've put in the VBA code suggested by TheSmileyOne, but I think I'm just so new to the concept of filtering via VBA that I'm just fudging up somewhere. I'm including the database here, with the code i've put in. If someone could take a look and tell me where I'm going utterly wrong I would appreciate it. Hopefully I was getting close and just missing a key concept. But if I was way off base, then please giggle away. :)

[TheSmileyCoder]

Alot of place you had written Me.Filter.Revis ionLookup, the .Filter should not be there.

I fixed it for the first combobox, the revision lookup, then you can work for there.

Note that I switched the syntax from using a Like "*"..... to =, since your using a combobox with ID fields.

I removed all the criteria from the query of the subform, the only criteria you should use there, are ones NOT related to the form.

You also (as far as i know) do not need to requery after setting the filterstring, so I removed that for the first combobox bit of code as well.

Have a look, see if you can make it work.

[wordbrew]

Alright, I've tried replacing and configuring the other VBA from what i could divine from your changes. I even went back and changed the names of the other boxes so I could make things look consistent. But I'm just not seeing something.

I don't know if it's because two of my combo's are based on value lists cmb_RevisionAud it, and cmb_Sort and/or my cmb_Year needs to sort just the year in the fldDate field in the query, which it was doing before in the query, but now the year format probably needs to be put in the VBA filter.

I really appreciate everything I've been helped with, and I don't expect to have it tinkered with and handed back to me working lol, if I could just get a few tips on where I'm going wrong that would be wonderful. You've already been awesome expending valuable time on my problem.

[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.