Toggling .Visible for controls in Subform footer

[Lyn]

Hi,
I have a form which contains a number of subforms in different pages of a
tab control. In the detail section of each subform, I list records related
to the main form record, but from different tables. In the footer section
of the subform I have a Notes text box and three buttons (Add, Update,
Delete). The latter each open a separate form for updating or deleting the
subform data (the records cannot be updated or deleted directly from the
subform which is for display only). The Notes field displays an additional
(large) field from the record selected in the subform list.

The subforms are loaded via the Current event of the main form.

If there are no records to display in the subform, I would like to make the
Notes control and the Update and Delete buttons not visible (leaving just
the Add button visible). To do this, I created a recordset in the main form
Current event using the same SQL as that used to load the subform table. If
the record count is zero, then I set the .Visible property of the Notes
field and the two buttons to FALSE.

This works fine -- except that once made not visible, they cannot be made
visible again on a new main form record when the subform record count is GT
zero. In fact, through experimenting, I have found that just mentioning the
..Visible property of these controls via the main form Current event makes
them invisible -- even if the only mention is to set .Visible TRUE!

What is going on???

I realise that the footer contents are intended to remain static for all
records. Is this the problem, that it is "illegal" to make the contents
data dependent? Or what?

Any advice and assistance greatly appreciated. (Access 2003, WinXP)

--
Cheers,
Lyn.

[Billie Kennedy Jr]

are you making sure that you set the visible to false on the others
when you make one visible? Sometimes overlapping can make it appear
that the one you want isn't visible.

I have an unbound form with several subforms which will appear
depending on which button you select. All of them overlap and are the
same size. I just turn one off then the other on and reset the focus
to where I want it to go.

As far as your footer contents. That shouldn't matter too much if you
just set the properties of something. I use the footer in one of my
forms for buttons to menus. Depending on the Access level of the user
they see different buttons. So having something in your footer
shouldn't matter too much if you need to set the .Visible property.

[Lyn]

Billie,
Thanks for your response. None of the objects in the footer overlap. I
just want to hide the Update and Delete buttons when there are no records to
update or delete.

Here is an extract from the main form's Current procedure where it loads the
subform when a new main form record is opened (main form is frmPerson):

'Display the record list in the subform
mySQL = "SELECT * FROM MiscRecs WHERE MiscRecs.IDPerson = " &
Nz(Forms.frmPerson!IDPerson, 0) & " AND RecordType = 'Education' "
Me.[sfmEducation].Form.RecordSource = mySQL
Me.[sfmEducation].LinkMasterFields = ""
Me.[sfmEducation].LinkChildFields = ""

'Count the number of records displayed
RS.Open mySQL, Conn, adOpenStatic

'Show or hide Update and Delete buttons, and Notes field
'depending on RecordCount value
If RS.RecordCount > 0 Then
' Me.sfmEducation.Form!btnDelete.Visible = Yes
' Me.sfmEducation.Form!btnUpdate.Visible = Yes
' Me.sfmEducation.Form!Notes.Visible = Yes
'Else
' Me.sfmEducation.Form!btnDelete.Visible = No
' Me.sfmEducation.Form!btnUpdate.Visible = No
' Me.sfmEducation.Form!Notes.Visible = No
End If
RS.Close

All three buttons and the Notes control display correctly when the code is
commented out as shown. If I activate the commented code, the buttons and
the Notes field all disappear -- REGARDLESS of the value of RecordCount (and
I have verified that RecordCount values accurately reflect the number of
rows in the subform -- I have had all values from 0 to 6). The If statement
does take the correct path depending on the value of RecordCount.

It gets even weirder -- if I delete all the code from "RS.Open" onwards, all
buttons are visible. However, if I add just the following statement:

Me.sfmEducation.Form!btnDelete.Visible = Yes

the Delete button disappears -- even though the statement just forces the
button to be visible! Similar statements for the other button and Notes
also make these objects invisible.

Any ideas? Anyone?
--
Cheers,
Lyn.

"Billie Kennedy Jr" <da****@orpgs.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

are you making sure that you set the visible to false on the others
when you make one visible? Sometimes overlapping can make it appear
that the one you want isn't visible.

