How to "refresh" a form when it becomes active.

[sheusz]

Good afternoon all. I'm hoping that I can explain this clearly in order to gain some assistance.

I have a customer form that has a button on it that, when clicked, opens a sub form showing records associated with the customer.




Overlaid on the button is an unbound text box that shows the number of records in the associated subform table. This works nicely.

The problem I have is that when I click the button, open the subform, then close the subform the unbound text box disappears. I'm guessing that it is actually now behind the button, hence obscured.




Is there a way that I can "refresh" the screen without requerying the customer form?

I've tried this below, but the problem is that this causes the record pointer to move to the first record in the customer table.

Private Sub Form_Activate()
    Me.Requery
End Sub

Basically, what I am wanting is for the unbound text box to reposition over the top of the subform button.

Thank you in advance.

[jojowhite]

why are you Overlaying the the button with an Unbound textbox?
if you want to put the Textbox in front, you can try to SetFocus on it on
your Activate event:

Private Sub Form_Activate()
     Me!UnboundTextboxName.SetFocus
End Sub

[davegri]

I'm guessing that it is actually now behind the button, hence obscured.

Why not temporarily separate the two, so that you can see what is happening?
Also, your code might require the subform to be open in order for it's record count to remain in the textbox.

[sheusz]

why are you Overlaying the the button with an Unbound textbox?
if you want to put the Textbox in front, you can try to SetFocus on it on
your Activate event:

Private Sub Form_Activate()
     Me!UnboundTextboxName.SetFocus
End Sub

I'm overlaying the textbox on the button so that the user can see how many service records are associated with that customer.

I had already tried SetFocus as you described, but the problem with this is that the textbox needs to be enabled, which I don't want. Its contents are populated on the customer forms OnCurrent event and I don't want the operator to be able to click the text box.

Me.txtServRecCount = DCount("[CustomerID]", "[xServices]", "[CustomerID]='" & Me.CustomerID & "'")

[sheusz]

Why not temporarily separate the two, so that you can see what is happening?
Also, your code might require the subform to be open in order for it's record count to remain in the textbox.

Hi davegri

Separating the two solves the problem, but it's not as visually appealing (to me at least) as having the number of records overlayed on the button.

[jojowhite]

you can totally Remove the code on the OnCurrent event by Assigning the formula as the
ControlSource of your textbox:

on design view of your form, click on the txtServRecCount textbox and on it's
Property->Data->ControlSource, put:

=DCount("[CustomerID]", "[xServices]", "[CustomerID]='" & Nz(Me.CustomerID, 0) & "'") 

Then remove the code on the Current Event of your form.

With this method, the Textbox is Readonly, so users cannot
change the value in it.

or just add a Caption to your button and on the Activate event,
assign the caption to it as your formula does:

Private Sub Form_Activate()
Me!TheButtonName.Caption = DCount("[CustomerID]", "[xServices]", "[CustomerID]='" & Nz(Me.CustomerID, 0) & "'")
End Sub

or another possible approach is to Setfocus on Another Control (except the button) on
your form:

Private Sub Form_Activate()
'set focus on the Counting textbox
Me.txtServRecCount.Setfocus
'set focus on another textbox/control on the form
Me.AnotherTextboxOnForm.Setfocus
End Sub

[June7]

You say you are opening and closing a subform but subforms are not "opened" and "closed", forms are. So are you actually opening and closing an independent form?

[sheusz]

Hi JoJo

Thanks for your suggestions.

I tried this as you suggested in the control source for the text box

=DCount("[CustomerID]", "[xServices]", "[CustomerID]='" & Nz(Me.CustomerID, 0) & "'") 

but the text box displays Name? So there is some sort of syntax issue. (BTW CustomerID is a text field, not numeric.)

Regardless of that, this issue remains the same.

As for the caption option on the button. I think that because there is a bitmap on the button, captions won't work.

I had already tried you third suggestion, but again the text boxes have to be enabled in order for SetFocus to work, no matter how briefly.

Private Sub Form_Activate()
'set focus on the Counting textbox
Me.txtServRecCount.Setfocus
'set focus on another textbox/control on the form
Me.AnotherTextboxOnForm.Setfocus
End Sub

But then I tried just setting the focus on another button on the form without setting focus on the textbox and that seems to have resolved the problem. Go figure.

[sheusz]

You say you are opening and closing a subform but subforms are not "opened" and "closed", forms are. So are you actually opening and closing an independent form?

You are correct.

[June7]

Can't use Me. in a textbox expression, only in vba, so just remove it. Just a little "oops" by jojo.

[sheusz]

Can't use Me. in a textbox expression, only in vba, so just remove it. Just a little "oops" by jojo.

OK. That did it.

=DCount("[CustomerID]", "[xServices]", "[CustomerID]='" & Nz(CustomerID, 0) & "'") 

Still not sure which is the best approach.

Using the OnCurrent event to set the value in the text box or the text box control data source. Both work, but are there benefits in one or the other?

[jojowhite]

then you temporarily Enable it:

Private Sub Form_Activate()
'enable and setfocus
With Me.txtServRecCount
     .Enabled = True
    .Setfocus
End With
'set focus on another textbox/control on the form
Me.AnotherTextboxOnForm.Setfocus
'disable it again
Me.txtServRecCount.Enabled =False
End Sub

Name? means the controlname on the Expression is the same as that
of the Table name, so Access is somewhat confused.

Textbox is not the same as Fieldname, so it is good practice
not to name your textbox same as the Fieldname where it
is bound. You can prefix the name of the textbox with "txt" (+Fieldname).

[June7]

I have noticed domain aggregate function in textbox slowing performance of form with large dataset.

As for setting caption property option - buttons can have an associated label control, use VBA to set its Caption. And visibility is no longer issue.

jojo, NAME? was due to Me. in textbox expression. Since removing it fixed, circular reference was not issue.

[sheusz]

Hi jojo and June7.

Thank you both for your help.

In the end the solution was simply to set focus to another button on the customer form when it become active

Private Sub Form_Activate()
    Me!CloseBtn.SetFocus
End Sub

and I will assign the value of the text box in the OnCurrent event, because I can see that the dataset will get quite large over the years.

Me.txtServRecCount = DCount("[CustomerID]", "[xServices]", "[CustomerID]='" & Nz(Me.CustomerID, 0) & "'")

[June7]

More specifically, I noticed a lag in Domain Aggregate calculation refreshing when navigating records.