Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]![ItemID]

[DoveArrow]

I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want to
create a fourth table where I can store which Items a Person has. Only
a few Items have Sub Items, and those that do have Sub Items only have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.

Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]!
[ItemID]

Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up that
says "Enter Parameter Value for Characters and Skills Table!Skill ID."
Of course, the whole point of telling it to pull the Item ID was so
that I wouldn't have to do this. So my question is, how do I get it to
do what I want it to do? Any advice would be appreciated.

 

[DoveArrow]

I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want to
create a fourth table where I can store which Items a Person has. Only
a few Items have Sub Items, and those that do have Sub Items only have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.

Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]!
[ItemID]

Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up that
says "Enter Parameter Value for Characters and Skills Table!Skill ID."
Of course, the whole point of telling it to pull the Item ID was so
that I wouldn't have to do this. So my question is, how do I get it to
do what I want it to do? Any advice would be appreciated.

Edit: I meant to say:

Select [SubItemID], [SubItem] From Table 3 Where [ItemID] = [Table4]!
[ItemID]

Sorry for the confusion.

 

[scubadiver]

If you want to restrict the number of sub items depending on the item chosen
that is called a cascading combo.

In the first combo you have your list of items.

The second combo you have a query that lists the subitems and a criteria in
the "item" field that references the first combo

[forms]![form name]![combo1]

In the first combo you have a requery in the "after update" event

combo2.requery

 

[DoveArrow]

If you want to restrict the number of sub items depending on the item chosen
that is called a cascading combo.

In the first combo you have your list of items.

The second combo you have a query that lists the subitems and a criteria in
the "item" field that references the first combo

[forms]![form name]![combo1]

In the first combo you have a requery in the "after update" event

combo2.requery

--

The 11th day of every month:

http://truthaction.org/forum/index.php

I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want to
create a fourth table where I can store which Items a Person has. Only
a few Items have Sub Items, and those that do have Sub Items only have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.

Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]!
[ItemID]

Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up that
says "Enter Parameter Value for Characters and Skills Table!Skill ID."
Of course, the whole point of telling it to pull the Item ID was so
that I wouldn't have to do this. So my question is, how do I get it to
do what I want it to do? Any advice would be appreciated.- Hide quoted text -

- Show quoted text -

I've actually tried a variation on the technique you've described. The
problem I'm having is that I'm trying to display this information as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.

 

[DoveArrow]

If you want to restrict the number of sub items depending on the item chosen
that is called a cascading combo.

In the first combo you have your list of items.

The second combo you have a query that lists the subitems and a criteria in
the "item" field that references the first combo

[forms]![form name]![combo1]

In the first combo you have a requery in the "after update" event

combo2.requery

--

The 11th day of every month:

http://truthaction.org/forum/index.php

I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want to
create a fourth table where I can store which Items a Person has. Only
a few Items have Sub Items, and those that do have Sub Items only have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.
Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]!
[ItemID]
Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up that
says "Enter Parameter Value for Characters and Skills Table!Skill ID."
Of course, the whole point of telling it to pull the Item ID was so
that I wouldn't have to do this. So my question is, how do I get it to
do what I want it to do? Any advice would be appreciated.- Hide quoted text -

- Show quoted text -

I've actually tried a variation on the technique you've described. The
problem I'm having is that I'm trying to display this information as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.- Hide quoted text -

- Show quoted text -

I should clarify: It doesn't 'disappear.' The data is still stored in
my base table. However, because the Sub Item isn't listed on the list
for the next item, it is no longer displayed. If I click on it again,
it is once again displayed, but the other Sub Item is no longer
displayed.

 

[scubadiver]

I don't use datasheets (although they do look good). What I would do is
create a continuous form and format it to look like a datasheet.

When you say "two items" you mean two records?

--

The 11th day of every month:

http://truthaction.org/forum/index.php

If you want to restrict the number of sub items depending on the item chosen
that is called a cascading combo.

In the first combo you have your list of items.

The second combo you have a query that lists the subitems and a criteria in
the "item" field that references the first combo

[forms]![form name]![combo1]

In the first combo you have a requery in the "after update" event


The 11th day of every month:

