I thought there might be a way to do this with a query, but haven't found one. I have a subdatasheet on a form that displays only when the user clicks the "+" button to expand. The problem is, the table the datasheet is based on contains many fields, and many records contain nulls for multiple fields. I would like the expanded datasheet to be filtered so that it only displays fields that do not contain nulls. I'm not sure if there's any way to do this, but if so it would be nice because the subdatasheet is large (too big to fit on the form without scroll bars). A dynamic filter that, for a given record, will hide fields that are null would be very helpful. It needs to be dynamic because different records contain nulls for different fields. Make sense?

?can i just confirm what you are asking?

You want to be able to dynamically hide fields that are null from the user on a subform? and the subform is in datasheet view?

If i am right then say i have 2 fields and 3 records

1,2,null
3,null,4

So in your model i would see

1,2
3,4

is this what you are after?

It would only be possible in form view for the subform as that allows only one record to be viewed at one time. In this way we could check each field and remove those we dont want the user to see. but in datasheet and continuous view this just isn't possible.

Haz

Actually, I would want to display the nulls in your example, because for each field, at least on of the records contains a value. You see, it is critical that both records are still viewable at the same time (values must fall underneath the correct field heading on the datasheet).

Let me illustrate. We have several records in the parent data-table. I want to view the associated subtable records for one of these parent records, so I click the expand button. I find that the record I'm selecting has 3 linked records in the subtable. Now let's say the the subtable contains 4 fields and data as followsl:

FieldA, FieldB, FieldC, FieldD...
Record1: (1,2,null,3)
Record2: (4,null,null,5)
Record3: (6,mull,null,null)

In this case, I would want the datasheet to show all of the following:

FieldA, FieldB, FieldD...
Record1: (1,2,3)
Record2: (4,null,5)
Record3: (6,null,null)

All subrecords are still shown, but the FieldC column needs to be hidden because all the associated records contain nulls for that field. Note that other subrecords (ones not associated with this particulat parent record) may very well contain values for FieldC, but this would not influence the feature I would like to implement.

I'm thinking this is probably one of those rare "not possible" instances for Access. Either that, or just not worth the effort. However, please let me know if I'm wrong.

Right now i see what you mean.

Well its possible to do in theory! But there would be a bit of effort involved and the more records involved the slower the process is going to be. how many on avg would there be?

On changing a record on the main form. Recheck the data in the subform for entire fields for null. Which means checking each record and every field. and then when no data is found in a column of fields then set the control on the subform to width = 0 (which should effectively hide the field) Interestingly setting visible to no has no effect on a subform..go figure

You can approach this one of two ways either dynamically or statically. depending on the amount of change your table structure ungoes. Statically you would tell the code which fields to check, but dynamically you would just cycle through the fields automatically. Dynamically is a bit of extra work but not much but staticlets you determine if a field should be included in your search - this however, is a LOT of work.

If you want to continue i'll start digging out code etc