Hide 0 records subform to reveal txtbox underneath

[tuesamlarry]

If there are no records in a subform when the underlying query runs, then I
wish the subform to be invisible so as to reveal alternate data in a textbox
hidden underneath the subform. I have tried all the solutions I could find in
these forums, but have been unsuccessful. I have set the subform visible
property to no and then tried various VBA solutions in the On Load event. I
get the subform to show the correct data when it exists, but when there is no
data, there is still a blank white space overlaying the txtbox hidden
underneath. Would appreciate some advice.
Access 2007 on Win XP.

 

[Allen Browne]

Does the subform show the new record row? This suggestion won't work if it
doesn't (e.g. if it is bound to a read-only source, or its AllowAdditions is
No, or the main form's AllowEdits is No.)

If it does, you could use the Current event of the subform to test the
RecordCount if its RecordsetClone, and toggle its Visible property.
Private Sub Form_Current
Dim bShow As Boolean
bShow = Me.Recordset.RecordCount <> 0)
With Me.Parent![NameOfYourSubformControlHere]
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End Sub
(I think you will find that Access still loads the subform records when
invisible, but test it.)

If the subform doesn't show the new row, its Current event won't fire when
there are no records. You would therefore need to use some events in the
main form that detect when LinkMasterFields change.

 

[tuesamlarry]

Allen,
My subform [sfrmStVincentAvailability] is based on a Totals query, showing
the Last record, so therefore there will be only one record. However, Allow
additions is on, and AllowEdits is on. I did paste the VBA code in the
Current Event, but got a compile Error/syntax error. I believe you forgot the
Clone on Recordset. After adding it, got rid of that error. But when trying
to open my main form I get a Run-Time error 2452.."The Expression you entered
has an invalid reference to the Parent Property....
With Me.Parent![sfrmStVincentAvailability]

The main form collects data on clients, so it will have many records, and
the subform follows along for each different client.

Does the subform show the new record row? This suggestion won't work if it
doesn't (e.g. if it is bound to a read-only source, or its AllowAdditions is
No, or the main form's AllowEdits is No.)

If it does, you could use the Current event of the subform to test the
RecordCount if its RecordsetClone, and toggle its Visible property.
Private Sub Form_Current
Dim bShow As Boolean
bShow = Me.Recordset.RecordCount <> 0)
With Me.Parent![NameOfYourSubformControlHere]
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End Sub
(I think you will find that Access still loads the subform records when
invisible, but test it.)

If the subform doesn't show the new row, its Current event won't fire when
there are no records. You would therefore need to use some events in the
main form that detect when LinkMasterFields change.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If there are no records in a subform when the underlying query runs, then
I
wish the subform to be invisible so as to reveal alternate data in a
textbox
hidden underneath the subform. I have tried all the solutions I could find
in
these forums, but have been unsuccessful. I have set the subform visible
property to no and then tried various VBA solutions in the On Load event.
I
get the subform to show the correct data when it exists, but when there is
no
data, there is still a blank white space overlaying the txtbox hidden
underneath. Would appreciate some advice.
Access 2007 on Win XP.

 

[Allen Browne]

