How can my cascading combo boxes sometimes begin with null value boxes?

[shocktea]

I have a sequence of 7 cascading combo boxes. If I want to skip selection for the first (i.e.) 2 combo boxes and enter in data for the rest, it won't let me.
I don't know how to write the cascading combo box VBA code that also includes telling Access a null/blank combo box to begin with is okay.

It currently produces an error message related to my current VBA code:
Syntax error (missing operator) in query expression '[Review Qtr]="AND [Review Year] = AND [Review Category] = " AND [Product]='Product 1".

---
Right now my query includes code to allow combo boxes to be empty AFTER the selections, but not BEFORE the selections. It works perfectly for the AFTER, but I also need the BEFORE.

[Review Qtr]=[forms]![frmContract]![CBRevQtr] Or [forms]![frmContract]![CBRevQtr] Is Null
Criteria: true
---

My VBA code for the cascading CBs is:

Private Sub Form_Load()
On Error Resume Next
 
  CBRevQtr.SetFocus
  CBRevQtr.Enabled = True
  CBRevQtr.RowSource = "Select distinct [Review Qtr] " & _
           "FROM tblTotalHistoricalData " & _
           "ORDER BY [Review Qtr]"
 
 
End Sub
Private Sub CBRevQtr_AfterUpdate()
  On Error Resume Next
 
  CBRevYear.Enabled = True
  CBRevYear.RowSource = "Select distinct [Review Year] " & _
           "FROM tblTotalHistoricalData " & _
           "WHERE [Review Qtr] = '" & CBRevQtr & "' " & _
           "ORDER BY [Review Year]"
  CBRevYear.SetFocus
 
  CBRevCategory.Enabled = True
  CBRevCategory.RowSource = "Select distinct [Review Category(G/Y/R)]" & _
           "FROM tblTotalHistoricalData " & _
           "WHERE [Review Qtr] = '" & CBRevQtr & "' AND " & _
           "[Review Year] = " & CBRevYear & " " & _
           "ORDER BY [Review Category (G/Y/R)]"
 
  CBProduct.Enabled = True
  CBProduct.RowSource = "Select distinct Product " & _
           "FROM tblTotalHistoricalData " & _
           "WHERE [Review Qtr] = '" & CBRevQtr & "' AND " & _
           "[Review Year] = " & CBRevYear & " AND " & _
           "[Review Category (G/Y/R)] = '" & CBRevCategory & "' " & _
           "ORDER BY Product"
 
  CBType.Enabled = True
  CBType.RowSource = "Select distinct Type " & _
           "FROM tblTotalHistoricalData " & _
           "WHERE [Review Qtr] = '" & CBRevQtr & "' AND " & _
           "[Review Year] = " & CBRevYear & " AND " & _
           "[Review Category (G/Y/R)] = '" & CBRevCategory & "' AND " & _
           "Product = '" & CBProduct & "' " & _
           "ORDER BY Type"
 
  CBCustomer.Enabled = True
  CBCustomer.RowSource = "Select distinct Customer " & _
           "FROM tblTotalHistoricalData " & _
           "WHERE [Review Qtr] = '" & CBRevQtr & "' AND " & _
           "[Review Year] = " & CBRevYear & " AND " & _
           "[Review Category (G/Y/R)] = '" & CBRevCategory & "' AND " & _
           "Product = '" & CBProduct & "' AND " & _
           "Type = '" & CBType & "' " & _
           "ORDER BY Customer"
 
  CBContract.Enabled = True
  CBContract.RowSource = "Select distinct Contract " & _
           "FROM tblTotalHistoricalData " & _
           "WHERE [Review Qtr] = '" & CBRevQtr & "' AND " & _
           "[Review Year] = " & CBRevYear & " AND " & _
           "[Review Category (G/Y/R)] = '" & CBRevCategory & "' AND " & _
           "Product = '" & CBProduct & "' AND " & _
           "Type = '" & CBType & "' AND " & _
           "Customer = '" & CBCustomer & "' " & _
           "ORDER BY Contract"
End Sub

Any help is appreciated!

p.s. I could also add an "ALL" drop down item for all the combo boxes, if it would produce the correct report, but I don't know how to do this either.

 

[WayneRyan]

Shocktea,

You can use the GotFocus of the later combos to check the prior ones and
alter the RowSource for itself:

sql = "Select ... " & _           '
      "From   YourTable(s) " & _  ' Your original RowSource minus the Where
      "Where "                    '

If Me.combo1 <> "" Then sql = sql & "[Review Qtr] = " & Me.combo1 & " And "
If Me.combo2 <> "" Then sql = sql & "[ReviewYear] = " & Me.combo2 & " And "
.
.
.
sql = Left(sql, Len(sql) - 5)

An additional thought (maybe easier) would be to modify your original sql
to have:

Where ([Review Qtr] = Forms!YourForm!Combo1 Or Combo1 Is Null) and
      ([ReviewYear] = Forms!YourForm!Combo2 Or Combo2 Is Null) ...

ps
I should have read your whole post first, now I'm really confused.

hth,
Wayne