Fire event before leaving modified record

[David R]

This seems fairly straightforward, but I can't see the forest for the trees.

I want to be able to load another form and recalculate meetings if a particular subform is changed. The problem comes that when I try to fire the event when leaving the record, it moves to another record before the new form loads, preventing me from reading the saved data.

I've read up on When Events Occur, but cannot find any event to actually stop the program from leaving that record. I can't use BeforeUpdate because then it gets caught in a code loop trying to save the record before it can use it...

Which other events allow you to cancel that action? I cannot predict how they will leave the form: it may be by closing the form, moving to another record, or closing the database entirely.

Thanks,
David R

 

[llkhoutx]

On your form BeforeUpdate check

if me!Subform.dirty = true then
' subform has changed
' do whatever
end if

 

[David R]

Did you actually read the question?

I know how to check if the form has been changed; what I need is a way to prevent the program from leaving the subform before it loads this new form/runs the code.

If I interrupt BeforeUpdate, it doesn't save the data, and therefore when it tries to read the new data, it can't. I'm going to try to use .Value to access the data on the screen, rather than what is saved, but I wondered if there is a more elegant way to do this.

 

[llkhoutx]

Use the event I've previously given and do what ever code you want. One the code starts, it all runs, regardless of what form you go to.

To figure out how to save the record, put a button on your subform using the wizard to save the record. Then copy the code code generated into the subject event. Then delete the code the wizard generated and button.

Your then home free.

 

[David R]

Getting closer...

OK. I can get around the DoCmd.RunCommand acCmdSaveRecord loop with the Deactivate event - except that Deactivate doesn't fire for subforms, or when you open a MsgBox.

Here's the relevant code snippet:

    If t Then
        If MsgBox("Do you wish to run an update of meetings for this group?", vbYesNo + vbQuestion + vbDefaultButton1, "Meeting data changed") = vbYes Then
            Me.l = True
            t = False
            DoCmd.OpenForm "selectMtgDates"
            Forms!selectMtgDates.SetFocus
            Forms!selectMtgDates!Page_Recalculate.SetFocus
            Cancel = True
            Debug.Print "sGMBU: " & Now
            Debug.Print Me.MeetingOrder
    
        End If
    t = False
    End If
End Sub

t being the check for dirty values, and Me.l being the current kludge to get this working.

What I've done now is to set an unbound field l to be True when the applicable fields have been changed; the DoCmd.OpenForm action sparks the Form_Deactivate action on the main form, which I then use to save the entire record. I hope.

Anyone got any better ideas? This is supremely ugly - if I just knew how to force a save on a record in another form, I wouldn't be dealing with all of this quite so much.

David

 

[Rich]

Since the BeforeUpdate event is only triggered when the form becomes Dirty there is no point checking for it
If Not Me.NewRecord Then
CallConf
EndIf
Public Function Conf()
Dim Msg, Style, Title, Response, MyString, frm As Form

Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Beep
Msg = "You have altered the" & " " & strControlName & " " & "field from" & " " & "[" & Screen.ActiveControl.OldValue & "]" & " " & "To" & " " & "[" & Screen.ActiveControl & "]" & " " & "are you sure you wish to do this ?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm record change"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Screen.ActiveForm.Undo
Exit Function

End If


End Function

 

[David R]

No go

I still get Run-time error 2115 (Essentially, that BeforeUpdate is stopping it from saving the record), because BeforeUpdate hasn't completed. Well duh! I had to stop the update once, to keep it from going to another record. But I have to save it now so that the table will be correct.

From the help files, the order of events here is:

1. Current (subform)
2. BeforeUpdate (subform) 'Cancel = True
3. AfterUpdate (subform) 'never happens because of Cancel = True
4. Deactivate (Main_Form)
5. LostFocus (subform_Control)
6. Open (New_Form)
7. Load (New Form)
8. Resize (New Form)
9. Activate (New Form)
   [/list=1]
   
   Between #2 and #9 I need to save the subform record. I suppose an alternative would be to pass the MeetingID (PK of the subformGroupMeetings) over to the new form, and let it save and move from the record anyway...

 

[David R]

Rich, the reason I have to check for 't' is that only certain fields being dirty will trigger a reschedule. A change in meeting time, for example, or location, doesn't change the calculated date of the meeting.

I guess I'm not being very clear. I should probably start over in a new topic, but I'll try to summarize once more:

The subform stores meeting data. When certain fields are dirtied, the 't' variable is set as a marker.
If 't' is set True when you save the record (BeforeUpdate), it asks if you want to recalculate the Meeting Dates via a popup form.
The popup form pulls its data from the table, except that it has two possibilities:
a) if there is a meeting in the Subform, it grabs that MeetingID and calculates for just that meeting.
b) If the subform isn't even open, it prompts you to cycle through the entire dataset (~500 records).
Before you can use that popup form, however, the subform moves to another record (or closes, or however you saved it), so the MeetingID is no longer valid. If I cancel the BeforeUpdate action though, I can't get it to save the correct data to the table so that the popup can read it into memory.

I suppose OpenArgs is going to be the best solution here. It's very frustrating having to work in counter-intuitive ways though, because I know I'm going to get complaints "It's trying to calculate a meeting that isn't on the screen any longer!", "It's going to use the wrong meeting!", etc.

*le sigh*
Pepe le David

 

[Rich]

Try Undoing the record, pass the Data to the 2nd form using the Open Args, make the 2nd form PopUp and modal, Re-query the first on the Unload of the second
Posted at the same time
Have you tried using the Cancel=True to prevent the form moving to new record

 

[David R]

OpenArgs works, after a fashion

It works because you can pass the MeetingID directly to the new form. It will not work if for some reason they leave the popup form open and come back to it later, but I can manage that later I suppose.

Cancel = True stops the subform from moving away. Great, except that it also stops the underlying table from being updated. I suppose I could use a recordset maneuver to force the table to be sync'ed, but that seems like using a sledgehammer to put a picture on the wall.

 

[Rich]

If you set the Modal property to yes, then they can't go anywhere else, well not in the db anyway