Update Field2 Only If Field1 Value has Changed

[Ray Stantz]

Hello All:

I searched the forum but could not find the exact question or answer i was looking for. I have a form named Contract_Profile that is used for data entry, for this example the field name is Contact_Name. Form2 also has a field named Contact_Name that auto populates the value from Form1's Contact_Name using:

=[Forms]![Contract Profile]![Contact Name]   (In the second form's field default value)

The OnCurrent Event in the second form currently will only auto populate the second form's Contact_Name field if the field is currently blank using:

If IsNull(Me![Contact Name]) Or Me.Contact_Name = "" Then
       Me![Contact Name] = [Forms]![Contract Profile]![Contact Name]
       End If

However, because of this configuration the second form's Contact_Name field will not update if i change the value in the first form. I would like the OnCurrent Event in form 2 to check to see if Contact_Name in form1 Value has changed and if so, update Field2 on Form 2.

Hope i explained that right. Any help would be greatly appreciated.

Ray

 

[CEH]

You might try just dropping the If...then... statement......And just use..
Me![Contact Name] = [Forms]![Contract Profile]![Contact Name]
Right now you are telling it that only if the field on form 1 is blank or null then they are the same.... if not..then do nothing.
But if these two fields are coming from the same table.... You should be able to link form 2 to 1 by your ContactID field. It should populate then. You might need a docmd.requery on the OnCurrent event of the second form.
This IS of course assuming you have only one "ContactName" field in the DB.

 

[Ray Stantz]

Hey CEH, thanks for the response...

I had it the way you suggested originally but i found that the form was updating even if the information were the same and that was causing record corruption issues because two people would be on the record at the same time and becasue it was set like that the database tried to update for both users.

 

[WayneRyan]

Ray,

Try using the .OldValue property.

If Nz([Forms]![Contract Profile]![Contact Name].Value, "x") <> 
   Nz([Forms]![Contract Profile]![Contact Name].OldValue, "x") Then ...

Wayne

 

[CEH]

So a split DB.... So if you are opening form 2 from form 1 via a button... The changes on form 1 have not been saved yet, so it is pulling from the table for form 2. You could add a save command to the button or event that opens the second form. Force it to save your update. Someone else might jump in here if there is another event, besides moving to a new record, that triggers a save. I think your requery would also trigger a save.