Make one field match another field

[rplohocky]

Hello,
I am trying to find a way to have one field match another field on a form either by conditional formatting or some other way.

I have one field where i enter an appointment date [txtOLConedate].
I have another field that I would like to show the appointment date 7 days before. This field would "auto fill" when [txtOLConedate] is populated.

 

[The_Doc_Man]

Some simple VBA could do this, perhaps using the .LostFocus event of the txtOLConedate control. You would simply force the other control to match txtOLConedate if that 2nd control is still empty or otherwise not valid.

However, if the second date is in a bound control, just remember that doing it this way "dirties" the form and thus has side effects such as requiring the form to be saved or undone before you close it, as well as "phantom" saves that you didn't realize would occur but suddenly they do occur.

 

[bob fitz]

Set the Control Source property of the text box to an expression which uses the DateAdd() function. Something like:

=DateAdd("d", -7, [txtOLConedate])

 

[The_Doc_Man]

However, note that Bob's solution - because it isn't a bound field - would not be saved when you saved the record in question.

 

[rplohocky]

Some simple VBA could do this, perhaps using the .LostFocus event of the txtOLConedate control. You would simply force the other control to match txtOLConedate if that 2nd control is still empty or otherwise not valid.

However, if the second date is in a bound control, just remember that doing it this way "dirties" the form and thus has side effects such as requiring the form to be saved or undone before you close it, as well as "phantom" saves that you didn't realize would occur but suddenly they do occur.

The Doc Man, thanks for responding.
This is a bound field so using Bob's expression would not work in this case unless I unbound, which is a possibility if I can't find another way to to do this.

Do you have another way the will just auto fill the field and allow it to be saved in my table? Maybe an expression in the default???

 

[Mark_]

Bob's expression as a default should do very nicely for what you are looking for, unless you wished to change said field in cases where it already exists.

From what you are looking to do, do you really want this as a field in your record? It seems to be a computed date and, as such, easily calculated when needed. Or is it simply a default value you are looking for that could be changed in cases where that date is not available for one reason or another?

 

[arnelgp]

there are three ways you can do this.
starting with the most recommended.

1. Create a Query that will calculate
the Date for you, and you can get rid
of the other field, thereby saving
you extra space on your db.

SELECT *, [OLConedate]-7 As CalcDate FROM yourTable;

2. On the form, you can use [OLConedate] Current
event to automatically fill the "OtherField"

Private Sub OLConedate_Change()
    If IsDate(Me.OLConedate.Text) Then
        Me.OtherField = CVDate(Me.OLConedate.Text) - 7
    End If
End Sub

3. Use actual Calculated Field on your table
then you don't need to code or put it in a
Query:

FieldName FieldType Expression
OtherField Calculated [OLConedate] - 7

 

[The_Doc_Man]

Mark's question is apropos here.

If the 2nd field is FOREVER bound to be 7 days after the 1st field (or any constant number of days, it's immaterial to the theory), then you can just use the expression.

If the 2nd field has to be updated if someone misses a deadline then you have to store it separately which means it has to be bound which means you DO NOT WANT any kind of automation inherent in the definition of the field. This case screams for a Form-and-VBA solution that applies some smarts to whether or not to update the field.

Do you have another way the will just auto fill the field and allow it to be saved in my table? Maybe an expression in the default???

No, because of timing issues. The default will be applied when the empty record is created - but at that time, the 1st field's date hasn't yet been entered or otherwise defined, so a default has no base for its definition.

The ideal time for this is on a form where you can do some relatively simple VBA tests to verify that a date is being entered for the first time on the 1st field and that the 2nd field is currently undefined, at which point your code can assert the value of the 2nd field in time for it to be stored when you store the rest of the record.

 

[rplohocky]

there are three ways you can do this.
starting with the most recommended.

1. Create a Query that will calculate
the Date for you, and you can get rid
of the other field, thereby saving
you extra space on your db.

SELECT *, [OLConedate]-7 As CalcDate FROM yourTable;

2. On the form, you can use [OLConedate] Current
event to automatically fill the "OtherField"

Private Sub OLConedate_Change()
    If IsDate(Me.OLConedate.Text) Then
        Me.OtherField = CVDate(Me.OLConedate.Text) - 7
    End If
End Sub

3. Use actual Calculated Field on your table
then you don't need to code or put it in a
Query:

FieldName FieldType Expression
OtherField Calculated [OLConedate] - 7

Thanks arnelgp!
I used option 2. I believe this will be the best option for my application. Thanks again for your help along with all the others as well!

There some great people out there!