:
I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want to
create a fourth table where I can store which Items a Person has. Only
a few Items have Sub Items, and those that do have Sub Items only have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.

Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]!
[ItemID]

Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up that
says "Enter Parameter Value for Characters and Skills Table!Skill ID."
Of course, the whole point of telling it to pull the Item ID was so
that I wouldn't have to do this. So my question is, how do I get it to
do what I want it to do? Any advice would be appreciated.- Hide quoted text -

- Show quoted text -

I've actually tried a variation on the technique you've described. The
problem I'm having is that I'm trying to display this information as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.- Hide quoted text -

- Show quoted text -

I should clarify: It doesn't 'disappear.' The data is still stored in
my base table. However, because the Sub Item isn't listed on the list
for the next item, it is no longer displayed. If I click on it again,
it is once again displayed, but the other Sub Item is no longer
displayed.

 

[BruceM]

How are Persons, Items, and Sub-Items related? Specifically, can a person
be associated with many items, and an item with many persons? A few
examples or Items and Sub-Items may help.
One thing of which I can be fairly certain is that you need a Sub-Item table
related to the Item table (each item may have several sub-items). A related
question is whether a sub-item can be assoicated with several different
items. Again, examples would make the problem more accessible.
I understand that each item may have several associated sub-items. There
may be two different issues here. When you select an item, you want the
list of sub-items restricted to those associated with that item. This is
the cascading combo box idea to which scubadiver referred.
The other issue is display. If you have selected several sub-items, when
you view the Item record you want to see those sub-items. A Single view of
the Items form is necessary if there is to be a subform. A continuous form
or datasheet view of a form cannot have a subform, as far as I know.
Another point here is that you are using lookup fields in tables. For a
variety of reasons these should be avoided. More here:
http://www.mvps.org/access/lookupfields.htm
Tables are for storing data. They should not be used to add and edit data.
Users should interact with the data through use of forms. If you get rid of
your lookup fields (which are very different from lookup tables, by the
way), and set up forms for data entry, the cascading combo box issue and the
display issues can be solved readily enough.

If you want to restrict the number of sub items depending on the item
chosen
that is called a cascading combo.

In the first combo you have your list of items.

The second combo you have a query that lists the subitems and a
criteria in
the "item" field that references the first combo

[forms]![form name]![combo1]

In the first combo you have a requery in the "after update" event


The 11th day of every month:

:
I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want to
create a fourth table where I can store which Items a Person has.
Only
a few Items have Sub Items, and those that do have Sub Items only
have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.

Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]!
[ItemID]

Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up that
says "Enter Parameter Value for Characters and Skills Table!Skill
ID."
Of course, the whole point of telling it to pull the Item ID was so
that I wouldn't have to do this. So my question is, how do I get it
to
do what I want it to do? Any advice would be appreciated.- Hide
quoted text -

- Show quoted text -

I've actually tried a variation on the technique you've described. The
problem I'm having is that I'm trying to display this information as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.- Hide quoted text -

- Show quoted text -

I should clarify: It doesn't 'disappear.' The data is still stored in
my base table. However, because the Sub Item isn't listed on the list
for the next item, it is no longer displayed. If I click on it again,
it is once again displayed, but the other Sub Item is no longer
displayed.

 

[DoveArrow]

How are Persons, Items, and Sub-Items related? Specifically, can a person
be associated with many items, and an item with many persons? A few
examples or Items and Sub-Items may help.
One thing of which I can be fairly certain is that you need a Sub-Item table
related to the Item table (each item may have several sub-items). A related
question is whether a sub-item can be assoicated with several different
items. Again, examples would make the problem more accessible.
I understand that each item may have several associated sub-items. There
may be two different issues here. When you select an item, you want the
list of sub-items restricted to those associated with that item. This is
the cascading combo box idea to which scubadiver referred.
The other issue is display. If you have selected several sub-items, when
you view the Item record you want to see those sub-items. A Single view of
the Items form is necessary if there is to be a subform. A continuous form
or datasheet view of a form cannot have a subform, as far as I know.
Another point here is that you are using lookup fields in tables. For a
variety of reasons these should be avoided. More here:http://www.mvps.org/access/lookupfields.htm
Tables are for storing data. They should not be used to add and edit data.
Users should interact with the data through use of forms. If you get rid of
your lookup fields (which are very different from lookup tables, by the
way), and set up forms for data entry, the cascading combo box issue and the
display issues can be solved readily enough.

