unbound textbox value derived from subform
in [Form]
|
Prev: Conditional Formatting - Comparing field on form to field on a different table
Next: Display a message while a form loads
From: Ron on 24 Feb 2010 18:25 I've got an unbound text box, txtLatestDate on a main form intended to show the latest date from a series of dates in a subform. Using comboboxes, the subform shows a status and a date, several for each related record on the main form. Using VBA, I use DMax to find the latest date and set me.parent.txtLatestDate equal to it. It works, BUT I can't seem to get txtLatestDate to refresh as soon as I change an existing date in the subform (to a later value than any other). I've tried me.parent.txtLatestDate = (DMax expression) me.parent.requery from several different events (onclick, dirty, afterupdate, etc.) But only after picking the date twice does the refresh occur. How do I get it to occur instantly? And a corollary question. I notice that when I delete "event procedure" from the control source property (in this case, a text box), the underlying event code is still in the VBA. Does this mean that it is executing even though there's no indication that it exists in the control event property?!? Thanks, -Ron
From: Ron on 24 Feb 2010 20:33 Fixed my own problem. In the change event for the combobox, this does it: me.refresh me.parent.txtLatestDate = (DMax expression) Not sure why the refresh is needed, since the (join) table to which the cboBox is bound (which is also the domain of the DMax expression) appears to update instantly upon picking a new value for the combobox. But I shoulda known the difference between .requery and .refresh :( Still would like to know if there's a setting that auto deletes event code when the property is cleared in the form's design view. -Ron > I've got an unbound text box, txtLatestDate on a main form intended to > show the latest date from a series of dates in a subform. Using > comboboxes, the subform shows a status and a date, several for each > related record on the main form. Using VBA, I use DMax to find the latest > date and set me.parent.txtLatestDate equal to it. It works, BUT I can't > seem to get txtLatestDate to refresh as soon as I change an existing date > in the subform (to a later value than any other). I've tried > > me.parent.txtLatestDate = (DMax expression) > me.parent.requery > > from several different events (onclick, dirty, afterupdate, etc.) But > only after picking the date twice does the refresh occur. How do I get it > to occur instantly? > > And a corollary question. I notice that when I delete "event procedure" > from the control source property (in this case, a text box), the > underlying event code is still in the VBA. Does this mean that it is > executing even though there's no indication that it exists in the control > event property?!? > > Thanks, -Ron
From: Rob Parker on 24 Feb 2010 21:49 Hi Ron, No, there's no setting to automatically remove code when the Event Procedure entry is removed. And, IMHO, that's a very good idea, because sometimes the Event Procedure entry will get removed by accident, and I'd prefer not to have to reprogram it all - particularly for some of the large, complicated subroutines which I have, which may have taken days (or months) to fine-tune. If the Event Procedure entry is removed, the code will never be run. HTH, Rob "Ron" <Harvested(a)comcast.net> wrote in message news:hm4k1k$of2$1(a)news.eternal-september.org... > Fixed my own problem. In the change event for the combobox, this does it: > > me.refresh > me.parent.txtLatestDate = (DMax expression) > > Not sure why the refresh is needed, since the (join) table to which the > cboBox is bound (which is also the domain of the DMax expression) appears > to update instantly upon picking a new value for the combobox. But I > shoulda known the difference between .requery and .refresh :( > > Still would like to know if there's a setting that auto deletes event code > when the property is cleared in the form's design view. > > -Ron > >> I've got an unbound text box, txtLatestDate on a main form intended to >> show the latest date from a series of dates in a subform. Using >> comboboxes, the subform shows a status and a date, several for each >> related record on the main form. Using VBA, I use DMax to find the >> latest date and set me.parent.txtLatestDate equal to it. It works, BUT I >> can't seem to get txtLatestDate to refresh as soon as I change an >> existing date in the subform (to a later value than any other). I've >> tried >> >> me.parent.txtLatestDate = (DMax expression) >> me.parent.requery >> >> from several different events (onclick, dirty, afterupdate, etc.) But >> only after picking the date twice does the refresh occur. How do I get >> it to occur instantly? >> >> And a corollary question. I notice that when I delete "event procedure" >> from the control source property (in this case, a text box), the >> underlying event code is still in the VBA. Does this mean that it is >> executing even though there's no indication that it exists in the control >> event property?!? >> >> Thanks, -Ron >
From: Ron on 24 Feb 2010 22:21 Hi Rob, > No, there's no setting to automatically remove code when the Event > Procedure entry is removed. And, IMHO, that's a very good idea, because > sometimes the Event Procedure entry will get removed by accident, and I'd > prefer not to have to reprogram it all - particularly for some of the > large, complicated subroutines which I have, which may have taken days (or > months) to fine-tune. Makes sense, thanks. > > If the Event Procedure entry is removed, the code will never be run. > Aha. Good to know! So if I ever get sufficiently proficient to be popping in subs while strolling through the coding windows (fat chance :) ), I'd better remember to "activate" them in the form design. Thank you. -Ron
From: John W. Vinson on 24 Feb 2010 23:50
On Wed, 24 Feb 2010 20:33:35 -0500, "Ron" <Harvested(a)comcast.net> wrote: >Fixed my own problem. In the change event for the combobox, this does it: > >me.refresh >me.parent.txtLatestDate = (DMax expression) Nitpick: I'd use the AfterUpdate event (which fires when a selection is made), rather than the misleadingly-named Change event (which fires at every keystroke or mouseclick). -- John W. Vinson [MVP] |