Filtering data with combo boxes

[ToyFixer]

I am developing a database with following objects and controls

Table - CCandGLRelation
Columns - GLCode(9 DigitCode)/ SubCategory/
BudgetCategory/AdminProd (to describe the type of expense)/and a column for
each or the Cost Centres (values Yes/No to describe whether the GLCode is
used in the Cost Centre)

so an example record would be like this
GLCode SubCategory BudgetCategory AdminProd C1000 C2000
C3000
A64000000 bla bla bla bla bla Admin yes
no yes
A64000001 bla bla xx bla bla yy Prod no
yes no

the initial logon form (frmSelectCC) gives the user the option to select the
Cost Centre e.g. C1000
What I want to do is select only the budget codes which has 'yes' in the
relevent cost centre column and let the user to filter through the GLCodes
until he selects a single GLCode. that is
AdminProd>BudgetCategory>SubCategory>GLCode.

I was successful in the first combo box (cmbAdminProd) and it displays
'Admin', 'Prod' or both depending on the relationship matrix.
This is the code I used to do it.

Private Sub Form_Load()
Dim strSQL100 As String
strSQL100 = "SELECT DISTINCT [CCandGLRelation].[AdminProd] FROM
[CCandGLRelation] WHERE ((([CCandGLRelation].[" &
[Forms]![frmSelectCC]![cmbCostCentre] & "]) = Yes))"
Me!cmbAdminProd.RowSourceType = "Table/Query"
Me!cmbAdminProd.RowSource = strSQL100
Me!cmbAdminProd = Me.cmbAdminProd.ItemData(0)
Call cmbAdminProd_AfterUpdate
End Sub

Then I wanted to select the records which has 'yes' in the cost centre
column AND which has the selected expense type (Admin or Prod only) using the
combo box cmbAdminProd. This is the code I wrote for that.

Private Sub cmbAdminProd_AfterUpdate()
Dim strSQL200 As String
Me.cmbBudgetCategory = Null
strSQL200 = "SELECT DISTINCT [CCandGLRelation].[BudgetCategory] FROM
[CCandGLRelation] WHERE ((([CCandGLRelation].[" &
[Forms]![frmSelectCC]![cmbCostCentre] & "]) = Yes) AND([" &
Me.cmbAdminProd.Value & "] = [CCandGLRelation].[AdminProd]))"
Me!cmbBudgetCategory.RowSourceType = "Table/Query"
Me!cmbBudgetCategory.RowSource = strSQL200
End Sub

But it prompts me to re-enter the value Admin or Prod before displaying the
result in the combo box cmbBudgetCategory.

Is there any syntax error? or How can I resolve this problem.

Please help me on this.

 

[Andy Hull]

Hi

I belive that the variable Me.cmbAdminProd.Value shouldn't be inside square
brackets else it is treated like a table column or a parameter in the SQL
which is why you are being prompted for it.

Referring to part of your code, you have...

AND([" & Me.cmbAdminProd.Value & "] =

I would remove the square brackets, changing it to...

AND(" & Me.cmbAdminProd.Value & " =

In full, the code looks like...

Private Sub cmbAdminProd_AfterUpdate()
Dim strSQL200 As String
Me.cmbBudgetCategory = Null
strSQL200 = "SELECT DISTINCT [CCandGLRelation].[BudgetCategory] FROM
[CCandGLRelation] WHERE ((([CCandGLRelation].[" &
[Forms]![frmSelectCC]![cmbCostCentre] & "]) = Yes) AND([" &
Me.cmbAdminProd.Value & "] = [CCandGLRelation].[AdminProd]))"
Me!cmbBudgetCategory.RowSourceType = "Table/Query"
Me!cmbBudgetCategory.RowSource = strSQL200
End Sub


Hope this Helps

Andy Hull