Change Event not working for all the records of the Table.

[rd.prasanna]

Hi All,

Need clarification,
Actually I have a form where i have multiple textbox fields with the data type is date and the same is associated with a table.

but my concern is when u make chanegs in the 1st date field 2nd txtbox should calculate the second date (Eg: date2=date1+10)

when i did it using change event for the 1st txtbox its working fine with only 1st record of the database, when i move to the next record the same codes not working, why is it so..

below is the code what im using:

Private Sub FC1_Change()
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Trans", dbOpenDynaset)
Pos = rs.AbsolutePosition
MsgBox Pos
Me.Dirty = False
For i = 2 To 8
rs.Edit
rs.Fields("FC" & i) = rs.Fields("FC" & i - 1) + rs.Fields("Pln" & i) - rs.Fields("Pln" & i - 1)
rs.Update
Next i
For i = 2 To 8
If Weekday(rs.Fields("FC" & i), 2) = 6 Then
rs.Edit
rs.Fields("FC" & i) = rs.Fields("FC" & i) + 2
rs.Update


ElseIf Weekday(rs.Fields("FC" & i), 2) = 7 Then
rs.Edit
rs.Fields("FC" & i) = rs.Fields("FC" & i) + 1
rs.Update
End If
Next i
Set rs = Nothing
Set db = Nothing
End Sub

Kindly do the needful.

Regards,
Prasanna R.D

[ItsMe]

There are three things that stand out in your post.

1) Why are you using the change event? Every time the user types a character, the event fires. Maybe an afterupdate would be better

2) Why are you attempting to store a calculated field in a table? Sometimes it is not a bad idea. Most the time it is a bad idea. usually calculations change as time goes by. So, rather than updating a table every time a user looks at a given field, you can just run the calculation in an unbound textbox. No need for the extra step of updating.

3) Your code makes absolutely no sense to me.