Reflect Control Source Change via VBA

  • Thread starter Thread starter Kou Vang
  • Start date Start date
K

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
 
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.)
 
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
 
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
 
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.
 
Back
Top