On Aug 21, 3:20 am, scubadiver <[email protected]>
wrote:
If you want to restrict the number of sub items depending on the item
chosen
that is called a cascading combo.
In the first combo you have your list of items.
The second combo you have a query that lists the subitems and a
criteria in
the "item" field that references the first combo
[forms]![form name]![combo1]
In the first combo you have a requery in the "after update" event
combo2.requery
--
The 11th day of every month:
http://truthaction.org/forum/index.php
:
I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want to
create a fourth table where I can store which Items a Person has.
Only
a few Items have Sub Items, and those that do have Sub Items only
have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.
Select [SubItemID], [SubItem] From Table2 Where [ItemID] = [Table4]!
[ItemID]
Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up that
says "Enter Parameter Value for Characters and Skills Table!Skill
ID."
Of course, the whole point of telling it to pull the Item ID was so
that I wouldn't have to do this. So my question is, how do I get it
to
do what I want it to do? Any advice would be appreciated.- Hide
quoted text -
- Show quoted text -
I've actually tried a variation on the technique you've described. The
problem I'm having is that I'm trying to display this information as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.- Hide quoted text -
- Show quoted text -

I should clarify: It doesn't 'disappear.' The data is still stored in
my base table. However, because the Sub Item isn't listed on the list
for the next item, it is no longer displayed. If I click on it again,
it is once again displayed, but the other Sub Item is no longer
displayed.- Hide quoted text -

- Show quoted text -

Well here's what finally worked. I'm not sure if it's a happy glitch,
or if it's really supposed to work this way, but here's what I wrote
that made it so that the SubItems for all Items remain visible even
when they're not on the list. Anyone's thoughts on why this is working
would be appreciated.

Private Sub Form_Current()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]"
Me.[SubItem].Requery
End Sub

Private Sub Item_Change()
Me.[SubItem] = Null
Me.SubItem.Requery
End Sub


Private Sub SubItem_GotFocus()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item].Value
Me.[SubItem].Requery
End Sub

 

[BruceM]

Comments inline.

How are Persons, Items, and Sub-Items related? Specifically, can a
person
be associated with many items, and an item with many persons? A few
examples or Items and Sub-Items may help.
One thing of which I can be fairly certain is that you need a Sub-Item
table
related to the Item table (each item may have several sub-items). A
related
question is whether a sub-item can be assoicated with several different
items. Again, examples would make the problem more accessible.
I understand that each item may have several associated sub-items. There
may be two different issues here. When you select an item, you want the
list of sub-items restricted to those associated with that item. This is
the cascading combo box idea to which scubadiver referred.
The other issue is display. If you have selected several sub-items, when
you view the Item record you want to see those sub-items. A Single view
of
the Items form is necessary if there is to be a subform. A continuous
form
or datasheet view of a form cannot have a subform, as far as I know.
Another point here is that you are using lookup fields in tables. For a
variety of reasons these should be avoided. More
here:http://www.mvps.org/access/lookupfields.htm
Tables are for storing data. They should not be used to add and edit
data.
Users should interact with the data through use of forms. If you get rid
of
your lookup fields (which are very different from lookup tables, by the
way), and set up forms for data entry, the cascading combo box issue and
the
display issues can be solved readily enough.

On Aug 21, 3:20 am, scubadiver <[email protected]>
wrote:

If you want to restrict the number of sub items depending on the
item
chosen
that is called a cascading combo.

In the first combo you have your list of items.

The second combo you have a query that lists the subitems and a
criteria in
the "item" field that references the first combo

[forms]![form name]![combo1]

In the first combo you have a requery in the "after update" event


The 11th day of every month:

:
I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want
to
create a fourth table where I can store which Items a Person has.
Only
a few Items have Sub Items, and those that do have Sub Items only
have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore
went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.

Select [SubItemID], [SubItem] From Table2 Where [ItemID] =
[Table4]!
[ItemID]

Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up
that
says "Enter Parameter Value for Characters and Skills Table!Skill
ID."
Of course, the whole point of telling it to pull the Item ID was
so
that I wouldn't have to do this. So my question is, how do I get
it
to
do what I want it to do? Any advice would be appreciated.- Hide
quoted text -

- Show quoted text -

I've actually tried a variation on the technique you've described. The
problem I'm having is that I'm trying to display this information as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.- Hide quoted
text -

- Show quoted text -

