In an Access 2000 database, I have a form with an unbound TextBox control named txtFullAddress. For complicated reasons, I can't make this control bound, but I want it to appear to be bound from the user's point of view. Specifically, I want the following to happen:
1. Whenever navigating to a record, the control gets loaded from the field. (If navigating to the "new" record, the control is set to Null.)
2. If the user starts editing the contents of the control, the icon in the record selector changes to indicate the record has unsaved changes.
3. When the record is saved, the control's contents are copied to the field.
4. If the user performs the Undo operation, the control reverts to its original value.
I have solved the first 3 requirements as follows.
1. Form_Current loads the control from the field (or sets it to Null if Me.NewRecord = True).
2. txtFullAddress_Change assigns an unrelated bound control to itself, which causes Access to switch the icon in the record selector. Because a bug prevents the Dirty event from happening if the first field change occurs in code, txtFullAddress_Change also calls Form_Dirty.
3. Form_BeforeUpdate stores the text box value in the field.
Number 4 (Undo operation) breaks down into 3 cases corresponding to the 3 different kinds of Undo: "Undo Typing/Cut/Paste", "Undo Field", and "Undo Record". The first two cases are taken care of automatically by Access. The third is not. Specifically, if txtFullAddress is edited, and then loses the focus, and then Undo Record is invoked by the user, txtFullAddress is not reverted.
I can solve this problem the following way:
1. Form_Dirty sets the TimerInterval property to 100 (1/10 second).
2. Form_Timer tests the Dirty property. If True, it just exits. If False, it resets TimerInterval to 0, refreshes txtFullAddress from the field, and exits.
This actually works, and the delay is short enough not to be evident to the user. The only things I dislike about it are that (1) it eats up some CPU time the whole time editing operations are going on, and (2) during development, the VBA window is jittery and troublesome to use, because that Timer event keeps switching it between "ready" and "running" modes. If I'm trying to edit code or enter something in the Immediate Window, keystrokes sometimes get lost. It's a real nuisance.
I experimented with trapping the Undo menu command, toolbar button, and keystrokes. The keystrokes are a problem because it's hard to tell which kind of Undo operation is about to happen. I don't want to revert txtFullAddress when Undo is being used to undo typing in some other control. Worse, I can't trap the menu command and toolbar button without replacing the built-in CommandBarButtons, but if I do that then Access doesn't update their captions any more, and I have no way to figure out which caption should be there.
Has anybody ever found a better way to detect an Undo Record operation in order to revert an unbound text box? (Wouldn't it be nice if Microsoft had given us a Form_Undo event? Hint, hint!)
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
1. Whenever navigating to a record, the control gets loaded from the field. (If navigating to the "new" record, the control is set to Null.)
2. If the user starts editing the contents of the control, the icon in the record selector changes to indicate the record has unsaved changes.
3. When the record is saved, the control's contents are copied to the field.
4. If the user performs the Undo operation, the control reverts to its original value.
I have solved the first 3 requirements as follows.
1. Form_Current loads the control from the field (or sets it to Null if Me.NewRecord = True).
2. txtFullAddress_Change assigns an unrelated bound control to itself, which causes Access to switch the icon in the record selector. Because a bug prevents the Dirty event from happening if the first field change occurs in code, txtFullAddress_Change also calls Form_Dirty.
3. Form_BeforeUpdate stores the text box value in the field.
Number 4 (Undo operation) breaks down into 3 cases corresponding to the 3 different kinds of Undo: "Undo Typing/Cut/Paste", "Undo Field", and "Undo Record". The first two cases are taken care of automatically by Access. The third is not. Specifically, if txtFullAddress is edited, and then loses the focus, and then Undo Record is invoked by the user, txtFullAddress is not reverted.
I can solve this problem the following way:
1. Form_Dirty sets the TimerInterval property to 100 (1/10 second).
2. Form_Timer tests the Dirty property. If True, it just exits. If False, it resets TimerInterval to 0, refreshes txtFullAddress from the field, and exits.
This actually works, and the delay is short enough not to be evident to the user. The only things I dislike about it are that (1) it eats up some CPU time the whole time editing operations are going on, and (2) during development, the VBA window is jittery and troublesome to use, because that Timer event keeps switching it between "ready" and "running" modes. If I'm trying to edit code or enter something in the Immediate Window, keystrokes sometimes get lost. It's a real nuisance.
I experimented with trapping the Undo menu command, toolbar button, and keystrokes. The keystrokes are a problem because it's hard to tell which kind of Undo operation is about to happen. I don't want to revert txtFullAddress when Undo is being used to undo typing in some other control. Worse, I can't trap the menu command and toolbar button without replacing the built-in CommandBarButtons, but if I do that then Access doesn't update their captions any more, and I have no way to figure out which caption should be there.
Has anybody ever found a better way to detect an Undo Record operation in order to revert an unbound text box? (Wouldn't it be nice if Microsoft had given us a Form_Undo event? Hint, hint!)
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein