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:
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.
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:
Code:
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.