looking for strategy for subForm filter

[WDSnews]

I have a parent table named 'Order Request' and a child table named 'Request
Detail'. I want to filter the Request Detail subform. The parent table
tracks the Request ID, People ID, date, etc. The child table is much like a
transaction log in which sometimes the [Item Type ID] field is used when an
Asset is requested. Other times the [Service Type ID] field is used when a
service is requested. In all cases, a Request Detail record uses one field,
but not both. Either the Item Type ID or the Service Type ID will be null.
Most other child fields are used for both types of requests, including cost,
status, etc., except for the Asset ID for Item fulfillment and the Offering
ID for Service fulfillment.

In the future, there's no problem and no need to filter since an Order
Request ID will be dedicated to either services or assets. The problem is
with existing data. An Order Request ID may link both services and items.

My current strategy is to offer two different request forms called Item
Request and Service Request, using the table directly as the data source.
The data source for both parent forms is the Order Request table, and the
data source for both subforms is the Request Detail table. At least that's
my current strategy.

How should I filter Service Type child records from the Item Request form,
and filter Item Type child records from the Service Request form? In both
cases, they can be detected by looking for a null field.

Thanks for your suggestions.

 

[Jeff Boyce]

I'm having trouble visualizing the relationship...

It sounds like the parent is the Request. So wouldn't every child refer
back to a Request? If so, then using a main form (based on the Request) and
a subform (based on the child record/s) should give you a way to keep them
related (via forms).

If you are saying that a request can be a "type of xxxxx" (either Service or
Item), then how is that a problem for the ?parent ... ?child

