Subform Question

[cheer]

I have created a subform, inside the subform, there are few columns, say columnA, columnB, columnC and columnD.

In the actual table link to the above subform, there are extra one field, columnE or fieldE which is not shown in the above subform. columnE or fieldE is a number field. Default value is 0

Question: When there is any change to the data at the columnA, columnB, columnC or columnD through the subform, i want the columnE or fieldE to record as 1.

What events and how to write the VBA codes ? Any suggestion is welcomed.

 

[apr pillai]

Try this simple trick.

1. Place ColumnE also on the Form and change its Visible Property Value to NO to keep it hidden from the User.

2. Define two Variables at the Global Area of the Form Module:

Dim xBefore, xAfter

3. on the Got Focus EventProcedure of ColumnA write the following Code:

Private Sub ColumnA_GotFocus()
xbefore = Me![ColumnA]

End Sub

4. on the Lost Focus EventProcedure of ColumnA write the following Code:

Private Sub ColumnA_LostFocus()
xAfter = Me![ColumnA]
if xAfter <> xBefore then
   Me![ColumnE] = 1
end if
End Sub

5. Repeat this for ColumnB, C & D.

Regards,

 

[Galaxiom]

Use the OnDirty event of the subsubform to update the value in FieldE.
(Also suggest rather than using a number field you use a Y/N field if it is only going to have two values. It is easier to query and uses far less memory to record.)

You don't need a control for FieldE on the form. After any change in any bound control on the subsubform, the following line will update the value in FieldE of the Current Record in the Record Source as you move to another record so long as there is no control by that name.

Me!FieldE = True

(Form references default first to the control names then to fields in their Record Source)

 

[Galaxiom]

The solution provided by apr_pillai while I was writing is clearly much more complex but it does have an advantage in that it will recognise when a record has been changed but reverted to the original value.

The OnDirty event simply notes that the value has been edited. Note also that this is the Form's OnDirty so it does not need to be set for each control on the form.

However as I previously pointed out, a hidden control is not required because you can refer directly to the field in the Record Source without the intermediary of the bound control.

 

[cheer]

Use the OnDirty event of the subsubform to update the value in FieldE.
(Also suggest rather than using a number field you use a Y/N field if it is only going to have two values. It is easier to query and uses far less memory to record.)

You don't need a control for FieldE on the form. After any change in any bound control on the subsubform, the following line will update the value in FieldE of the Current Record in the Record Source as you move to another record so long as there is no control by that name.

Me!FieldE = True

(Form references default first to the control names then to fields in their Record Source)

Wonderful solution. Simple statement.