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.