Auto update next record's value on change to previous record

[rubyred]

I have 2 fields on a form - StartTime and StopTime.

To prevent any gaps in time, the field "StopTime"s value is automatically copied to the next record's StartTime field.

This form in in datasheet view.The problem arises when we go back and change a "stoptime" value after a few records have been entered. It will not, at this point, change the following record's "starttime".

The code used to initially have this happen is:

If NewRecord Then

StartTime.DefaultValue = Chr(34) & StopTime & Chr(34).

How can I get the "stoptime"s value copied to the next "starttime" AFTER A CHANGE has been made to the preceeding stoptime?

Thanks in advance for any help.

 

[cogent1]

Private Sub StopTime_AfterUpdate()


Dim NewStartTime As String 'set this to your data type, may be date.

NewStartTime = (Me!StopTime)


DoCmd.GoToRecord , , acNext


Me!StartTime= NewStartTime


End Sub

 

[rubyred]

Thanks cogent1 for your reply and help. It works!!!! I am very grateful. . . .

However, it has somehow caused my data entry to be a litte amiss. Now when I key in stoptime as a regular entry, the focus goes immediately to the next record's stoptime instead of going to the next control on the first record.

I have tried to set focus to this next field on the current record - and it sets the focus to the desired control, but on the next record and not the one above.

How can I get the focus back to the next control on the current record instead of jumping to the next record's stoptime. Remember the other code that I stated in my first post that I already have in the afterupdate event. I don't know if this has some effect on what is happening or not.

Thanking you in advance for your help. . . .

 

[cogent1]

Rubyred

try adding

DoCmd.GotoRecord,,acPrevious

before EndSub

It may be necessary to also add

DoCmd.GoToControl "MyNextFieldThatIWantToBeIn", (substituting your own field name of course!), because it will probably step back to the first field in the previous record...

 

[fernin8r]

have you tried adding

DoCmd.GoToRecord,,acPrevious

Like this:

Private Sub StopTime_AfterUpdate()


Dim NewStartTime As String 'set this to your data type, may be date.

NewStartTime = (Me!StopTime)


DoCmd.GoToRecord , , acNext


Me!StartTime= NewStartTime


DoCmd.GoToRecord,,acPreivous


End Sub

Then you can set the focus to the correct field. I think that's what you're asking. Another question for you is, you said that you already had code in the AfterUpdate event. Is that the Forms AfterUpdate Event or the StopTime's AfterEvent?

 

[rubyred]

OK! That did it!!!!! Thanks to both of you!!!

I'll be back shortly with a few more questions I hope I won't be a pest. Before I ask a question, rest assured I have already spent a couple of hours trying to figure it out, but because I am so green, it's really hard for me at this point. This is about the best way I have experienced so far to learn VBA - seeing it in action and analyzing it as such. I have learned so much already!

Again, thanks to both of you for your time and your wisdom!!

 

[rubyred]

Oooops! Fernin8R, I'm sorry, I didn't see your question to just a second ago. The first AfterUpdate Event for the stoptime is in the stoptime's control. Why do you ask?? Should it be in the form's.

This is actually a subform of, of course, in a main form. Just the two. Main form with this one subform.

Thanks!