Reflect Control Source Change via VBA

[Kou Vang]

I have a series of unbound textboxes on a form. I run a click event that
then changes the control sources of the textboxes to Dlookup formulas. The
problem is that once the event is done working, I have to maually click on
the textboxes to see the reflected return of the Dlookup in the control
source. I tried setting focus after changing the ControlSources but that
didn't work either? What can I do to reflect the changes of the control
source? I could just actually put the Dlookup formulas in each textbox, but
I have like close to 100 textboxes! Thanks.

Kou

 

[Allen Browne]

Recalc forces the form to recalculate the calculated controls, so use:
Me.Recalc

Using 100 x DLookup() expressions on the one form is going to be very slow
(and resource-hungry.) It could probably be done better with a bound form
(assuming you have a relational data structure to start with.)

 

[Kou Vang]

I have struggled with this as well. I am trying to reproduce a paper form
which on it has several collection boxes, a 5 X 60 2 page sheet. I am only
using one column bound to a table, while the other 4 unbound columns are for
just looking up what other samples have been taken based on the same project
code. Therefore after about the 3rd iteration of this form, I settled on the
dlookups. I thought about creating a recordset and populating all the
unbound textboxes as well, but just went with the dlookup. Any suggestions?
Thanks for the help.

Kou

 

[Kou Vang]

I tried the Me.Recalc and it didn't work. I also tried Me.Repaint too. I
placed this inside my for statement after I update the control sources as
well as at the end right before exiting the sub. Strange?

Kou

 

[Allen Browne]

If you placed the Recalc after you reassigned the ControlSource and it did
not work, then something else is wrong with the expressions you are using.

I don't believe this approach is useful, so I have no further suggestions to
offer.