Updating controls when form has no data

[John S. Ford, MD]

I'm working with Access 97 and have a form with an embedded subform (whose
AllowAdditions property is deliberately set to False). On the footer of the
subform there are two TextBoxes (txtCurrentRecord and txtTotalRecords). I
put the following code in the subform's OnOpen and Current events:

If Me.RecordsetClone.RecordCount = 0 Then
txtTotalRecords = 0
txtCurrentRecord = 0
End If

My thought was that if the MAIN form moves to a record for which there are
no child records in the subform, it would be reflected by the two TextBoxes.
But the subform seems to ignore this and the TextBox values are not updated
to 0 and 0. Apparently, this scenario doesn't trigger either of these
events. The subform disappears (except for the footer) because I've got
AllowAdditions set to false. How can I get those TextBoxes updated?

Am I picking the wrong event? Is there a better test for an empty form then
the one I'm using?

John

 

[Ken Snell]

Try putting the code on the main form's OnCurrent event:

Private Sub Form_Current()
If Me.SubformControlName.Form.RecordsetClone.RecordCount = 0 Then
Me.SubformControlName.Form.txtTotalRecords = 0
Me.SubformControlName.Form.txtCurrentRecord = 0
End If
End Sub

 

[Ken Snell]

see reply to your post in another newsgroup.

 

[John S. Ford, MD]

Ken,

I've tried putting that code in both the OnOpen and OnCurrent events. It
seems to get bypassed either way.

John

 

[Albert D. Kallal]

Are those two text boxes bound, or un-bound?

I mean, if they are bound to some expression, then you obviously can't set
the values of those two controls if they have an expression

Further, is your problem that you need to ref these values in the "main"
form, and when no values occur in the sub form..they don't have a value?

For example, a expression to return he number of records in the sub-form
could be:

txtbox name txtRecCount

in the data source for the txtRecCount you can put:

=sum(1)

In you main form, then you can go:

msgbox "number of records in sub form is " & me!MySubForm!txtRecCount

of course, if there is NO records in the sub form, then txtRecCount will be
null, so you can use:

msgbox "number of records in sub form is " & nz(me!MySubForm!txtRecCount,0)

The nz() function will return 0 if the value is null

 

[Ken Snell]

Not knowing the answers to Albert's questions elsethread, I'm hesitant to
suggest something else. But just to clarify...you have tried this code in
the * main form * 's OnCurrent event?

 

[John S. Ford, MD]

Ken,

I put this code in the SUBFORM's OnOpen and OnCurrent events. I'll try
putting it in the MAINFORM's OnCurrent event and see what happens.

Also both of the TextBoxes are not bound to an underlying dataset.

John

 

[John S. Ford, MD]

Well I tried it in the MAINFORM's OnCurrent event (with the appropriate
references to the subform) and got the same result. It has me baffled.

John

 

[Albert D. Kallal]

Also both of the TextBoxes are not bound to an underlying dataset.

That is not what I asked. I asked if the txtBoxes are un-bound....BIG
difference.

Of course you can't assign a value to a control that is bound to an
expression (ie: it not bound to recordset, but has a expression for the
control source setting of the control))).
(how would the control know to use the expression, or the value you
assign???).

So, I have to assume the txtBoxes you are talking about are un-bound, as you
can't assign a value to a textbox that has some expression.
(of course, this then begs the question how did (or do) you assign values to
these txtBoxes WHEN you have data in the sub form? In other words, how do
these textboxes EVER get a value?

I have to admit that my question is a "long shot", and not very likely.
However, we are running out of the obviously, and , so I had to guess/ask if
in fact those textboxes are un-bound (once again, by un-bound..it means the
"control source" setting for the control is empty (not set))

 

[John S. Ford, MD]

Albert,

The TextBoxes are completely unbound (ControlSource is blank). Actually the
code I use to fill them works perfectly as long as there's at least one
record in the subform's underlying dataset. It only gets bypassed when
there are NO records.

I do appreciate you trying to help!

John

 

[Ken Snell]

Perhaps the value of the recordcount of your subform's recordsetclone is not
zero in these situations. Try using a Msgbox to display the value to you in
the mainform's OnCurrent event.

Also see Albert's suggestion... I'm not 100% clear on what the setup would
be, so you might ask him for more info if you need it.

 

[user]

Perhaps the OnCurrent event doesn't fire if there are no records (after all
it's supposed to fire when you move to a new record)? Did putting the code
into the parent form's OnCurrent event not work?

Doug

 

[John S. Ford, MD]

Doug,

I put the code in both the OnOpen and OnCurrent events of the SUBFORM. Is
that wrong?

John

 

[user]

If you put it in the OnCurrent of the main form it should do what you want,
since you want to recompute the number of subform records each time you move
to a new record on the main form. I haven't tried it but it seems like it
should work.

Doug