Sub Categories

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to sort out a sub category, but for some reason I am having
trouble with my answers. I have two tables one called tblcategory and another
called tblsubcategory. I would like tblsubcategory to only show whatever is
relevant to what has been chosen from tblcategory. At the moment when I
choose a sub category I get an Enter Parameter window, instead of the options
which I have available in tblsubcategory.

I have a relationship set up between the two tables - tblcategoryid.

The script below is what I have so far.

Option Compare Database

Private Sub Subcategory_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblcategoryid] = " & Me.Category.Value
Me.SubCategory.RowSource = sSubcategorySource
Me.SubCategory.Requery
End Sub

-----------------------
The Row Source for the Category is :
SELECT [category_Query].[Category] FROM category_Query;

The Row Source for the Sub Category is (with IT changing to whatever has
been chosen in the category:
SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] FROM
tblsubcategory WHERE [tblcategoryid] = IT
 
the rowsource for "Category" should be more like:

SELECT CategoryID, Category
FROM tblcategory

bound column = 1
column count =2
column widths = 0";1"


the rowsource for "SubCategory" should be more like:

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid], " & _
"[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] =" & Me.Category

HTH,
Brian
 
And actually, you don't even need to set the rowsource programmatically. You
could just use the query builder for its rowsource. The SQL for the
SubCategory combo would be:

SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[subcategory]
FROM tblsubcategory
WHERE ((([tblcategoryid]) = Forms!YourForm!Category))

Then you'd requery SubCategory in the Category_AfterUpdate event and your
Form_Current() event

Brian



Brian Bastl said:
the rowsource for "Category" should be more like:

SELECT CategoryID, Category
FROM tblcategory

bound column = 1
column count =2
column widths = 0";1"


the rowsource for "SubCategory" should be more like:

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid], " & _
"[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] =" & Me.Category

HTH,
Brian


Paul said:
I am trying to sort out a sub category, but for some reason I am having
trouble with my answers. I have two tables one called tblcategory and another
called tblsubcategory. I would like tblsubcategory to only show whatever is
relevant to what has been chosen from tblcategory. At the moment when I
choose a sub category I get an Enter Parameter window, instead of the options
which I have available in tblsubcategory.

I have a relationship set up between the two tables - tblcategoryid.

The script below is what I have so far.

Option Compare Database

Private Sub Subcategory_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblcategoryid] = " & Me.Category.Value
Me.SubCategory.RowSource = sSubcategorySource
Me.SubCategory.Requery
End Sub

-----------------------
The Row Source for the Category is :
SELECT [category_Query].[Category] FROM category_Query;

The Row Source for the Sub Category is (with IT changing to whatever has
been chosen in the category:
SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] FROM
tblsubcategory WHERE [tblcategoryid] = IT
Click to expand...
Click to expand...
 
Thanks Brian, but for some reason my head is still in some jumbled manner and
I still can't get this to work. I've got this bit in the event:

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] = " &
Me.Category
End Sub

Private Sub SubCategory_BeforeUpdate(Cancel As Integer)


End Sub

But I seem to be lost. If I was going to put something in the Row Source
instead of the Sub Category looking up a query or a table, what would it be?

Confusion is just a state of mind and at the moment my mind is a mess :)

Brian Bastl said:
And actually, you don't even need to set the rowsource programmatically. You
could just use the query builder for its rowsource. The SQL for the
SubCategory combo would be:

SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[subcategory]
FROM tblsubcategory
WHERE ((([tblcategoryid]) = Forms!YourForm!Category))

Then you'd requery SubCategory in the Category_AfterUpdate event and your
Form_Current() event

Brian



Brian Bastl said:
the rowsource for "Category" should be more like:

SELECT CategoryID, Category
FROM tblcategory

bound column = 1
column count =2
column widths = 0";1"


the rowsource for "SubCategory" should be more like:

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid], " & _
"[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] =" & Me.Category

HTH,
Brian


Paul said:
I am trying to sort out a sub category, but for some reason I am having
trouble with my answers. I have two tables one called tblcategory and another
called tblsubcategory. I would like tblsubcategory to only show whatever is
relevant to what has been chosen from tblcategory. At the moment when I
choose a sub category I get an Enter Parameter window, instead of the options
which I have available in tblsubcategory.

I have a relationship set up between the two tables - tblcategoryid.

The script below is what I have so far.

Option Compare Database

Private Sub Subcategory_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblcategoryid] = " & Me.Category.Value
Me.SubCategory.RowSource = sSubcategorySource
Me.SubCategory.Requery
End Sub

-----------------------
The Row Source for the Category is :
SELECT [category_Query].[Category] FROM category_Query;

The Row Source for the Sub Category is (with IT changing to whatever has
been chosen in the category:
SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] FROM
tblsubcategory WHERE [tblcategoryid] = IT
Click to expand...
Click to expand...
Click to expand...
 
Paul,

You're missing the part about assigning the rowsource.

Private Sub Category_AfterUpdate()

Dim sSubcategorySource as String

sSubcategorySource = "" & _
"SELECT [tblsubcategory].[tblsubcategoryid], " & _
"[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE " & _
"[tblsubcategory].[tblcategoryid] = " & Me.Category