I have an unbound form with several subforms which will appear
depending on which button you select. All of them overlap and are the
same size. I just turn one off then the other on and reset the focus
to where I want it to go.

As far as your footer contents. That shouldn't matter too much if you
just set the properties of something. I use the footer in one of my
forms for buttons to menus. Depending on the Access level of the user
they see different buttons. So having something in your footer
shouldn't matter too much if you need to set the .Visible property.

[Billie Kennedy Jr]

I had problems with some of my stuff disappearing as well. Or not even
showing up properly. Right now I use Form_Current() for setting the
default values of fields and properties for my form. Once the form is
open I have all of the active code in Form_Activate().

When a user chooses a record the form gets updated with the information
needed. When the user chooses a new form that updates the contents of
the first form and closes the new one then the first form is updated
with the new information as well.

I do have to say that the first form is an unbound form with no active
fields. If the user wants to edit any of the information on the first
form they have to open a different form. Those options are per user
access set by a user table.
Try the Form_Activate() function for your form instead of
Form_Current().

This helped me. It may help you.
--------------
Billie
http://www.orpgs.com

[Lyn]

It was worth a try. I hadn't tried it before because I understand Activate
to occur only when the form window becomes current after focus moves back to
it from another window, whereas I need the buttons to be updated when
scrolling from main form record to record. In fact, I use Activate to
Requery the subforms -- like your application, I don't update the subforms
directly, but call another form to do this. On return to the main form, the
subforms are requeried to update them according to the changes made in the
update form.

In fact, your comments made me realise that I also need to update the
buttons' visibility in the Activate event as well as the Current event (if I
delete the only row, then the subform will now be empty and the buttons need
to be hidden -- or if the subform was empty and I Add a record, the buttons
need to be made visible).

Anyway, I tried copying the button hide/show code into Activate, but it
still (mis)behaves the same way as in Current (after updating a subform
record). I guess that it was made to work this way for some reason.

The only workaround I can come up with is to include the buttons in each row
of the subform -- if you want to update or delete a record, you would have
to click the button in that row. The Add button could still be in the
footer as this should always be visible. Not a very elegant method.

However, I am sure that there must be a better solution out there
somewhere...

Thanks again for your help.
--
Cheers,
Lyn.

"Billie Kennedy Jr" <da****@orpgs.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

I had problems with some of my stuff disappearing as well. Or not even
showing up properly. Right now I use Form_Current() for setting the
default values of fields and properties for my form. Once the form is
open I have all of the active code in Form_Activate().

When a user chooses a record the form gets updated with the information
needed. When the user chooses a new form that updates the contents of
the first form and closes the new one then the first form is updated
with the new information as well.

I do have to say that the first form is an unbound form with no active
fields. If the user wants to edit any of the information on the first
form they have to open a different form. Those options are per user
access set by a user table.
Try the Form_Activate() function for your form instead of
Form_Current().

This helped me. It may help you.
--------------
Billie
http://www.orpgs.com

[Lyn]

Billie,
I have solved the mystery. It had nothing to do with the controls being in
the footer, it was a general problem that only occurred in the footer in my
form.

I was setting properties ".Visible = Yes/No" which is how they are set in
the property sheet. But in VBA it should be ".Visible = True/False".
Values Yes and No are both treated as False.

Anyway, making this simple change fixed everything. I guess I was so close
to the problem I couldn't see the wood for the trees!

--
Cheers,
Lyn.

"Billie Kennedy Jr" <da****@orpgs.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

I had problems with some of my stuff disappearing as well. Or not even
showing up properly. Right now I use Form_Current() for setting the
default values of fields and properties for my form. Once the form is
open I have all of the active code in Form_Activate().

When a user chooses a record the form gets updated with the information
needed. When the user chooses a new form that updates the contents of
the first form and closes the new one then the first form is updated
with the new information as well.

I do have to say that the first form is an unbound form with no active
fields. If the user wants to edit any of the information on the first
form they have to open a different form. Those options are per user
access set by a user table.
Try the Form_Activate() function for your form instead of
Form_Current().

This helped me. It may help you.
--------------
Billie
http://www.orpgs.com