I should clarify: It doesn't 'disappear.' The data is still stored in
my base table. However, because the Sub Item isn't listed on the list
for the next item, it is no longer displayed. If I click on it again,
it is once again displayed, but the other Sub Item is no longer
displayed.- Hide quoted text -

- Show quoted text -

Well here's what finally worked. I'm not sure if it's a happy glitch,
or if it's really supposed to work this way, but here's what I wrote
that made it so that the SubItems for all Items remain visible even
when they're not on the list. Anyone's thoughts on why this is working
would be appreciated.

Private Sub Form_Current()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]"
Me.[SubItem].Requery
End Sub

There is probably no need for SubItemID in the Row Source query. I can see
no need to requery in the form's Current event. The Row Source is
establised in the previous lines of code. Nothing happens to change the Row
Source before the requery, so requerying will have no effect.

Private Sub Item_Change()
Me.[SubItem] = Null
Me.SubItem.Requery
End Sub

This event runs every time you change the information in Item (which I
assume is a combo box). This can happen if you select something from the
combo box list, or after each character you enter if you are typing
something into the combo box. If you mean to set the Row Source for SubItem
based on the selection in Item you would do better to use the After Update
event, although I suppose it doesn't matter if the combo box Limit To List
property is set to Yes. I'm not sure of that, but I do know that the After
Update event makes good sense no matter the Limit To List property. In any
case, I can see no need to set SubItem to Null.

Private Sub SubItem_GotFocus()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item].Value
Me.[SubItem].Requery
End Sub

This should work as long as the user makes a selection before clicking into
SubItem, but if Item is null (no user selection) this won't work as intended
(if it works at all). It's probably not the best place to set the Row
Source. Again, no need to requery immediately after setting the Row Source.

Here's what you have. You set the SubItem Row Source to all records when
you first move to a record (either an existing record or a new one). That
is when the form's Current event runs. Even if something is selected in
Item for an existing record, SubItem will show you an unfiltered list. If
you intend always to limit the SubItem list to records where ItemID = the
form's current ItemID you could use something like this in the SubItem Row
Source:

"SELECT [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item]"

You could also use a named query. There is no need to use the Value
property for the combo box Item. Value is the default property. There is
no harm to using it, but there is no benefit in this case. In the
AfterUpdate event for Item you could just requery SubItem.

 

[DoveArrow]

Comments inline.

How are Persons, Items, and Sub-Items related? Specifically, can a
person
be associated with many items, and an item with many persons? A few
examples or Items and Sub-Items may help.
One thing of which I can be fairly certain is that you need a Sub-Item
table
related to the Item table (each item may have several sub-items). A
related
question is whether a sub-item can be assoicated with several different
items. Again, examples would make the problem more accessible.
I understand that each item may have several associated sub-items. There
may be two different issues here. When you select an item, you want the
list of sub-items restricted to those associated with that item. This is
the cascading combo box idea to which scubadiver referred.
The other issue is display. If you have selected several sub-items, when
you view the Item record you want to see those sub-items. A Single view
of
the Items form is necessary if there is to be a subform. A continuous
form
or datasheet view of a form cannot have a subform, as far as I know.
Another point here is that you are using lookup fields in tables. For a
variety of reasons these should be avoided. More
here:http://www.mvps.org/access/lookupfields.htm
Tables are for storing data. They should not be used to add and edit
data.
Users should interact with the data through use of forms. If you get rid
of
your lookup fields (which are very different from lookup tables, by the
way), and set up forms for data entry, the cascading combo box issue and
the
display issues can be solved readily enough.

