Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    18

    Red face Unanswered: Change Event not working for all the records of the Table.

    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
    Last edited by rd.prasanna; 10-17-13 at 05:23. Reason: Code updated

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    at no point in your code do you move tot he next row in the record source
    so all your changes are done on the first row in the recordset
    you'd need to issue a rs.movenext
    you also need to trsap for a=the end of rows int he recordset
    eg
    rs.EOF = true
    mind you might also want to trap for no records
    eg:- where rs.bof = true

    BTW it makes it musch easier to understa\nd your code if you poist it here inside code tags (the # button in the advanced post page or
    eg:-
    Code:
    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
    also please indewnt your code, againit makes it easier to spot code blocks/loops
    Code:
    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
    Last edited by healdem; 10-17-13 at 06:02.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    18
    Hi healdem,

    Thank u for your reply,
    I know i havn't used the rs.movenext in the coding so its woriking for the same record.

    if i use the do until loop till EOF condition and the codes will be applied for all the records, which i dont want.

    i wnat to apply the changes to the current record only.

    sorry i did not used the (#) and indents for the coding.

    is change event if correct for this.???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •