Conditionally hide command button if no records on form

[Tracey]

I have a form called fClientFile. It contains a subform called
fBenefitDesignSelect. That subform contains a subform called
fBenefitDesignView. A series of tabbed forms reside on this latter form. An
example would be fDeductibleView using the control DeductibleDetailsID.
fDeductibleView is based on a query.

I have placed a command button (AddNewDed) on the tab page itself (not
within fDeductibleView) to open another form. However, I only want the
command button to be visible if there are no records to display in
fDeductibleView.

On form load of fBenefitDesignView, I have the following code:
Me.AddNewDed.Visible = False
because most of the time I don't need to see the command button.

However, when there are no records to display in fDeductible View, I want to
see the command button. I've tried every combination I can think of, but I
keep getting a syntax error. Can someone suggest the code to make this
command button visible under the right condition? Thanks.

 

[Jay]

I believe some variation on this will work to determine the number of records
returned for the form.

Form.RecordsetClone.RecordCount

I can't recall how to reference the specific form by name if you're calling
this from another form, but this should put you in the right direction.

Hope it helps,
Jay

 

[Jay]

I think I might have misunderstood, and so I thought of something else.

Here's how I read it:
Form fDeductibleView is based on a query, and you want to know if there will
be records returned for that form 'before' the form is displayed.

I'm no Access champ, so I might be missing a step here, but I thought of an
easier way to check # of records.

In Form_Load of fBenefitDesignView, you could do this:
If DCount("*", queryName) > 0 Then
Me.AddNewDed.Visible = False
Else
Me.AddNewDed.Visible = True
End If

Hope that (also) helps,
Jay

 

[Dirk Goldgar]

I have a form called fClientFile. It contains a subform called
fBenefitDesignSelect. That subform contains a subform called
fBenefitDesignView. A series of tabbed forms reside on this latter
form. An example would be fDeductibleView using the control
DeductibleDetailsID. fDeductibleView is based on a query.

I have placed a command button (AddNewDed) on the tab page itself (not
within fDeductibleView) to open another form. However, I only want the
command button to be visible if there are no records to display in
fDeductibleView.

On form load of fBenefitDesignView, I have the following code:
Me.AddNewDed.Visible = False
because most of the time I don't need to see the command button.

However, when there are no records to display in fDeductible View, I
want to see the command button. I've tried every combination I can
think of, but I keep getting a syntax error. Can someone suggest the
code to make this command button visible under the right condition?
Thanks.

So you have this form hierachy:

fClientFile
fBenefitDesignSelect
fBenefitDesignView
fDeductibleView

?

Unless fBenefitDesignView is unbound, I think you will want to use the
Current event of that form to check the contents of fDeductibleView.

The syntax to do this has only one wrinkle. When building a reference
to the properties and controls on a subform, you have to use the name of
the subform *control* -- the control on the parent form that displays
the form object that is serving as a subform. The name of the subform
control may or may not be the same as the name of the form object it
displays.

For the code I'm about to suggest, I'm assuming the subform control is
named the same as the form it displays. You have to check that, though,
and adjust it as needed. This code would go in the Current event of
fBenefitDesignView:

'----- start of suggested code -----
Private Sub Form_Current()

Me.AddNewDed.Visible = _
(Me!fDeductibleView.Form.Recordset.RecordCount = 0)

End Sub
'----- end of suggested code -----

 

[Tracey]

This works perfectly! However, it opens the query in the background. Since I
will have a total of 5 of these forms, I need a way to close the query. What
code do I add to the statement you gave me below to do that?

You've been a great help!

Tracey

 

[Dirk Goldgar]

This works perfectly! However, it opens the query in the background.
Since I will have a total of 5 of these forms, I need a way to close
the query. What code do I add to the statement you gave me below to
do that?

There's nothing in that code, or the code you posted, that would open
the query in the background -- if by that you mean that the query is
opened in datasheet view. The mere fact that you use the query as the
recordsource for a form will not cause that. You must have code or a
macro that opens the query, using DoCmd.OpenQuery or the OpenQuery macro
action. Unless you have some reason for doing that -- and it doesn't
sound like you do -- you should locate the code procedure or macro and
remove that statement/action.

 

[Tracey]

I figured that out just as you must have been responding. A colleague who had
tried to help me with this put in code to open the query on form load. Found
it and took it out and everything works great! Thanks for being a great help!

 

[Dirk Goldgar]

I figured that out just as you must have been responding. A colleague
who had tried to help me with this put in code to open the query on
form load. Found it and took it out and everything works great!
Thanks for being a great help!

You're welcome; it was my pleasure.