On Aug 21, 3:20 am, scubadiver <[email protected]>
wrote:
If you want to restrict the number of sub items depending on the
item
chosen
that is called a cascading combo.
In the first combo you have your list of items.
The second combo you have a query that lists the subitems and a
criteria in
the "item" field that references the first combo
[forms]![form name]![combo1]
In the first combo you have a requery in the "after update" event
combo2.requery
--
The 11th day of every month:
http://truthaction.org/forum/index.php
:
I'm sure that you've probably guessed why I'm here. I have three
tables, a Person Table, an Item Table and a Sub Item Table. I want
to
create a fourth table where I can store which Items a Person has.
Only
a few Items have Sub Items, and those that do have Sub Items only
have
a few from the list. Because of this, I want a ComboBox that will
select only the Sub Items that apply to each item. I therefore
went
into the Design View of the Table, clicked on the Lookup Tab, and
entered the following statement for the row source.
Select [SubItemID], [SubItem] From Table2 Where [ItemID] =
[Table4]!
[ItemID]
Unfortunately, this doesn't work the way I want it to. Instead of
pulling the Item ID from my fourth table, a message box pops up
that
says "Enter Parameter Value for Characters and Skills Table!Skill
ID."
Of course, the whole point of telling it to pull the Item ID was
so
that I wouldn't have to do this. So my question is, how do I get
it
to
do what I want it to do? Any advice would be appreciated.- Hide
quoted text -
- Show quoted text -
I've actually tried a variation on the technique you've described. The
problem I'm having is that I'm trying to display this information as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.- Hide quoted
text -
- Show quoted text -
I should clarify: It doesn't 'disappear.' The data is still stored in
my base table. However, because the Sub Item isn't listed on the list
for the next item, it is no longer displayed. If I click on it again,
it is once again displayed, but the other Sub Item is no longer
displayed.- Hide quoted text -
- Show quoted text -

Well here's what finally worked. I'm not sure if it's a happy glitch,
or if it's really supposed to work this way, but here's what I wrote
that made it so that the SubItems for all Items remain visible even
when they're not on the list. Anyone's thoughts on why this is working
would be appreciated.

Private Sub Form_Current()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]"
Me.[SubItem].Requery
End Sub

There is probably no need for SubItemID in the Row Source query. I can see
no need to requery in the form's Current event. The Row Source is
establised in the previous lines of code. Nothing happens to change the Row
Source before the requery, so requerying will have no effect.

Private Sub Item_Change()
Me.[SubItem] = Null
Me.SubItem.Requery
End Sub

This event runs every time you change the information in Item (which I
assume is a combo box). This can happen if you select something from the
combo box list, or after each character you enter if you are typing
something into the combo box. If you mean to set the Row Source for SubItem
based on the selection in Item you would do better to use the After Update
event, although I suppose it doesn't matter if the combo box Limit To List
property is set to Yes. I'm not sure of that, but I do know that the After
Update event makes good sense no matter the Limit To List property. In any
case, I can see no need to set SubItem to Null.

Private Sub SubItem_GotFocus()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item].Value
Me.[SubItem].Requery
End Sub

This should work as long as the user makes a selection before clicking into
SubItem, but if Item is null (no user selection) this won't work as intended
(if it works at all). It's probably not the best place to set the Row
Source. Again, no need to requery immediately after setting the Row Source.

Here's what you have. You set the SubItem Row Source to all records when
you first move to a record (either an existing record or a new one). That
is when the form's Current event runs. Even if something is selected in
Item for an existing record, SubItem will show you an unfiltered list. If
you intend always to limit the SubItem list to records where ItemID = the
form's current ItemID you could use something like this in the SubItem Row
Source:

"SELECT [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item]"

You could also use a named query. There is no need to use the Value
property for the combo box Item. Value is the default property. There is
no harm to using it, but there is no benefit in this case. In the
AfterUpdate event for Item you could just requery SubItem.- Hide quoted text -

- Show quoted text -

"There is probably no need for SubItemID in the Row Source query."
I'm confused by this. SubItem ID is the data that I'm trying to pull
in my query. Why wouldn't I need it?

"I can see no need to requery in the form's Current event. The Row
Source is establised in the previous lines of code."

Good point. I'll edit that part out.

"This event runs every time you change the information in Item (which
I assume is a combo box). This can happen if you select something
from the combo box list, or after each character you enter if you are
typing something into the combo box."

This actually explains a problem I'm having with another combo box.
I'm glad you mentioned it. I will definitely change it.

"In any case, I can see no need to set SubItem to Null."

The reason I have it setting the SubItem to Null is because I was
having the problem of Sub Items getting attached to Items that they
shouldn't be attached to. I should probably be setting it to Empty,
though. Setting it to Null is sort've a bad habit I got into, mostly
because I didn't know any better when I first started fiddling with
Visual Basic.

"Here's what you have. You set the SubItem Row Source to all records
when you first move to a record (either an existing record or a new
one). That is when the form's Current event runs. Even if something
is selected in Item for an existing record, SubItem will show you an
unfiltered list."

