Shared Sub-form referring to a field in the same sub-form

Discussion in 'Microsoft Access Form Coding' started by Iram, May 13, 2010.

  1. Iram

    Iram Guest

    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
     
    Iram, May 13, 2010
    #1
    1. Advertisements

  2. Iram

    Iram Guest

    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]
    > .
    >
     
    Iram, May 13, 2010
    #2
    1. Advertisements

  3. Iram

    Iram Guest

    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]
    > .
    >
     
    Iram, May 14, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Casey

    Referring to Other Subform on Same form

    Casey, Jul 14, 2003, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    448
    Casey
    Jul 15, 2003
  2. Tony

    Referring to a sub form on a tabbed form

    Tony, Mar 10, 2004, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    155
  3. Guest

    Referring to field on sub form

    Guest, Feb 21, 2005, in forum: Microsoft Access Form Coding
    Replies:
    7
    Views:
    197
    Guest
    Mar 1, 2005
  4. Guest

    main form, sub form, sub sub form ...etc

    Guest, Oct 31, 2006, in forum: Microsoft Access Form Coding
    Replies:
    8
    Views:
    517
    Guest
    Nov 5, 2006
  5. Capt. Skinny

    Referring to form field in control's RowSource WHERE clause

    Capt. Skinny, Sep 22, 2009, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    2,200
    Jack Leach
    Sep 22, 2009
Loading...

Share This Page