You ewakky need to try to understand any code you are going
to use. What I posted was just the general idea, not
something that could be Copy/Pasted as is.
The compile error is because the variable strSQL was not
declared. The placeholders f1, f2, ... need to be replaced
with the real field names you have in your table. Also,
setting a record source or row source automatically causes
the data to be loaded, so it is redundant and a waste of
resources to Requery after setting either of those
properties.
I'll **try** to clean it up for you, but you still need to
double check everything as I am just typing the code into a
post and I am a notoriously poor typist.
Private Sub Category_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [Sub Category], Category, " _
& "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 Category]"
Me.Sub_Cat.RowSource = strSQL
Me.Sub_Cat.SetFocus
Me.Sub_Cat.Dropdown
Else
Beep
Me.Sub_Cat = Null
End If
End Sub
On the After Update of the Category field I have the following...
Private Sub Category_AfterUpdate()
strSQL = "SELECT f1, f2, ... FROM tbl_category WHERE "
If Not IsNull(Me.cboCategory) Then
strSQL = strSQL & "Category=" & Me.cboCategory
Me.cboSub_cat.RowSource = strSQL
Me.Sub_Cat.Requery
Me.Sub_Cat.SetFocus
Me.Sub_Cat.Dropdown
End Sub
...but I get an error after modifying the Category field, "Compile Error
Variable not defined". FYI, I haven't done anything to the Sub-Cat field
events or attributes, but this is what the sql view looks like for the
Sub-Cat Row source...
SELECT tbl_CategorySub.[Sub Category], tbl_CategorySub.Category,
tbl_CategorySub.ReportType AS [Category Type], tbl_CategorySub.Remove
FROM tbl_CategorySub
WHERE (((tbl_CategorySub.Remove)=0))
ORDER BY tbl_CategorySub.[Sub Category], tbl_CategorySub.Category;
The Sub-Cat dropdown needs to display 3 columns, with heads, at 2";2";2"
column widths, and 50 list rows, and 6" List width.
Can you still help me with this? If so what am I doing wrong?
Marshall Barton said:
Iram wrote:
I have a subform called "frm_SubformCaseDetail". This sub-form is a sub-form
on many Forms, about 20 in total for so many different reasons.
I have a problem in the sub-form. One field references another field,
example the Sub-Category combobox field narrows down its list based upon the
Category combobox field. Since the query that runs the subform needs to be
configured to look at the Category field it wants to point to a particular
form with the entire path including [Form] [Sub-form] [field]. I don't want
to create a seperate subform for each of the 20 Forms, I would rather keep
this shared sub-form but configure it to look at the Category field without
having it to look at the entire path. Does this make sense? If so is this
possible?
It makes sense, but I know of no way to use that kind of
parameter.
An alternative is to use VBA code to construct the
subcategory combo box's row source query. Use the category
combo box's AfterUpdate ir Exit event:
strSQL = "SELECT f1, f2, ... FROM categories WHERE "
If Not IsNull(Me.cboCategory) Then
strSQL = strSQL & "Category=" & Me.cboCategory
Me.cboSubcategory.RowSource = strSQL
End If
You will need to run the same code wherever you Requery
cboSubcategory.