On this point, I'm not sure if I understand you correctly, because it
sounds like you think that this is a problem, so let me just back up a
little bit and reiterate my original point.

I want a form, that can be displayed in Datasheet View, that has a
cascading combo box which will filter only the SubItems that can be
selected for a particular Item. What I do not want is to have all of
the SubItems already selected to disappear if they don't appear on the
current, filtered list in my cascading combo box. Right now, my form
is everything I want it to. What I don't understand is why. Is it a
happy glitch, or did I stumble onto something that's really brilliant,
and I'm just too stupid to figure out what it is?

Now perhaps you're explaining to me why it's working. If that is the
case, then I don't understand your explanation. If you think that it's
just bad programming, believe me, I know I need all the criticism in
the world, but I don't want to 'fix' it and then come to find that
it's gone back to making all of my Sub Items disappear whenever I move
to a new Item.

"If you intend always to limit the SubItem list to records where
ItemID = the
form's current ItemID you could use something like this in the SubItem
Row
Source:


"SELECT [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item]" "

As elegant as it sounds, this is the exact line of code that made me
want to run up walls, screaming "I AM A FISH! I AM A FISH!" because it
wouldn't displaying my Sub Items the way I wanted it to.

"You could also use a named query."

What's a named query? I've never heard this term before and after
doing a quick Google search, and I couldn't find anything for
Microsoft Access or Visual Basic that made reference to the term
(although I did find stuff for Java and SQL, which made no sense to
me).

"There is no need to use the Value property for the combo box Item.
Value is the default property. There is no harm to using it, but
there is no benefit in this case."

Fair point.

"In the AfterUpdate event for Item you could just requery SubItem."

If I understand you correctly, you're saying that I can write a line
of code that looks like this:

Private Sub SubItem_AfterUpdate()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]"


That sounds great, in theory. However, I've found that if you don't
actually update the field, it doesn't do anything. Also, if the focus
is still on the field after you've updated, it doesn't do anything. So
in essence, it really doesn't do anything.

 

[BruceM]

Comments inline.

How are Persons, Items, and Sub-Items related? Specifically, can a
person
be associated with many items, and an item with many persons? A few
examples or Items and Sub-Items may help.
One thing of which I can be fairly certain is that you need a Sub-Item
table
related to the Item table (each item may have several sub-items). A
related
question is whether a sub-item can be assoicated with several
different
items. Again, examples would make the problem more accessible.
I understand that each item may have several associated sub-items.
There
may be two different issues here. When you select an item, you want
the
list of sub-items restricted to those associated with that item. This
is
the cascading combo box idea to which scubadiver referred.
The other issue is display. If you have selected several sub-items,
when
you view the Item record you want to see those sub-items. A Single
view
of
the Items form is necessary if there is to be a subform. A continuous
form
or datasheet view of a form cannot have a subform, as far as I know.
Another point here is that you are using lookup fields in tables. For
a
variety of reasons these should be avoided. More
here:http://www.mvps.org/access/lookupfields.htm
Tables are for storing data. They should not be used to add and edit
data.
Users should interact with the data through use of forms. If you get
rid
of
your lookup fields (which are very different from lookup tables, by
the
way), and set up forms for data entry, the cascading combo box issue
and
the
display issues can be solved readily enough.

On Aug 21, 3:20 am, scubadiver
<[email protected]>
wrote:

If you want to restrict the number of sub items depending on the
item
chosen
that is called a cascading combo.

In the first combo you have your list of items.

The second combo you have a query that lists the subitems and a
criteria in
the "item" field that references the first combo

[forms]![form name]![combo1]

In the first combo you have a requery in the "after update" event


The 11th day of every month:

:
I'm sure that you've probably guessed why I'm here. I have
three
tables, a Person Table, an Item Table and a Sub Item Table. I
want
to
create a fourth table where I can store which Items a Person
has.
Only
a few Items have Sub Items, and those that do have Sub Items
only
have
a few from the list. Because of this, I want a ComboBox that
will
select only the Sub Items that apply to each item. I therefore
went
into the Design View of the Table, clicked on the Lookup Tab,
and
entered the following statement for the row source.

Select [SubItemID], [SubItem] From Table2 Where [ItemID] =
[Table4]!
[ItemID]