The RecordsetClone (rather than Recordset) is required for Access 97 and
earlier. (It's fine in later versions too.)

The Parent property will fail if you open the form directly (not as a
subform.)

If the subform is based on a Totals query, it will be read-only, so you
can't use the subform's Current event. Try the main form's Current event. If
it is at a new record, there cannot be any related records in the subform's
table. If the main form is not at a new record, DLookup() the subform's
table to see if there are any related records or not. (You can't test in the
subform, becuase it's not loaded yet.) Also, if the LinkMasterFields
field(s) are editable, you may need to use their AfterUpdate event as well.

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
My subform [sfrmStVincentAvailability] is based on a Totals query, showing
the Last record, so therefore there will be only one record. However,
Allow
additions is on, and AllowEdits is on. I did paste the VBA code in the
Current Event, but got a compile Error/syntax error. I believe you forgot
the
Clone on Recordset. After adding it, got rid of that error. But when
trying
to open my main form I get a Run-Time error 2452.."The Expression you
entered
has an invalid reference to the Parent Property....
With Me.Parent![sfrmStVincentAvailability]

The main form collects data on clients, so it will have many records, and
the subform follows along for each different client.

Does the subform show the new record row? This suggestion won't work if
it
doesn't (e.g. if it is bound to a read-only source, or its AllowAdditions
is
No, or the main form's AllowEdits is No.)

If it does, you could use the Current event of the subform to test the
RecordCount if its RecordsetClone, and toggle its Visible property.
Private Sub Form_Current
Dim bShow As Boolean
bShow = Me.Recordset.RecordCount <> 0)
With Me.Parent![NameOfYourSubformControlHere]
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End Sub
(I think you will find that Access still loads the subform records when
invisible, but test it.)

If the subform doesn't show the new row, its Current event won't fire
when
there are no records. You would therefore need to use some events in the
main form that detect when LinkMasterFields change.

If there are no records in a subform when the underlying query runs,
then
I
wish the subform to be invisible so as to reveal alternate data in a
textbox
hidden underneath the subform. I have tried all the solutions I could
find
in
these forums, but have been unsuccessful. I have set the subform
visible
property to no and then tried various VBA solutions in the On Load
event.
I
get the subform to show the correct data when it exists, but when there
is
no
data, there is still a blank white space overlaying the txtbox hidden
underneath. Would appreciate some advice.
Access 2007 on Win XP.

 

[tuesamlarry]

Allen,
No luck so far. I moved the VBA to the Current Event on the main form.
Your line

With Me.Parent![sfrmStVincentAvailability]

produced Runtime error 2452. "The expression you entered has an invalid
reference to the Parent Property. The following mod finally produced no
syntax error.

With Forms![frmClient]![sfrmStVincentAvailability]

I still have a blank white blob when there are no records, conforming to the
size of the subform in design mode. I have 3 subforms I am trying to do this
for, by the way, but am only working on one at a time. The main form opens on
an existing record. The subform(s) linked with parent/child fields.

The RecordsetClone (rather than Recordset) is required for Access 97 and
earlier. (It's fine in later versions too.)

The Parent property will fail if you open the form directly (not as a
subform.)

If the subform is based on a Totals query, it will be read-only, so you
can't use the subform's Current event. Try the main form's Current event. If
it is at a new record, there cannot be any related records in the subform's
table. If the main form is not at a new record, DLookup() the subform's
table to see if there are any related records or not. (You can't test in the
subform, becuase it's not loaded yet.) Also, if the LinkMasterFields
field(s) are editable, you may need to use their AfterUpdate event as well.

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
My subform [sfrmStVincentAvailability] is based on a Totals query, showing
the Last record, so therefore there will be only one record. However,
Allow
additions is on, and AllowEdits is on. I did paste the VBA code in the
Current Event, but got a compile Error/syntax error. I believe you forgot
the
Clone on Recordset. After adding it, got rid of that error. But when
trying
to open my main form I get a Run-Time error 2452.."The Expression you
entered
has an invalid reference to the Parent Property....
With Me.Parent![sfrmStVincentAvailability]

The main form collects data on clients, so it will have many records, and
the subform follows along for each different client.

Does the subform show the new record row? This suggestion won't work if
it
doesn't (e.g. if it is bound to a read-only source, or its AllowAdditions
is
No, or the main form's AllowEdits is No.)

If it does, you could use the Current event of the subform to test the
RecordCount if its RecordsetClone, and toggle its Visible property.
Private Sub Form_Current
Dim bShow As Boolean
bShow = Me.Recordset.RecordCount <> 0)
With Me.Parent![NameOfYourSubformControlHere]
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End Sub
(I think you will find that Access still loads the subform records when
invisible, but test it.)

If the subform doesn't show the new row, its Current event won't fire
when
there are no records. You would therefore need to use some events in the
main form that detect when LinkMasterFields change.

If there are no records in a subform when the underlying query runs,
then
I
wish the subform to be invisible so as to reveal alternate data in a
textbox
hidden underneath the subform. I have tried all the solutions I could
find
in
these forums, but have been unsuccessful. I have set the subform
visible
property to no and then tried various VBA solutions in the On Load
event.
I
get the subform to show the correct data when it exists, but when there
is
no
data, there is still a blank white space overlaying the txtbox hidden
underneath. Would appreciate some advice.
Access 2007 on Win XP.

 

[Allen Browne]

I'm not sure what your purpose is here.

If the subform control is the ActiveForm of the main form, you won't be able
to hide it.

If you only need one subform at a time, it might be possible to set the
SourceObject of the subform control to load the form you want into it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
No luck so far. I moved the VBA to the Current Event on the main form.
Your line

With Me.Parent![sfrmStVincentAvailability]

produced Runtime error 2452. "The expression you entered has an invalid
reference to the Parent Property. The following mod finally produced no
syntax error.

With Forms![frmClient]![sfrmStVincentAvailability]

I still have a blank white blob when there are no records, conforming to
the
size of the subform in design mode. I have 3 subforms I am trying to do
this
for, by the way, but am only working on one at a time. The main form opens
on
an existing record. The subform(s) linked with parent/child fields.

The RecordsetClone (rather than Recordset) is required for Access 97 and
earlier. (It's fine in later versions too.)

The Parent property will fail if you open the form directly (not as a
subform.)

If the subform is based on a Totals query, it will be read-only, so you
can't use the subform's Current event. Try the main form's Current event.
If
it is at a new record, there cannot be any related records in the
subform's
table. If the main form is not at a new record, DLookup() the subform's
table to see if there are any related records or not. (You can't test in
the
subform, becuase it's not loaded yet.) Also, if the LinkMasterFields
field(s) are editable, you may need to use their AfterUpdate event as
well.

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
My subform [sfrmStVincentAvailability] is based on a Totals query,
showing
the Last record, so therefore there will be only one record. However,
Allow
additions is on, and AllowEdits is on. I did paste the VBA code in the
Current Event, but got a compile Error/syntax error. I believe you
forgot
the
Clone on Recordset. After adding it, got rid of that error. But when
trying
to open my main form I get a Run-Time error 2452.."The Expression you
entered
has an invalid reference to the Parent Property....
With Me.Parent![sfrmStVincentAvailability]

The main form collects data on clients, so it will have many records,
and
the subform follows along for each different client.

:

Does the subform show the new record row? This suggestion won't work
if
it
doesn't (e.g. if it is bound to a read-only source, or its
AllowAdditions
is
No, or the main form's AllowEdits is No.)

If it does, you could use the Current event of the subform to test the
RecordCount if its RecordsetClone, and toggle its Visible property.
Private Sub Form_Current
Dim bShow As Boolean
bShow = Me.Recordset.RecordCount <> 0)
With Me.Parent![NameOfYourSubformControlHere]
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End Sub
(I think you will find that Access still loads the subform records
when
invisible, but test it.)

If the subform doesn't show the new row, its Current event won't fire
when
there are no records. You would therefore need to use some events in
the
main form that detect when LinkMasterFields change.

If there are no records in a subform when the underlying query runs,
then
I
wish the subform to be invisible so as to reveal alternate data in a
textbox
hidden underneath the subform. I have tried all the solutions I
could
find
in
these forums, but have been unsuccessful. I have set the subform
visible
property to no and then tried various VBA solutions in the On Load
event.
I
get the subform to show the correct data when it exists, but when
there
is
no
data, there is still a blank white space overlaying the txtbox
hidden
underneath. Would appreciate some advice.
Access 2007 on Win XP.

 

[Geoffrey Peall]

Do your thing

 

[tuesamlarry]

Allen,
I solved my problem as follows:

1. Set the 3 subforms which contain the three values I want to Not Visible
2. Created a txtbox for each of the subform values set to
=Nz([nameofsubform].[Form]![nameofsubformcontrol])
3. Created VBA code in the Current Event of the main form as

Private Sub Form_Current()
If [nameoftxtbox] = "" Then
[nameoftxtbox].Visible = False
Else: [nameoftxtbox].Visible = True
End If

I can now place the txtbox on top of another unbound txtbox with a message.
When there are no record results for a particular query, the label disappears
revealing the alternate data value underneath.

Works great.

I'm not sure what your purpose is here.

If the subform control is the ActiveForm of the main form, you won't be able
to hide it.

If you only need one subform at a time, it might be possible to set the
SourceObject of the subform control to load the form you want into it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
No luck so far. I moved the VBA to the Current Event on the main form.
Your line

With Me.Parent![sfrmStVincentAvailability]

produced Runtime error 2452. "The expression you entered has an invalid
reference to the Parent Property. The following mod finally produced no
syntax error.

With Forms![frmClient]![sfrmStVincentAvailability]

I still have a blank white blob when there are no records, conforming to
the
size of the subform in design mode. I have 3 subforms I am trying to do
this
for, by the way, but am only working on one at a time. The main form opens
on
an existing record. The subform(s) linked with parent/child fields.

The RecordsetClone (rather than Recordset) is required for Access 97 and
earlier. (It's fine in later versions too.)

The Parent property will fail if you open the form directly (not as a
subform.)

If the subform is based on a Totals query, it will be read-only, so you
can't use the subform's Current event. Try the main form's Current event.
If
it is at a new record, there cannot be any related records in the
subform's
table. If the main form is not at a new record, DLookup() the subform's
table to see if there are any related records or not. (You can't test in
the
subform, becuase it's not loaded yet.) Also, if the LinkMasterFields
field(s) are editable, you may need to use their AfterUpdate event as
well.

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
My subform [sfrmStVincentAvailability] is based on a Totals query,
showing
the Last record, so therefore there will be only one record. However,
Allow
additions is on, and AllowEdits is on. I did paste the VBA code in the
Current Event, but got a compile Error/syntax error. I believe you
forgot
the
Clone on Recordset. After adding it, got rid of that error. But when
trying
to open my main form I get a Run-Time error 2452.."The Expression you
entered
has an invalid reference to the Parent Property....
With Me.Parent![sfrmStVincentAvailability]

The main form collects data on clients, so it will have many records,
and
the subform follows along for each different client.

:

Does the subform show the new record row? This suggestion won't work
if
it
doesn't (e.g. if it is bound to a read-only source, or its
AllowAdditions
is
No, or the main form's AllowEdits is No.)

If it does, you could use the Current event of the subform to test the
RecordCount if its RecordsetClone, and toggle its Visible property.
Private Sub Form_Current
Dim bShow As Boolean
bShow = Me.Recordset.RecordCount <> 0)
With Me.Parent![NameOfYourSubformControlHere]
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End Sub
(I think you will find that Access still loads the subform records
when
invisible, but test it.)

If the subform doesn't show the new row, its Current event won't fire
when
there are no records. You would therefore need to use some events in
the
main form that detect when LinkMasterFields change.

If there are no records in a subform when the underlying query runs,
then
I
wish the subform to be invisible so as to reveal alternate data in a
textbox
hidden underneath the subform. I have tried all the solutions I
could
find
in
these forums, but have been unsuccessful. I have set the subform
visible
property to no and then tried various VBA solutions in the On Load
event.
I
get the subform to show the correct data when it exists, but when
there
is
no
data, there is still a blank white space overlaying the txtbox
hidden
underneath. Would appreciate some advice.
Access 2007 on Win XP.