Me.SubCategory.RowSource = sSubcategorySource

End Sub

HTH,
Brian

Paul said:
Thanks Brian, but for some reason my head is still in some jumbled manner and
I still can't get this to work. I've got this bit in the event:

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] = " &
Me.Category
End Sub

Private Sub SubCategory_BeforeUpdate(Cancel As Integer)


End Sub

But I seem to be lost. If I was going to put something in the Row Source
instead of the Sub Category looking up a query or a table, what would it be?

Confusion is just a state of mind and at the moment my mind is a mess :)

Brian Bastl said:
And actually, you don't even need to set the rowsource programmatically. You
could just use the query builder for its rowsource. The SQL for the
SubCategory combo would be:

SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[subcategory]
FROM tblsubcategory
WHERE ((([tblcategoryid]) = Forms!YourForm!Category))

Then you'd requery SubCategory in the Category_AfterUpdate event and your
Form_Current() event

Brian



Brian Bastl said:
the rowsource for "Category" should be more like:

SELECT CategoryID, Category
FROM tblcategory

bound column = 1
column count =2
column widths = 0";1"


the rowsource for "SubCategory" should be more like:

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid], " & _
"[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] =" & Me.Category

HTH,
Brian


I am trying to sort out a sub category, but for some reason I am having
trouble with my answers. I have two tables one called tblcategory and
another
called tblsubcategory. I would like tblsubcategory to only show whatever
is
relevant to what has been chosen from tblcategory. At the moment when I
choose a sub category I get an Enter Parameter window, instead of the
options
which I have available in tblsubcategory.

I have a relationship set up between the two tables - tblcategoryid.

The script below is what I have so far.

Option Compare Database

Private Sub Subcategory_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblcategoryid] = " & Me.Category.Value
Me.SubCategory.RowSource = sSubcategorySource
Me.SubCategory.Requery
End Sub

-----------------------
The Row Source for the Category is :
SELECT [category_Query].[Category] FROM category_Query;

The Row Source for the Sub Category is (with IT changing to whatever has
been chosen in the category:
SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] FROM
tblsubcategory WHERE [tblcategoryid] = IT
Click to expand...
Click to expand...
Click to expand...
 
Brian

Suddenly everything is clear - You're a genius.

Paul

Brian Bastl said:
Paul,

You're missing the part about assigning the rowsource.

Private Sub Category_AfterUpdate()

Dim sSubcategorySource as String

sSubcategorySource = "" & _
"SELECT [tblsubcategory].[tblsubcategoryid], " & _
"[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE " & _
"[tblsubcategory].[tblcategoryid] = " & Me.Category

Me.SubCategory.RowSource = sSubcategorySource

End Sub

HTH,
Brian

Paul said:
Thanks Brian, but for some reason my head is still in some jumbled manner and
I still can't get this to work. I've got this bit in the event:

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] = " &
Me.Category
End Sub

Private Sub SubCategory_BeforeUpdate(Cancel As Integer)


End Sub

But I seem to be lost. If I was going to put something in the Row Source
instead of the Sub Category looking up a query or a table, what would it be?

Confusion is just a state of mind and at the moment my mind is a mess :)

Brian Bastl said:
And actually, you don't even need to set the rowsource programmatically. You
could just use the query builder for its rowsource. The SQL for the
SubCategory combo would be:

SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[subcategory]
FROM tblsubcategory
WHERE ((([tblcategoryid]) = Forms!YourForm!Category))

Then you'd requery SubCategory in the Category_AfterUpdate event and your
Form_Current() event

Brian



the rowsource for "Category" should be more like:

SELECT CategoryID, Category
FROM tblcategory

bound column = 1
column count =2
column widths = 0";1"


the rowsource for "SubCategory" should be more like:

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid], " & _
"[tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblsubcategory].[tblcategoryid] =" & Me.Category

HTH,
Brian


I am trying to sort out a sub category, but for some reason I am having
trouble with my answers. I have two tables one called tblcategory and
another
called tblsubcategory. I would like tblsubcategory to only show whatever
is
relevant to what has been chosen from tblcategory. At the moment when I
choose a sub category I get an Enter Parameter window, instead of the
options
which I have available in tblsubcategory.

I have a relationship set up between the two tables - tblcategoryid.

The script below is what I have so far.

Option Compare Database

Private Sub Subcategory_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Category_AfterUpdate()
Dim sSubcategorySource As String

sSubcategorySource = "SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] " & _
"FROM tblsubcategory " & _
"WHERE [tblcategoryid] = " & Me.Category.Value
Me.SubCategory.RowSource = sSubcategorySource
Me.SubCategory.Requery
End Sub

-----------------------
The Row Source for the Category is :
SELECT [category_Query].[Category] FROM category_Query;

The Row Source for the Sub Category is (with IT changing to whatever has
been chosen in the category:
SELECT [tblsubcategory].[tblsubcategoryid],
[tblsubcategory].[tblcategoryid], [tblsubcategory].[subcategory] FROM
tblsubcategory WHERE [tblcategoryid] = IT
Click to expand...
Click to expand...
Click to expand...
 
Back
Top