Unfortunately, this doesn't work the way I want it to. Instead
of
pulling the Item ID from my fourth table, a message box pops up
that
says "Enter Parameter Value for Characters and Skills
Table!Skill
ID."
Of course, the whole point of telling it to pull the Item ID
was
so
that I wouldn't have to do this. So my question is, how do I
get
it
to
do what I want it to do? Any advice would be appreciated.- Hide
quoted text -

- Show quoted text -

I've actually tried a variation on the technique you've described.
The
problem I'm having is that I'm trying to display this information
as a
datasheet, and if I have two items on my datasheet, each with a
different list of Sub Items, the text for the first Sub Item
disappears the moment that I click on the next one.- Hide quoted
text -

- Show quoted text -

I should clarify: It doesn't 'disappear.' The data is still stored
in
my base table. However, because the Sub Item isn't listed on the
list
for the next item, it is no longer displayed. If I click on it
again,
it is once again displayed, but the other Sub Item is no longer
displayed.- Hide quoted text -

- Show quoted text -

Well here's what finally worked. I'm not sure if it's a happy glitch,
or if it's really supposed to work this way, but here's what I wrote
that made it so that the SubItems for all Items remain visible even
when they're not on the list. Anyone's thoughts on why this is working
would be appreciated.

Private Sub Form_Current()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]"
Me.[SubItem].Requery
End Sub

There is probably no need for SubItemID in the Row Source query. I can
see
no need to requery in the form's Current event. The Row Source is
establised in the previous lines of code. Nothing happens to change the
Row
Source before the requery, so requerying will have no effect.

Private Sub Item_Change()
Me.[SubItem] = Null
Me.SubItem.Requery
End Sub

This event runs every time you change the information in Item (which I
assume is a combo box). This can happen if you select something from the
combo box list, or after each character you enter if you are typing
something into the combo box. If you mean to set the Row Source for
SubItem
based on the selection in Item you would do better to use the After
Update
event, although I suppose it doesn't matter if the combo box Limit To
List
property is set to Yes. I'm not sure of that, but I do know that the
After
Update event makes good sense no matter the Limit To List property. In
any
case, I can see no need to set SubItem to Null.

Private Sub SubItem_GotFocus()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[SubItem ID]," &
_
" [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item].Value
Me.[SubItem].Requery
End Sub

This should work as long as the user makes a selection before clicking
into
SubItem, but if Item is null (no user selection) this won't work as
intended
(if it works at all). It's probably not the best place to set the Row
Source. Again, no need to requery immediately after setting the Row
Source.

Here's what you have. You set the SubItem Row Source to all records when
you first move to a record (either an existing record or a new one).
That
is when the form's Current event runs. Even if something is selected in
Item for an existing record, SubItem will show you an unfiltered list.
If
you intend always to limit the SubItem list to records where ItemID = the
form's current ItemID you could use something like this in the SubItem
Row
Source:

"SELECT [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item]"

You could also use a named query. There is no need to use the Value
property for the combo box Item. Value is the default property. There
is
no harm to using it, but there is no benefit in this case. In the
AfterUpdate event for Item you could just requery SubItem.- Hide quoted
text -

- Show quoted text -

"There is probably no need for SubItemID in the Row Source query."
I'm confused by this. SubItem ID is the data that I'm trying to pull
in my query. Why wouldn't I need it?

I'm having trouble keeping track of just what has been said and explained.
I thought you were storing ItemID. If you are storing SubItemID then it
definitely needs to be in the Row Source. However, in that case I would
think ItemID is not needed in the Row Source. The Row Source consists of
fields that you need to see or use. This includes the field or fields that
you see in the combo box list, and the field that is being stored. It also
includes hidden columns that you will be displaying in separate controls, or
that you need to refer to in code.

"I can see no need to requery in the form's Current event. The Row
Source is establised in the previous lines of code."

Good point. I'll edit that part out.

"This event runs every time you change the information in Item (which
I assume is a combo box). This can happen if you select something
from the combo box list, or after each character you enter if you are
typing something into the combo box."

This actually explains a problem I'm having with another combo box.
I'm glad you mentioned it. I will definitely change it.

"In any case, I can see no need to set SubItem to Null."

