PC Review


Reply
Thread Tools Rate Thread

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

 
 
Iram
Guest
Posts: n/a
 
      13th May 2010
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
 
Reply With Quote
 
 
 
 
Iram
Guest
Posts: n/a
 
      13th May 2010
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]
> .
>

 
Reply With Quote
 
 
 
 
Iram
Guest
Posts: n/a
 
      14th May 2010
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]
> .
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Referring to another field in the same table =?Utf-8?B?TWFyaWFubmU=?= Microsoft Access 2 24th Jul 2006 04:47 PM
Two subforms referring to the same table. =?Utf-8?B?QmVu?= Microsoft Access Forms 0 10th Nov 2004 01:19 PM
Row source of table referring to same table? Noozer Microsoft Access Queries 2 12th Aug 2004 08:29 PM
Referring to Report Text Boxxes in a Different Band on Same Report Mike Thomas Microsoft Access Reports 1 31st Dec 2003 07:14 PM
Referring to Other Subform on Same form Casey Microsoft Access Form Coding 2 15th Jul 2003 12:11 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:24 AM.