Shared Sub-form referring to a field in the same sub-form
in [Forms]
|
Prev: Reload Previous Filter
Next: Undo a combo box selection
From: Iram on 13 May 2010 12:41 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 13 May 2010 14:34 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 13 May 2010 17:12 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 13 May 2010 18:44 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 13 May 2010 20:09
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] > . > |