The reason I have it setting the SubItem to Null is because I was
having the problem of Sub Items getting attached to Items that they
shouldn't be attached to. I should probably be setting it to Empty,
though. Setting it to Null is sort've a bad habit I got into, mostly
because I didn't know any better when I first started fiddling with
Visual Basic.

I may have misunderstood. You are clearing the current value that is
displayed before you requery? I wouldn't have thought it was necessary, but
maybe I'm not getting the whole picture. If by Empty you mean an empty
string, you could probably do that, but if there is a need to clear the
combo box, Null should work, I think.

"Here's what you have. You set the SubItem Row Source to all records
when you first move to a record (either an existing record or a new
one). That is when the form's Current event runs. Even if something
is selected in Item for an existing record, SubItem will show you an
unfiltered list."

On this point, I'm not sure if I understand you correctly, because it
sounds like you think that this is a problem, so let me just back up a
little bit and reiterate my original point.

I want a form, that can be displayed in Datasheet View, that has a
cascading combo box which will filter only the SubItems that can be
selected for a particular Item. What I do not want is to have all of
the SubItems already selected to disappear if they don't appear on the
current, filtered list in my cascading combo box. Right now, my form
is everything I want it to. What I don't understand is why. Is it a
happy glitch, or did I stumble onto something that's really brilliant,
and I'm just too stupid to figure out what it is?

Can an item have several sub-items? If so, are sub-items stored in a
related table? I have been assuming something of the sort. In my first
posting in this thread I asked:
"How are Persons, Items, and Sub-Items related? Specifically, can a person
be associated with many items, and an item with many persons? A few
examples or Items and Sub-Items may help."
In the absence of specifics I have been making some assumptions and guesses.

Now perhaps you're explaining to me why it's working. If that is the
case, then I don't understand your explanation. If you think that it's
just bad programming, believe me, I know I need all the criticism in
the world, but I don't want to 'fix' it and then come to find that
it's gone back to making all of my Sub Items disappear whenever I move
to a new Item.

"If you intend always to limit the SubItem list to records where
ItemID = the
form's current ItemID you could use something like this in the SubItem
Row
Source:


"SELECT [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]" & _
"WHERE [Item ID] = " & Me.[Item]" "

As elegant as it sounds, this is the exact line of code that made me
want to run up walls, screaming "I AM A FISH! I AM A FISH!" because it
wouldn't displaying my Sub Items the way I wanted it to.

How did you want them displayed? Again, I am assuming sub-items are in a
table that is related to Items, but that is an assumption based on common
practice rather than a statement based on an understanding of your project.
If your database is structured other than as I imagine then I will need a
clearer understanding of that structure.

"You could also use a named query."

What's a named query? I've never heard this term before and after
doing a quick Google search, and I couldn't find anything for
Microsoft Access or Visual Basic that made reference to the term
(although I did find stuff for Java and SQL, which made no sense to
me).

I just meant that if you go to the Queries tab and choose to create a query,
when you are done you can name the query. For instance, if you have an
employee table your query may be something like:
SELECT [EmployeeID], [FirstName] & " " & [LastName] AS FullName
FROM tblEmployee
ORDER BY [LastName], [FirstName];

If you save the query (File > Save) you will be prompted for a name. Let's
say you call it qryRoster. Now you can just use qryRoster as the combo box
Row Source any time you need a drop-down list of employees.

"There is no need to use the Value property for the combo box Item.
Value is the default property. There is no harm to using it, but
there is no benefit in this case."

Fair point.

"In the AfterUpdate event for Item you could just requery SubItem."

If I understand you correctly, you're saying that I can write a line
of code that looks like this:

Private Sub SubItem_AfterUpdate()
Me.[SubItem].RowSource = "SELECT [SubItem Table].[Item ID]," & _
" [SubItem Table].[SubItem] " & _
"FROM [SubItem Table]"


That sounds great, in theory. However, I've found that if you don't
actually update the field, it doesn't do anything. Also, if the focus
is still on the field after you've updated, it doesn't do anything. So
in essence, it really doesn't do anything.

After Update runs as soon as you make a selection in the combo box. At that
point the combo box is updated, and any After Update code runs.

You are using the After Update code of SubItem to set the SubItem Row
Source. I can't think of a reason for doing that. Do you mean to go back
and select from the list again? After making a selection from the combo
box, the After Update code runs, which sets the Row Source for the combo box
from which you just made the selection.