Hide column in contious form

[Ryan Tisserand]

I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels. Please advise.

 

[Marshall Barton]

I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels.

You need to adjust the Left property of the other controls.
How you name your controls can make it easier or more
difficult. For example if the controls a named something
like Ctl1, Ctl2, ... in left to right order, then you could
use code along these lines:

Dim k As Integer
Dim pos As Long

For k = 1 to N
With Me("Ctl" & k)
If .Visible Then
.Left = pos
pos = pos + .Width
End If
End With
Next K

 

[tina]

one solution might be to bind the crosstab query results to a form set to
Datasheet view, then use that form as a subform and put your "filters and
labels", and whatever else you need, in the main form.

hth

 

[Ryan Tisserand]

one solution might be to bind the crosstab query results to a form set to
Datasheet view, then use that form as a subform and put your "filters and
labels", and whatever else you need, in the main form.

hth

I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels. Please advise.

 

[Ryan Tisserand]

I tried this method, but when I added more columns they did not show up on
the datasheet form. I think my best plan of attack is going to be to export
the crosstab query to a excel spreadsheet.

one solution might be to bind the crosstab query results to a form set to
Datasheet view, then use that form as a subform and put your "filters and
labels", and whatever else you need, in the main form.

hth

I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels. Please advise.

 

[Marshall Barton]

I think tima was suggesting that you set the subform
**control's** SourceObject property to the crosstab query's
name. In this kind or arrangement Access automatically
creates a datasheet subform so you will not have a form
object for this purpose. Of course this approach is not
viable if you require code behind the form.
--
Marsh
MVP [MS Access]

I tried this method, but when I added more columns they did not show up on
the datasheet form. I think my best plan of attack is going to be to export
the crosstab query to a excel spreadsheet.

one solution might be to bind the crosstab query results to a form set to
Datasheet view, then use that form as a subform and put your "filters and
labels", and whatever else you need, in the main form.

I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels.

 

[Ryan Tisserand]

Would I put this code in the on open event of the form? I havent tried this
code, but how is the If.Visible going to work if it doesnt know if its
visible or not yet?

I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels.

You need to adjust the Left property of the other controls.
How you name your controls can make it easier or more
difficult. For example if the controls a named something
like Ctl1, Ctl2, ... in left to right order, then you could
use code along these lines:

Dim k As Integer
Dim pos As Long

For k = 1 to N
With Me("Ctl" & k)
If .Visible Then
.Left = pos
pos = pos + .Width
End If
End With
Next K

 

[Marshall Barton]

You're right, the open event is way too soon.

You never did say how you are making a column invisible,
but, whatever it is, that's the place where you should put
the code.
--
Marsh
MVP [MS Access]

Would I put this code in the on open event of the form? I havent tried this
code, but how is the If.Visible going to work if it doesnt know if its
visible or not yet?

I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels.

You need to adjust the Left property of the other controls.
How you name your controls can make it easier or more
difficult. For example if the controls a named something
like Ctl1, Ctl2, ... in left to right order, then you could
use code along these lines:

Dim k As Integer
Dim pos As Long

For k = 1 to N
With Me("Ctl" & k)
If .Visible Then
.Left = pos
pos = pos + .Width
End If
End With
Next K

 

[Ryan Tisserand]

Here is the code for one of the 17 checkbox's.

Private Sub Form_Activate()
If (Me.DemoMissing) = -1 Then
Me.DemoMissing.Visible = True
Else
Me.DemoMissing.Visible = False
End If
End Sub

On average there will only be one or two boxes checked, in which case I
would only want to see those two results, from left to right order next to
each other. I can make everything work except the placement of the text box.
I still have not tried your code, im not exactly sure how to use it. Please
forgive me, but could you give me a little more detail on how to use the left
property tecnique.
Private Sub Form_Activate()
If (Me.DemoMissing) = -1 Then
Me.DemoMissing.Visible = True
Else
Me.DemoMissing.Visible = False
End If
End Sub

You're right, the open event is way too soon.

You never did say how you are making a column invisible,
but, whatever it is, that's the place where you should put
the code.
--
Marsh
MVP [MS Access]

Would I put this code in the on open event of the form? I havent tried this
code, but how is the If.Visible going to work if it doesnt know if its
visible or not yet?

