Way too Complex cascading combo boxes

[AccessNovice7]

I'm having trouble getting the file small enough to upload

[AccessNovice7]

ok, here is a sample - again, I appreciate all of your time and imput.

[AccessNovice7]

Oh, I stripped out the excel file import routine - that part is all working fine anyway

[June7]

I don't think the Change, Click, GotFocus events for Criteria1 are really needed. Why Click event for Criteria2?

Selection1 and Selection2 are set for multi-select - do you want to allow multiple parameters for one field? For alternative code review http://allenbrowne.com/ser-50.html

If field names in combobox are exactly same as in table, Case structure could be reduced. If the fields were all same data type would not need Case.

For Each varItem In Me.Selection2.ItemsSelected
  Select Case Me.Criteria2
    Case "Region", "Fiscal Year"
      strCriteria1 = strCriteria1 & Me.Criteria2 & " = " & Me.Selection2.ItemData(varItem) & " OR "
    Case Else
      strCriteria1 = strCriteria1 & Me.Criteria2 & " = '" & Me.Selection2.ItemData(varItem) & "' OR "
 End Select
Next

[AccessNovice7]

Thank you so much for taking the time to help me. This piece is working well now and I learned allot. The fields aren't all the same data type unfortunately and I am allowing multiple parameters. So it may not be pretty, but it is doing the job so far in my tests. Have a great evening!

[ssanfu]

It took me a while (job kept interfering) but I managed to mangle your dB.

There were a couple of problems. One was the field data types. The other was that the record source for the subform was a query.
The code was changing the query SQL after the subform was open. I couldn't figure out a way to close and open the subform to get the new query into the subform.

So I changed the subform records source to a query I created, "qrySales". Then it was a matter of getting the field types to set the correct delimiters.
In the code, I set the subform filter property after processing the selections in the list boxes.

Before all of the above happened, I created another form to get everything working. I prefer the single form when possible...

Almost forgot: I also created a table to hold the field names of table "Sales". It was easier to make it bend to my will.


Maybe this will give you more ideas...... it was fun

[AccessNovice7]

ok, thanks, I'll take a look. Appreciate your time.