Hide subform footer controls if no records present (AC2007)

[AOB]

Hi there,

I have a subform which lists a set of records. In the footer section of the subform, I have a number of controls which display calculations based on the records present.

The number of records in the subform will change (reduce). Basically, this is a review / approval function, the idea being that each record will be checked and approved, which will then remove it from the list.

So eventually, all of the records in the subform should disappear (the subform is requeried each time the user approves a record)

I want to be able to hide the controls in the footer section once the subform has been cleared of all records.

But not sure
a) how to determine when the subform's recordset reaches zero and
b) what event to use to fire the code to hide the footer controls.

This is my basic attempt :

Private Sub [COLOR=red]Form_Current[/COLOR]()
[INDENT]Dim ctl As Control
 
For Each ctl In Me.FormFooter.Controls
 
[INDENT]ctl.Visible = Not ([COLOR=blue]Me.Recordset.EOF[/COLOR])
 
[/INDENT]Next ctl
 
[/INDENT]End Sub

But it doesn't work (it only seems to fire when the subform is loaded, not when it is requeried after each approval?)

Can anybody point me in a direction? I've tried Form_Query, Form_DataChange and Form_DataSetChange but no joy with those either.

Thanks

AOB

 

[Insane_ai]

1. I don't like the use of Me. Forms![Form Name] is more consistent and less error prone.

2. Try this
Private Sub Form_Current()Dim ctl As Control
If Forms![Form Name].RecordSet.RecordCount >1 Then
For Each ctl In Me.FormFooter.Controls
ctl.Visible = False

Next ctl
Else
For Each ctl In Me.FormFooter.Controls
ctl.Visible = True

Next ctl
End If
End Sub


.EOF only registers that the recordset is at the end of its list. If you are eliminating records throughout the process, the record count will eventually be zero but not .EOF

In the past I have tried to evaulate RecordCout = 0 without success but >1 works for me. There may be a better solution to your issue or the recordcount evaulation but this should work.

 

[AOB]

Thanks Insane_ai

I tried substituting [Forms]![sfmApprovals] for Me but got an error :

Run-time error '2450':
Can't find the form 'sfmApprovals' referred to in a macro expression or Visual Basic code

Also tried including the parent form, i.e. [Forms]![frmApprovals]![sfmApprovals] and get a different error :

Run-time error '438':
Object doesn't support this property or method

However, Me.Recordset.RecordCount does appear to work?

(As an aside, would I not need to check for RecordCount > 0, or even RecordCount >=1, rather than RecordCount > 1? I don't want the footer controls to disappear with one record still remaining?)

The event trigger is still an issue though; it only fires when the form is loaded, not as each record is updated and the subform requeried (with the reduced dataset) I've used a breakpoint on the first line to trap when it fires and it only hits on load.

What is the correct event to use to trigger this code? And what is the problem with using Me (the sub is in the subforms code and will only be fired from within it?)

Thanks

AOB

 

[AOB]

Just tested with multiple records - it seems the Current event fires as long as there are records present in the subform recordset. It's only on the last one (which is where I want something to happen) that the Current event does not fire.

So I need an event which fires regardless of whether there are records present in the dataset or not.

 

[Insane_ai]

If Me is working, stick with it for now then troubleshoot it after you get the code working the way you want.

>1 is basically zero in this context. If a single record exists in the subform then the recordcount is not >1.

This thread explains the Form_Current event pretty well. To answer your question on which event to use I need more information. I am trying to imagine your forms working so correct me if I am wrong.

User opens teh main form with the subform listing items to review.
The user clicks on an item in the subform to review.
A new form with the detail opens up, the data is reviewed and edited as needed then marked as complete which would eliminate it from the list.

If this is the case, initiate a requery of the subform when this second form is closed or when the approval field is updated. This should look to see if the form is open so the event doesn't trigger when the form used in a different context.

Check for form being open:

If CurrentProject.AllForms("frmMyForm").IsLoaded Then '(actual code from link)
     [I]Me.subform.requery (pseudocode for reference)[/I]

From:
http://www.access-programmers.co.uk/forums/showthread.php?t=77119

I hope this gets you in the right direction.

 

[AOB]

Thanks Insane_ai

>1 is not basically zero. It is 2 or more when dealing with integers. I only want to hide the footer controls when there are 0 records left in the subform. If I use .RecordCount > 1 as my check, the controls will hide when there is 1 record left which is not what I want.

You're pretty much spot on with regard to the mechanics of the form, although no updates are made to the record. The user can only approve a record (which fires an UPDATE query) or reject a record (which fires a DELETE query).

I have three command buttons per record in the continuous subform. One to open the record in detail (in a separate popup, although it is purely for reference purposes, they cannot make changes) The other two to approve or reject respectively.

Both the approval and reject buttons requery the subform once the UPDATE or DELETE query has been completed :

Private Sub comApprove_Click()
 
' Code to construct SQL Update query, which includes logic to flag the record as approved and therefore exclude it from the query on which the subform's recordsource is based
 
    Me.Requery   ' To refresh the dataset
    Me.Recalc    ' To refresh the calculated controls in the footer
 
End Sub

The form has to be open because you can only approve or reject through it. So I don't really see the point in checking that the form is loaded - the code is driven from a button click event within it, so by definition it HAS to be.

I think the problem is that the Current event is not the correct event to use in this scenario. It only fires while there are records in the queried recordset. When there are none, it does not fire. I need a different event?

 

[Insane_ai]

< less than
> greater than. Sorry for the typo.

I would piggy back the approve / reject event to requery the subform.