From: Iram on
Hello.
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?


Thanks.
Iram
From: Marshall Barton on
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.

--
Marsh
MVP [MS Access]
From: Iram on
Hello Marshall,
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?

Thanks.
Iram


"Marshall Barton" wrote:

> 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.
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: Marshall Barton on
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

Iram wrote:
>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" wrote:
>> 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.
--
Marsh
MVP [MS Access]
From: Iram on
Marshall,
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?

Iram


"Marshall Barton" wrote:

> 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
>
> Iram wrote:
> >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" wrote:
> >> 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.
> --
> Marsh
> MVP [MS Access]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Reload Previous Filter
Next: Undo a combo box selection