Update seperate table when a record is edited.

[Homegrownandy]

Assuming you guys aren't fed up with helping me ( I know you love it ).

I have a form where a user can change the scheduled start date for a job. On a sub form on the same screen is a list of notes relating to that job.

any notes added, automatically have todays date and are locked when the user clicks off.

When the scheduled start date is changed I need a note to be made. Either forced, then entered by the user or automatically.

So basically I'm looking for Ideas or feedback.

I was thinking of making the scheduled start appear in a small form and the button to make it come up could add a new note on the click event (possibly in a message).

Or even better (probably harder) any time the value is changed in the form a new note is added.

Other options could be a pop up form to add one note on a change.

Look forward to your opinions, Andy.

[orange]

Just thinking as I type - perhaps you could consider a "rescheduling function/process". Could be restricted to a certain user/role. Takes an existing Job, and changes the ScheduledStartDate (and other fields -resource(s), duration....) and within the process creates timestamped note(s).

Good luck.

[Homegrownandy]

So on default the date field is locked for editing. The user clicks to open a new small form with the notes field required. Not sure how to force the user to enter data. But im sure google will answer that one easily.

Don't even start on users/role. I'm no where near anything like that haha.

Andy,

[nick404]

The user clicks to open a new small form with the notes field required. Not sure how to force the user to enter data.,

Yeah you could just have a form for notes only (frmNotes), and have a button that opens it.. Or where ever they are changing the scheduled date you could have the afterupdate open the form as well in a dialog box so it is a sort of 'pop-up' so to speak.
DateField_AfterUpdate()
DoCmd.OpenForm "frmNotes", acNormal, , , ,acDialog
End

Could have the notes field required. Or this bit before they close the pop up form:
If IsNull(me.txtNotes) Then
MsgBox "Must enter a note, etc. etc."
End If

Another idea is that if the user changes the date have a message box come up telling them to kindly add a note in the notes field.

What kind of notes are you looking for them to make? Is it just a simple 'Scheduling date was changed from x to y? Or something with a reason why it was changed, etc?

[Homegrownandy]

'Scheduling date was changed from x to y' would be great if it was automatic. Then any additional notes the user could add themselves. Notes could include reasons for date changes, but also it would be used for anything else relating to the job (before the work is carried out).

our current excel scheduler has hundreds of notes.

Ill look more closely at your post tomorrow and see if I can get something working.

thank you for the replies.