Form issue with Link Child and Master Flds

[mattc66 via AccessMonster.com]

Hi All,

I hope I can explain this correct.

I have a sub form based on an unbound combo box. The cbo box is based on a
query grouped of the subform data. The sub form is linked to a fld named
"Region". If the Region fld is blank and the cboBox is blank I want it to
list those records as well. It only list records if the Region fld and the
cboBox have matching words. Why wont it match those records that are blank?

Matt

 

[John W. Vinson]

Hi All,

I hope I can explain this correct.

I have a sub form based on an unbound combo box.

Ummm... No. You don't. A subform must be based on a Query or a Table; a combo
box is a display tool. Do you mean that you're using the combo box as the
Master Link Field? If not, what are the Recordsource, Master and Child Link
Field properties of the subform?

The cbo box is based on a
query grouped of the subform data.

Please post the SQL of the rowsource.

The sub form is linked to a fld named
"Region". If the Region fld is blank and the cboBox is blank I want it to
list those records as well. It only list records if the Region fld and the
cboBox have matching words. Why wont it match those records that are blank?

NULL is a funny beast. NULL means approximately "this value is undefined,
unspecified, unknown". As such NULL is not equal to anything else, not even to
another NULL. It's not even UNEQUAL to another NULL; any comparison -
including a master/child link - from NULL to anything will fail (it's not even
false, the relationship is NULL).

I think you'll need to have a special Region value such as "Region unknown" or
"Unspecified" or "N/A" and use that as the linking value.

John W. Vinson [MVP]

 

[mattc66 via AccessMonster.com]

The subform is based on a Query. The combo box is linked to the Master Link
Field.

SELECT qryARCUST_SalesByStateRegion_C1.Region,
qryARCUST_SalesByStateRegion_C1.TerritoryMgr FROM
qryARCUST_SalesByStateRegion_C1 GROUP BY qryARCUST_SalesByStateRegion_C1.
Region, qryARCUST_SalesByStateRegion_C1.TerritoryMgr;

 

[John W. Vinson]

The subform is based on a Query. The combo box is linked to the Master Link
Field.

What do you mean by "linked to"?

What in fact is the Master Link Field property of the Subform control?

SELECT qryARCUST_SalesByStateRegion_C1.Region,
qryARCUST_SalesByStateRegion_C1.TerritoryMgr FROM
qryARCUST_SalesByStateRegion_C1 GROUP BY qryARCUST_SalesByStateRegion_C1.
Region, qryARCUST_SalesByStateRegion_C1.TerritoryMgr;

Again... if REGION is null, you *cannot* link to a subform. NULL will not link
to NULL, nor will it link to anything else. You will need some *other*
placeholder value in the combo box and in the subform's Region field, not a
NULL value, in order to give you a link.

John W. Vinson [MVP]