Prev: Reload Previous Filter
Next: Undo a combo box selection
From: Marshall Barton on 16 May 2010 17:28
Iram wrote:
>This is what I have adjusted your code to but, after selecting a Category in
>the Category combobox the drop down opens up for the Sub-Cat field but with
>no selections available. In the Sub-Cat combobox field I noticed that the
>below code inserted this into the Row Source
>
>SELECT Sub_Category, tbl_CategorySub, ReportType AS [Category Type], Remove
>FROM tbl_CategorySub WHERE Remove=0 And Category=User ID ORDER BY [Sub_Cat]
>
>Dim strSQL As String
> strSQL = "SELECT [Sub_Category], tbl_CategorySub, " _
> & "ReportType AS [Category Type], " _
> & "Remove FROM tbl_CategorySub " _
> & "WHERE Remove=0 And "
> If Not IsNull(Me.Category) Then
> strSQL = strSQL & "Category=" & Me.Category _
> & " ORDER BY [Sub_Cat]"
>
> Me.Sub_Cat.RowSource = strSQL
> Me.Sub_Cat.SetFocus
> Me.Sub_Cat.Dropdown
> Else
> Beep
> Me.Sub_Cat = Null
> End If
>End Sub
>
>--------------------------------
>Table Info
>tbl_Category 'this table has the following fields: Category,
>ReportType, Description, Remove Category.
>
>tbl_CategorySub 'this table has the following field: Sub Category,
>Category, ReportType, Remove
>
>Marshall, what am I doing wrong?


You replaced a field name with a table name and there is an
underscore where you said there is a space:

strSQL = "SELECT [Sub Category], Category, " _

I also see that I had a logic error, It should be:

& "WHERE Remove=0"
> If Not IsNull(Me.Category) Then
strSQL = strSQL & " And Category=" & Me.Category _

Are you sure the SubCat combo box needs all four fields?
Check that against the ColumnCount property.

--
Marsh
MVP [MS Access]
First  |  Prev  | 
Pages: 1 2
Prev: Reload Previous Filter
Next: Undo a combo box selection