(as I said, I'm having trouble seeing how Items and Services relate to
Requests (or to the children of requests, whatever they are!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Let me add ...

If you use a main form/subform construction, Access will take care of what I
believe you are describing as "filtering".

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm having trouble visualizing the relationship...

It sounds like the parent is the Request. So wouldn't every child refer
back to a Request? If so, then using a main form (based on the Request)
and a subform (based on the child record/s) should give you a way to keep
them related (via forms).

If you are saying that a request can be a "type of xxxxx" (either Service
or Item), then how is that a problem for the ?parent ... ?child

(as I said, I'm having trouble seeing how Items and Services relate to
Requests (or to the children of requests, whatever they are!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a parent table named 'Order Request' and a child table named
'Request Detail'. I want to filter the Request Detail subform. The
parent table tracks the Request ID, People ID, date, etc. The child table
is much like a transaction log in which sometimes the [Item Type ID] field
is used when an Asset is requested. Other times the [Service Type ID]
field is used when a service is requested. In all cases, a Request Detail
record uses one field, but not both. Either the Item Type ID or the
Service Type ID will be null. Most other child fields are used for both
types of requests, including cost, status, etc., except for the Asset ID
for Item fulfillment and the Offering ID for Service fulfillment.

In the future, there's no problem and no need to filter since an Order
Request ID will be dedicated to either services or assets. The problem
is with existing data. An Order Request ID may link both services and
items.

My current strategy is to offer two different request forms called Item
Request and Service Request, using the table directly as the data source.
The data source for both parent forms is the Order Request table, and the
data source for both subforms is the Request Detail table. At least
that's my current strategy.

How should I filter Service Type child records from the Item Request
form, and filter Item Type child records from the Service Request form?
In both cases, they can be detected by looking for a null field.

Thanks for your suggestions.

 

[WDSnews]

Each asset and each service is listed in a separate record of Request
Detail. The problem is with existing data since the child, called 'Request
Detail' contains multiple records for both services and assets. I'm
wondering how I can create a Service mode in which just services are
viewable, and an asset mode in which the same Request.ID -> Request Detail
reveals only asset records.

I'm having trouble visualizing the relationship...

It sounds like the parent is the Request. So wouldn't every child refer
back to a Request? If so, then using a main form (based on the Request)
and a subform (based on the child record/s) should give you a way to keep
them related (via forms).

If you are saying that a request can be a "type of xxxxx" (either Service
or Item), then how is that a problem for the ?parent ... ?child

(as I said, I'm having trouble seeing how Items and Services relate to
Requests (or to the children of requests, whatever they are!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a parent table named 'Order Request' and a child table named
'Request Detail'. I want to filter the Request Detail subform. The
parent table tracks the Request ID, People ID, date, etc. The child table
is much like a transaction log in which sometimes the [Item Type ID] field
is used when an Asset is requested. Other times the [Service Type ID]
field is used when a service is requested. In all cases, a Request Detail
record uses one field, but not both. Either the Item Type ID or the
Service Type ID will be null. Most other child fields are used for both
types of requests, including cost, status, etc., except for the Asset ID
for Item fulfillment and the Offering ID for Service fulfillment.

In the future, there's no problem and no need to filter since an Order
Request ID will be dedicated to either services or assets. The problem
is with existing data. An Order Request ID may link both services and
items.

My current strategy is to offer two different request forms called Item
Request and Service Request, using the table directly as the data source.
The data source for both parent forms is the Order Request table, and the
data source for both subforms is the Request Detail table. At least
that's my current strategy.

How should I filter Service Type child records from the Item Request
form, and filter Item Type child records from the Service Request form?
In both cases, they can be detected by looking for a null field.

Thanks for your suggestions.

 

[John Spencer]

If you are using two subforms - base the source of the subform on a
query that tests to see if the relevant linking field has a value.

That is ITem Type ID is Not Null or Service Type ID is not null.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[WDSnews]

that works. thank you.

If you are using two subforms - base the source of the subform on a query
that tests to see if the relevant linking field has a value.

That is ITem Type ID is Not Null or Service Type ID is not null.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a parent table named 'Order Request' and a child table named
'Request Detail'. I want to filter the Request Detail subform. The
parent table tracks the Request ID, People ID, date, etc. The child
table is much like a transaction log in which sometimes the [Item Type
ID] field is used when an Asset is requested. Other times the [Service
Type ID] field is used when a service is requested. In all cases, a
Request Detail record uses one field, but not both. Either the Item Type
ID or the Service Type ID will be null. Most other child fields are used
for both types of requests, including cost, status, etc., except for the
Asset ID for Item fulfillment and the Offering ID for Service
fulfillment.

In the future, there's no problem and no need to filter since an Order
Request ID will be dedicated to either services or assets. The problem
is with existing data. An Order Request ID may link both services and
items.

My current strategy is to offer two different request forms called Item
Request and Service Request, using the table directly as the data source.
The data source for both parent forms is the Order Request table, and the
data source for both subforms is the Request Detail table. At least
that's my current strategy.

How should I filter Service Type child records from the Item Request
form, and filter Item Type child records from the Service Request form?
In both cases, they can be detected by looking for a null field.

Thanks for your suggestions.

 

[Andrzej Sagan]

rejestruj a nie bawisz siê ze mn±

that works. thank you.

If you are using two subforms - base the source of the subform on a query
that tests to see if the relevant linking field has a value.

That is ITem Type ID is Not Null or Service Type ID is not null.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a parent table named 'Order Request' and a child table named
'Request Detail'. I want to filter the Request Detail subform. The
parent table tracks the Request ID, People ID, date, etc. The child
table is much like a transaction log in which sometimes the [Item Type
ID] field is used when an Asset is requested. Other times the [Service
Type ID] field is used when a service is requested. In all cases, a
Request Detail record uses one field, but not both. Either the Item
Type ID or the Service Type ID will be null. Most other child fields are
used for both types of requests, including cost, status, etc., except
for the Asset ID for Item fulfillment and the Offering ID for Service
fulfillment.

In the future, there's no problem and no need to filter since an Order
Request ID will be dedicated to either services or assets. The problem
is with existing data. An Order Request ID may link both services and
items.

My current strategy is to offer two different request forms called Item
Request and Service Request, using the table directly as the data
source. The data source for both parent forms is the Order Request
table, and the data source for both subforms is the Request Detail
table. At least that's my current strategy.

How should I filter Service Type child records from the Item Request
form, and filter Item Type child records from the Service Request form?
In both cases, they can be detected by looking for a null field.

Thanks for your suggestions.