How to filter a child field combo box?

[WDSnews]

I have an Orders form with a subForm. The subform is bound to the ODetails
table. The ODetails table has an ID field and two parents; namely the
Orders table and an ItemTypes table. Each ODetails record points to both
parents with an OrdersID field and an ItemTypesID field. It has a fourth
field called AssetID; yep, pointing to a table called Assets. The Assets
table also contains an ItemTypesID field and is also a child of ItemTypes.
(Summary: ODetails has four fields; ID, OrdersID, ItemTypesID, and AssetID.)

The ODetails records were previously filled in with ItemTypesID and
OrdersID. However my fullfillment team needs to fill in the AssetID field
of ODetails.

I want my users to have the ability to pick an asset off a short list. My
goal is for the ODetails subform to contain an AssetID combobox, whose list
is limited to the assets which match the ItemTypesID. Is this a query based
combobox? How can I build a combobox query which references another field
of its own record?

I know this seems backwards, but there's a good reason for one step of the
work flow to specify an ItemType, and a later step to choose the specific
Asset.

 

[Arvin Meyer MVP]

You simply need to use the ItemTypeID as a criteria in the query for the
AssetID rowsource

In the criteria box, enter:

[Forms]![YourFormName]![cboItemTypeID]

substituting the correct names for the form and combo.

Then in the GotFocus event of AssetID combo, requery. (aircode)

Sub cboAssetID_GotFocus()
Me.cboAssetID.Requery
End Sub

A similar example for a single form is at:

http://www.accessmvp.com/Arvin/Combo.zip

 

[WDSnews]

Thank you. This helped me immensely as I try to convert to Access from
ObjectPal. Afterward, I saw Ken's message where he explains the problem of
disappearing data as each source row surrenders to the next. I had
struggled with this problem for many hours before coming back to the forum
and discovering his explanation. As he points out, the data still exists in
the table. It just doesn't show up on the form.

That seems like an unacceptable solution. While we data-guys understand it,
the users simply won't.

The tool I had in ObjectPal that seems missing from Access is an event in
the event model that allows each child record to 'arrive' and execute a bit
of code. I've asked others about this, but they didn't know how child
records could execute code as they arrive on screen.

Do you know how an event could be triggered by each arriving child record?

You simply need to use the ItemTypeID as a criteria in the query for the
AssetID rowsource

In the criteria box, enter:

[Forms]![YourFormName]![cboItemTypeID]

substituting the correct names for the form and combo.

Then in the GotFocus event of AssetID combo, requery. (aircode)

Sub cboAssetID_GotFocus()
Me.cboAssetID.Requery
End Sub

A similar example for a single form is at:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an Orders form with a subForm. The subform is bound to the
ODetails table. The ODetails table has an ID field and two parents;
namely the Orders table and an ItemTypes table. Each ODetails record
points to both parents with an OrdersID field and an ItemTypesID field.
It has a fourth field called AssetID; yep, pointing to a table called
Assets. The Assets table also contains an ItemTypesID field and is also a
child of ItemTypes. (Summary: ODetails has four fields; ID, OrdersID,
ItemTypesID, and AssetID.)

The ODetails records were previously filled in with ItemTypesID and
OrdersID. However my fullfillment team needs to fill in the AssetID
field of ODetails.

I want my users to have the ability to pick an asset off a short list.
My goal is for the ODetails subform to contain an AssetID combobox, whose
list is limited to the assets which match the ItemTypesID. Is this a
query based combobox? How can I build a combobox query which references
another field of its own record?

I know this seems backwards, but there's a good reason for one step of
the work flow to specify an ItemType, and a later step to choose the
specific Asset.

 

[WDSnews]

Ken, thank you for the explanation. I'll play with it for awhile.

 

[John W. Vinson]

Do you know how an event could be triggered by each arriving child record?

Try the form's Current event.

 

[kate]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> a écrit dans le
message de groupe de discussion :
(e-mail address removed)...

 

[kate]

Ken, thank you for the explanation. I'll play with it for awhile.

 

[kate]

Thank you. This helped me immensely as I try to convert to Access from
ObjectPal. Afterward, I saw Ken's message where he explains the problem
of disappearing data as each source row surrenders to the next. I had
struggled with this problem for many hours before coming back to the forum
and discovering his explanation. As he points out, the data still exists
in the table. It just doesn't show up on the form.

That seems like an unacceptable solution. While we data-guys understand
it, the users simply won't.

The tool I had in ObjectPal that seems missing from Access is an event in
the event model that allows each child record to 'arrive' and execute a
bit of code. I've asked others about this, but they didn't know how child
records could execute code as they arrive on screen.

Do you know how an event could be triggered by each arriving child record?

You simply need to use the ItemTypeID as a criteria in the query for the
AssetID rowsource

In the criteria box, enter:

[Forms]![YourFormName]![cboItemTypeID]

substituting the correct names for the form and combo.

Then in the GotFocus event of AssetID combo, requery. (aircode)

Sub cboAssetID_GotFocus()
Me.cboAssetID.Requery
End Sub

A similar example for a single form is at:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an Orders form with a subForm. The subform is bound to the
ODetails table. The ODetails table has an ID field and two parents;
namely the Orders table and an ItemTypes table. Each ODetails record
points to both parents with an OrdersID field and an ItemTypesID field.
It has a fourth field called AssetID; yep, pointing to a table called
Assets. The Assets table also contains an ItemTypesID field and is also
a child of ItemTypes. (Summary: ODetails has four fields; ID, OrdersID,
ItemTypesID, and AssetID.)

The ODetails records were previously filled in with ItemTypesID and
OrdersID. However my fullfillment team needs to fill in the AssetID
field of ODetails.

I want my users to have the ability to pick an asset off a short list.
My goal is for the ODetails subform to contain an AssetID combobox,
whose list is limited to the assets which match the ItemTypesID. Is
this a query based combobox? How can I build a combobox query which
references another field of its own record?

I know this seems backwards, but there's a good reason for one step of
the work flow to specify an ItemType, and a later step to choose the
specific Asset.