Repeat from previous field if empty after update

[Carenne]

I have three fields in a form (not the same fields I described in a previous post) which will often be sequential (one-time) repeats of the same field in the previous record. I know I can use the Ctrl+' feature to repeat the data from the previous field, but I'd like to make it even easier and quicker.

What I'd like to do is (I'm assuming through an AfterUpdate Event Procedure), if the field tests true for being empty, to then have it repeat the field immediately precediing record. What is the simplest way to accomplish this? I thought I'd have to use a DLookup or DLast statement and have to somehow get the record ID from the previous field but I haven't been successful in doing that. Meanwhile, I've created a quirky workaround using dummy fields but it makes for a very cumbersome looking form and I just know there's an easier way. FYI, one field is a numerical combo box, another a date field, and another a text field.

Thanks in advance!

Karen Schouest

 

[RickSpr]

You only want the value to be filled in on the next record, right? Not on each subsequent record, as you do with the DefaultValue property logic?

The tricky part here is to get the timing right. You could save the values in the controls' AfterUpdate event, but what happens if the user doesn't enter anything? The AfterUpdate event for the control never fires, so your saved value from the previous record never changes, and gets repeated again and again until they do enter something. This is similar to setting the DefaultValue.

I'd suggest using the Form_BeforeUpdate event. Create a module-level variable (in the Declarations section of the module) for each value to be saved. Then code something like this for each control you want a one-shot for:
If IsNull(Me!ctlname) Then
Me!ctlname = CLng(modulevariable)
modulevariable = Null ' this makes it a one-shot
Else
modulevariable = Me!ctlname ' save for next record
End If
Note that the module-level variables must be Variant types in order to be able to assign Null to them. Also, since these are Variants, you will need to use CDate(), CLng(), etc. conversion functions when assigning them to the controls. Otherwise you might have the same problems you had with the DefaultValue code I gave you before.

Note: You won't be able to get this to work with the Form AfterUpdate event, because the record has already been saved by then. Doing it in the controls' AfterUpdate events would work, but only if they enter something, and of course if they enter something you don't need the saved value from the last record anyway. That's why I recommended the Form_BeforeUpdate event. But there's a disadvantage to this: the user will never see the repeated value going into the field.

If you need the user to see the one-shot defaulting (and I'd recommend it), we might be able to put this code in each control's Exit event, which fires when they tab out of (or otherwise leave) the field. I don't use the Exit event often, though, so you'll have to try it and see. Rick Sprague

 

[Carenne]

Rick -- A THOUSAND thank-yous to you for taking time out of your Sunday to post those WONDERFULLY detailed responses to my questions in this and my other post! What a wealth of information! I have printed them out AND saved them in a safe place on my computer and I am going to pore over them in slow motion some tonight and first thing in the morning. I'll come back and let you know the outcome after I have put your fabulous advice to work. Thanks again ... and again ... and again!!!!

Karen