David R
I know a few things...
- Local time
- Today, 02:31
- 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?
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: