Textbox ControlSource Issue

[RonL]

I have an unbound form with some textboxes in the header. They are for miscellaneous computations. For one of them, I've set the control source to a DSum function whose domain is an established query. This works, but not on form open. I'd like the value to appear in that box immediately on form open ie. double click from navigation bar, but the box remains blank until either I click on it, or use the tab key (which works even though it's last in the tab order) - after which is shows the anticipated value.



A small thing, but what's the trick to getting it to display properly when opening the form? I've tried, to no avail, simple things like doing a form refresh or requery on load, binding the whole form to the query, and playing with the properties of the textbox.

Thanks -Ron

[June7]

It should calculate and display when opening. Works for me.

What is the DSum() expression?

Post the query SQL statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

[RonL]

The control source for the text box is:

=DSum("qryBenesPrimary.BequestMax","qryBenesPrimary")

The query is:

SELECT tblBeneficiaries.Lname, tblBeneficiaries.FName, tblBeneficiaries.Addr1, tblBeneficiaries.Addr2, tblBeneficiaries.Addr3, tblBeneficiaries.City, tblBeneficiaries.State, tblBeneficiaries.Zip, tblBeneficiaries.Phone, tblBeneficiaries.BequestMax, tblBeneficiaries.BequestMin, tblBeneficiaries.ContingentBeneficiary, tblBeneficiaries.BequestContingent, tblBeneficiaries.Contingent
FROM tblBeneficiaries
ORDER BY tblBeneficiaries.BequestMax DESC;

It contains text, currency, and one boolean fields. Running Access 2010 on Win7x64. -Ron

Edit - I just discovered that if I open the form, click to design view and then back to form view, the textbox becomes filled in. Also, if I open the form DIRECTLY to design view, then click to form view, it's also filled in. Curious.....

[June7]

Not seeing any reason for this issue.

However, the query doesn't have any filtering and there are no table joins. Why not just reference the table in the DSum ?

[CJ_London]

I'm surprised it does work - your query has the field 'tblBeneficiaries.BequestMax' but your dsum is referencing 'qryBenesPrimary.BequestMax'. Not tested but perhaps it is quirk of dsum since you only need bequestmax and it figures it out for itself

[June7]

Good eye, Ajax.

Try simply:

=DSum("BequestMax","qryBenesPrimary")

or

=DSum("BequestMax","tblBeneficiaries")

[RonL]

First, thanx June for catching the lack of filter. Somehow, I deleted the where clause (built on the boolean field) while tinkering. Correct query is

SELECT tblBeneficiaries.Lname, tblBeneficiaries.FName, tblBeneficiaries.Addr1, tblBeneficiaries.Addr2, tblBeneficiaries.Addr3, tblBeneficiaries.City, tblBeneficiaries.State, tblBeneficiaries.Zip, tblBeneficiaries.Phone, tblBeneficiaries.BequestMax, tblBeneficiaries.BequestMin, tblBeneficiaries.ContingentBeneficiary, tblBeneficiaries.BequestContingent
FROM tblBeneficiaries
WHERE (((tblBeneficiaries.Contingent)=Off))
ORDER BY tblBeneficiaries.BequestMax DESC;

Doesn't fix the problem (as expected).

Nor does adjusting the DSum expression per your suggestions. Will fiddle with global settings. Thx for looking. -Ron

EDIT - I worked around. In Form Load, I set focus on the txtBox in question, then back to the first in the tab order. Now value shows immediately on open. Will mark solved, even though it's not really. If anyone wants to test whether this is a genuine buglet, you gotta ensure when the form opens the textbox with the domain function as control source does not get focus.

[June7]

I tested with the textbox having focus and not having focus when form opens - no difference, still calculates.

[RonL]

Thank you for testing June! I did some searching and find others have reported a similar, or the same, problem. More than one reported it was fixed by - are you ready for this - rebooting the computer. So I commented out the refocusing code, rebooted, and guess what - it works! Seems like the problem only appears for those who reboot less frequently than every 20 days or so, a category to which I decidedly belong. There's speculation the issue derives somehow from an interaction between Windows Update and Access, but no one really knows. Sounds like MS is aware, but hasn't taken corrective action, at least not for Access 2010. Go figure.

Another buglet factoid submitted to the Access Community for its consideration from..... the Twilight Zone.

EDIT - Actally, MS HAS addressed the problem:
https://support.microsoft.com/en-us/kb/2827138
EDIT - More recent: https://support.microsoft.com/en-us/kb/3055045

[CJ_London]

Something else to consider is your form may have become cluttered with old/deleted code (it never goes away!)

To clear all the rubbish out, you need to decompile the database - see this link

http://www.access-programmers.co.uk/...ight=decompile

[RonL]

Tx Ajax. I had done a compact/repair but not decompile. Will do eventually. But see my previous post. -Ron

[June7]

In the 8 years I have been using Access and building dbs, have never run a decompile. Just tried. Appears to be no impact. Db is same size. This is done on an accdb?

[CJ_London]

Yes - accdb. It will only reduce for code - I usually see reductions of 10-20%, but then I'm often rewriting it and have separate front ends so files are quite small in the first place. No point doing it on back ends since they do not contain code