Ryan Tisserand wrote:
I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels.

You need to adjust the Left property of the other controls.
How you name your controls can make it easier or more
difficult. For example if the controls a named something
like Ctl1, Ctl2, ... in left to right order, then you could
use code along these lines:

Dim k As Integer
Dim pos As Long

For k = 1 to N
With Me("Ctl" & k)
If .Visible Then
.Left = pos
pos = pos + .Width
End If
End With
Next K

 

[Marshall Barton]

You really should rename the controls as I said before. If
you don't, you will need to have 7 lines of xide for each of
the text boxes (didn't you mean check boxes?)

If you do that, try using the Load event with code like:

Private Sub Form_Load()
Dim k As Integer
Dim pos As Long

For k = 1 to N 'change N to the number of text boxes
With Me("Ctl" & k)
If .Value = True Then
.Visible = True
.Left = pos
pos = pos + .Width
Else
.Visible = False
End If
End With
Next K

End Sub

Are you aware that whatever is done to a continuous form's
property settings will apply to every record (much like in
the query's sheet view) and that you are only checking the
value of the query fields in the first record?
--
Marsh
MVP [MS Access]

Here is the code for one of the 17 checkbox's.

Private Sub Form_Activate()
If (Me.DemoMissing) = -1 Then
Me.DemoMissing.Visible = True
Else
Me.DemoMissing.Visible = False
End If
End Sub

On average there will only be one or two boxes checked, in which case I
would only want to see those two results, from left to right order next to
each other. I can make everything work except the placement of the text box.
I still have not tried your code, im not exactly sure how to use it. Please
forgive me, but could you give me a little more detail on how to use the left
property tecnique.
Private Sub Form_Activate()
If (Me.DemoMissing) = -1 Then
Me.DemoMissing.Visible = True
Else
Me.DemoMissing.Visible = False
End If
End Sub

You're right, the open event is way too soon.

You never did say how you are making a column invisible,
but, whatever it is, that's the place where you should put
the code.

Would I put this code in the on open event of the form? I havent tried this
code, but how is the If.Visible going to work if it doesnt know if its
visible or not yet?


Ryan Tisserand wrote:
I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels.


:
You need to adjust the Left property of the other controls.
How you name your controls can make it easier or more
difficult. For example if the controls a named something
like Ctl1, Ctl2, ... in left to right order, then you could
use code along these lines:

Dim k As Integer
Dim pos As Long

For k = 1 to N
With Me("Ctl" & k)
If .Visible Then
.Left = pos
pos = pos + .Width
End If
End With
Next K

 

[Ryan Tisserand]

You see, that is the problem. I need something that looks like this.

Date Patient Name CHK = Label of the checked box
01/01/2007 John Doe CHK1 CH4 CH13
01/02/2007 Jane Doe CHK8
01/03/2007 Bob Smith CHK6 CH9 CHK10 CHK12 CHK13

You really should rename the controls as I said before. If
you don't, you will need to have 7 lines of xide for each of
the text boxes (didn't you mean check boxes?)

If you do that, try using the Load event with code like:

Private Sub Form_Load()
Dim k As Integer
Dim pos As Long

For k = 1 to N 'change N to the number of text boxes
With Me("Ctl" & k)
If .Value = True Then
.Visible = True
.Left = pos
pos = pos + .Width
Else
.Visible = False
End If
End With
Next K

End Sub

Are you aware that whatever is done to a continuous form's
property settings will apply to every record (much like in
the query's sheet view) and that you are only checking the
value of the query fields in the first record?
--
Marsh
MVP [MS Access]

Here is the code for one of the 17 checkbox's.

Private Sub Form_Activate()
If (Me.DemoMissing) = -1 Then
Me.DemoMissing.Visible = True
Else
Me.DemoMissing.Visible = False
End If
End Sub

On average there will only be one or two boxes checked, in which case I
would only want to see those two results, from left to right order next to
each other. I can make everything work except the placement of the text box.
I still have not tried your code, im not exactly sure how to use it. Please
forgive me, but could you give me a little more detail on how to use the left
property tecnique.
Private Sub Form_Activate()
If (Me.DemoMissing) = -1 Then
Me.DemoMissing.Visible = True
Else
Me.DemoMissing.Visible = False
End If
End Sub

You're right, the open event is way too soon.

You never did say how you are making a column invisible,
but, whatever it is, that's the place where you should put
the code.


Ryan Tisserand wrote:
Would I put this code in the on open event of the form? I havent tried this
code, but how is the If.Visible going to work if it doesnt know if its
visible or not yet?


Ryan Tisserand wrote:
I have a continous form [BatchSummaryTotals] that's recordsource is a
crosstab query [BatchSummaryTotals_Crosstab]. In datasheet view when I set
the field Me.C42.visible = false the column is removed and the rest of the
columns move left and remove blank space. Is there a way to do this on a
continous form? I can set the visible property to false and it is removed,
but it leaves a blank space as large as the column that was there. I am open
to other ways to get my data in a row/column form, but I need header and
footer sections for filters and labels.


:
You need to adjust the Left property of the other controls.
How you name your controls can make it easier or more
difficult. For example if the controls a named something
like Ctl1, Ctl2, ... in left to right order, then you could
use code along these lines:

Dim k As Integer
Dim pos As Long

For k = 1 to N
With Me("Ctl" & k)
If .Visible Then
.Left = pos
pos = pos + .Width
End If
End With
Next K

 

[Marshall Barton]

You see, that is the problem. I need something that looks like this.

Date Patient Name CHK = Label of the checked box
01/01/2007 John Doe CHK1 CH4 CH13
01/02/2007 Jane Doe CHK8
01/03/2007 Bob Smith CHK6 CH9 CHK10 CHK12 CHK13

There is no way to do that with the approach we've been
talking about.

How about using a concatenated list of the lable captions in
a single wide text box? This can be done using a function
in the form's module:

Public Function CheckedNames()
Dim k As Integer
For k = 1 to N
If Not IsNull(Me("Chk" & k)) Then
CheckedNames = CheckedNames _
& Me("Chk" & k).Controls(0).Caption & " "
End If
Next k
End Function

Then make all of the Chkxx check boxes invisible and set the
new text box's control source expression to =CheckedNames()

 

[Ryan Tisserand]

I have come up with a solution. I have done everything in the query itself.
Here is what I did if you wish to know. I did the following for all my
checkboxes.

1DemoMissing: IIf([DemoMissing]=True,"DemoMissing, ",Null)

Then I did the following in the same query to build a one field result.

([1DemoMissing]) & "" & ([2Page1Missing]) & "" & ([3Page2Missing]) & "" &
([4MissingChiefComplaint]) & "" & ([5MissingPhysicianSignature]) & "" &
([6HPIIncomplete]) & "" & ([7PFSHIncomplete]) & "" & ([8ROSIncomplete]) & ""
& ([9PEIncomplete]) & "" & ([10NursesNotesMissing]) & "" &
([11SupervisingPhysiciansNotesMissing]) & "" & ([12CriticalCareTimeMissing])
& "" & ([13OrdersMissing]) & "" & ([14ProcedureMissing])

Worked like a charm because then I had all the checked boxes in one feild
seperated by a ,

Thank you for all your help Marshall.

 

[Marshall Barton]

I have come up with a solution. I have done everything in the query itself.
Here is what I did if you wish to know. I did the following for all my
checkboxes.

1DemoMissing: IIf([DemoMissing]=True,"DemoMissing, ",Null)

Then I did the following in the same query to build a one field result.

([1DemoMissing]) & "" & ([2Page1Missing]) & "" & ([3Page2Missing]) & "" &
([4MissingChiefComplaint]) & "" & ([5MissingPhysicianSignature]) & "" &
([6HPIIncomplete]) & "" & ([7PFSHIncomplete]) & "" & ([8ROSIncomplete]) & ""
& ([9PEIncomplete]) & "" & ([10NursesNotesMissing]) & "" &
([11SupervisingPhysiciansNotesMissing]) & "" & ([12CriticalCareTimeMissing])
& "" & ([13OrdersMissing]) & "" & ([14ProcedureMissing])

Looks good to me. It's essentially the same as my last
idea, but you did it in one long query expression instead of
using a form function in a text box expression. The nice
thing about doing it in the query is that you avoid having
all those invisible check box controls on the form.