How to hide columns in a form in datasheet view if null

[tm5]

Can someone please show me how to hide all columns when it is null using VBA
code. I am using a form in datasheet view. I am trying to hide all columns
that are null and display all columns that are not null. I have tried the
following in the Form_Current event and it does not work. Listed below is
one of the column names on the form. Please Help!!!

If IsNull(Me![51712desc]) = True Then
Me![51712desc].ColumnHidden = True
Else
Me![51712desc].ColumnHidden = False
End If

 

[mscertified]

It cannot be done like that. A datasheet shows many rows and some rows may
have null values and others may not. You could run a query to determine if
all rows contain a null value and them omit that column from the query
feeding the datasheet but I don't know any way to do it programatically. The
user can eliminate columns by dragging the separator bars.

-Dorian

 

[SteveM]

Do the columns only contain numeric data?

If so, you could create totals field(s) in the form footer and reference
that before hiding a column. This field will not display in datasheet view
but you can reference it.

 

[tm5]

yes, only numeric data

Do the columns only contain numeric data?

If so, you could create totals field(s) in the form footer and reference
that before hiding a column. This field will not display in datasheet view
but you can reference it.

--
Steve McGuire
MCSD, MCAD, MCP

Can someone please show me how to hide all columns when it is null using VBA
code. I am using a form in datasheet view. I am trying to hide all columns
that are null and display all columns that are not null. I have tried the
following in the Form_Current event and it does not work. Listed below is
one of the column names on the form. Please Help!!!

If IsNull(Me![51712desc]) = True Then
Me![51712desc].ColumnHidden = True
Else
Me![51712desc].ColumnHidden = False
End If

 

[SteveM]

Ok then, in design mode, in the footer, place a textbox txtTotal1 and set the
ControlSource to '=Sum(51712desc)' everything between the single quotes...

Your code:
If Not IsNumeric(Me![txtTotal1]) Then
Me![51712desc].ColumnHidden = True
Else
Me![51712desc].ColumnHidden = False
End If
--
Steve McGuire
MCSD, MCAD, MCP

yes, only numeric data

Do the columns only contain numeric data?

If so, you could create totals field(s) in the form footer and reference
that before hiding a column. This field will not display in datasheet view
but you can reference it.

--
Steve McGuire
MCSD, MCAD, MCP

Can someone please show me how to hide all columns when it is null using VBA
code. I am using a form in datasheet view. I am trying to hide all columns
that are null and display all columns that are not null. I have tried the
following in the Form_Current event and it does not work. Listed below is
one of the column names on the form. Please Help!!!

If IsNull(Me![51712desc]) = True Then
Me![51712desc].ColumnHidden = True
Else
Me![51712desc].ColumnHidden = False
End If

 

[tm5]

Thanks! It worked!

Ok then, in design mode, in the footer, place a textbox txtTotal1 and set the
ControlSource to '=Sum(51712desc)' everything between the single quotes...

Your code:
If Not IsNumeric(Me![txtTotal1]) Then
Me![51712desc].ColumnHidden = True
Else
Me![51712desc].ColumnHidden = False
End If
--
Steve McGuire
MCSD, MCAD, MCP

yes, only numeric data

Do the columns only contain numeric data?

If so, you could create totals field(s) in the form footer and reference
that before hiding a column. This field will not display in datasheet view
but you can reference it.

--
Steve McGuire
MCSD, MCAD, MCP


:

Can someone please show me how to hide all columns when it is null using VBA
code. I am using a form in datasheet view. I am trying to hide all columns
that are null and display all columns that are not null. I have tried the
following in the Form_Current event and it does not work. Listed below is
one of the column names on the form. Please Help!!!

If IsNull(Me![51712desc]) = True Then
Me![51712desc].ColumnHidden = True
Else
Me![51712desc].ColumnHidden = False
End If