Form Control still 'saving' after Undo/Cancel (1 Viewer)

David R

I know a few things...
Local time
Today, 04:12
Joined
Oct 23, 2001
Messages
2,633
This frame controls an option group. If they are in an existing citation and want to start one for another type, this offers to write a new record in when they try to switch. Problem is, if it DOES write the new record, it seems to be saving the old record, and thus both will end up 56, for example. Access seems to be ignoring/overwriting the Me.frameCitationType.Undo (I don't want to use Me.Undo because they may have made other changes as well).

If the user does not spawn the new citation, it undoes fine. I suspect the problem may be the Filter/FilterOn?

Code:
Private Sub frameCitationType_BeforeUpdate(Cancel As Integer)
Dim chap As Integer
Dim othercit As Variant
Dim curRecord  As DAO.Recordset
Dim curDefend  As DAO.Recordset

    If Me.NewRecord = True And IsNull(Me.CaseNumber) Then
        MsgBox "Enter the Service Order and Increment first, please.", vbExclamation
        Me.frameCitationType.Undo
        Cancel = True
        Me.CaseNumber.SetFocus
        Exit Sub
    End If

    If DCount("*", "queryCitationViolations", "[CitationID] = " & Me.CitationID & " AND [ChapterID] <> " & Me.frameCitationType.Value) > 0 Then
        'first undo the edit so we don't jack everything up later
        chap = Me.frameCitationType.Value
        Me.frameCitationType.Undo
        Cancel = True

        othercit = DLookup("CaseNumber", "queryCountUnmailed", "[CaseNumber] = '" & Me.CaseNumber & "' AND [ChapterID] = " & chap)

        If Not IsNull(othercit) Then 'existing citation in the new category
            If MsgBox("There is an existing Chapter " & Me.frameCitationType.Value & " citation draft. Jump to it?", vbQuestion + vbYesNo, _
                      "This citation is for Chapter " & Me.frameCitationType.OldValue) = vbYes Then 'jump to it
                Me.Filter = "[CaseNumber] = '" & Me.CaseNumber & "' AND [ChapterID] = " & chap
                Me.FilterOn = True
            End If
        Else 'no other citation, let's offer to create it
            If MsgBox("Would like to create a new Chapter " & chap & " citation for this Service Order now?", vbQuestion + vbYesNo, _
                      "This citation is for Chapter " & Me.frameCitationType.OldValue) = vbYes Then 'jump to it
                Set curRecord = CurrentDb.OpenRecordset("tableCitations", dbOpenDynaset)
                Set curDefend = CurrentDb.OpenRecordset("SELECT tableDefendants.* FROM tableDefendants WHERE [CitationID] = " & Me.CitationID, dbOpenDynaset)
                Me.Parent.SetFocus
                'doCmd.RunCommand acCmdRecordsGoToNew
                
                With curRecord
                    .AddNew
                    !CaseNumber = Me.CaseNumber
                    !CitationType = chap
                    !CitationDate = Int(Now())
                    !FineAmount = 100 'default
                    !HearingDate = Me.HearingDate
                    !DelinquentDate = Me.DelinquentDate
                    !IncidentAddress = Me.IncidentAddress
                    !IncidentKIVAPIN = Me.IncidentKIVAPIN
                    !IncidentZIP = Me.IncidentZIP
                    !LegalDescription = Me.LegalDescription
                    !AdminID = Me.AdminID
                    !InspectorID = Me.InspectorID
                    !SupervisorID = Me.SupervisorID
                    othercit = !CitationID 'store the new one to jump to
                    .Update
                End With
                
                If curDefend.RecordCount > 0 Then
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL "INSERT INTO tableDefendants ( CitationID, PersonName, PersonAddress, PersonCity, PersonState, PersonZIP, PersonCountry, PersonRelationship ) " & _
                                 "SELECT " & othercit & ", PersonName, PersonAddress, PersonCity, PersonState, PersonZIP, PersonCountry, PersonRelationship " & _
                                 "FROM tableDefendants WHERE [CitationID] = " & Me.CitationID
                    DoCmd.SetWarnings True
                End If
                
                If MsgBox("Do you want to go there now?", vbQuestion + vbYesNo, "New citation for Chapter " & chap & " drafted") = vbYes Then
                    Me.Filter = "[CitationID] = " & othercit
                    Me.FilterOn = True
                End If
                
                curRecord.Close
                Set curRecord = Nothing
                curDefend.Close
                Set curDefend = Nothing
            End If
        End If
    Else
        Me.subformViolations.SourceObject = "formViolations" & Me.frameCitationType.Value
        Me.subformViolations.Form.FilterOn = True
    End If
End Sub
 
Last edited:

spikepl

Eledittingent Beliped
Local time
Today, 10:12
Joined
Nov 3, 2010
Messages
6,144
BeforeUpdate of a control determines only whether or not the new value submitted for the control will be stored in the record - and not whether the entire record actually gets stored. Unless you force a save or make it go to another record, in which case Access automatically will attempt to save the entire record.

To prevent saving of a record you need to cancel the Form's BeforeUpdate.
 

David R

I know a few things...
Local time
Today, 04:12
Joined
Oct 23, 2001
Messages
2,633
Aren't I reversing the value change, though?
Code:
        Me.frameCitationType.Undo
        Cancel = True

It's impossible to 'change' the value of the frame from within the BeforeUpdate event. My other option would be to fire it from AfterUpdate instead, but that sounds like a recipe for getting stuck in a loop...
 
Last edited:

David R

I know a few things...
Local time
Today, 04:12
Joined
Oct 23, 2001
Messages
2,633
Bumping. This is still/again a problem, and I can see no reason why.
Code:
If Me.NewRecord = True And IsNull(Me.CaseNumber) Then
        MsgBox "Enter the Service Order and Increment first, please.", vbExclamation
        Me.frameCitationType.Undo
        Cancel = True
        Me.CaseNumber.SetFocus
        Exit Sub
    End If
This is literally the first click on the form. There is NO reason it should be unable to undo itself (and no, I can't use Me.Undo because that would remove the user's valid edits, if they had made any).
 
Last edited:

Users who are viewing this thread

Top Bottom