Windows 8 
Access 2007
First let me start by saying I apologize for the length of this email I have tried to take screenshots but am unable to send them through the forum. Therefore, I'm going to break this down on the flow both by design and need. This database fulfills several functions of my agency one being to track subpoenas issued to officers for court and another to track property disposal dispositions for evidence that was previously seized but no longer required.
The table is named subpoenatable and without going into every field I have it structured as follows. The eventnumber is an auto number field followed by the officersname, TPDCase1, TPDCase2, TPDCase3, TPDcase4, TPDcase5, firstnotification, duedate, secondnotification, dispositioncompleted. I need the eventnumber auto number so I can create a dropdown lookup to find previous entries.
This table then feeds two separate forms. The "subpoenaentryform" and "propertyroompropertydisposaltrack" form. When our officers receive subpoenas the information is entered into the subpoena entry form and notification is emailed to the officer when to attend court. The second form is used to track requests made to the officers asking they dispose of property that is no longer required as evidence.
There are records created in the property disposal form that have nothing to do with subpoenas as not every piece of evidence seized is tied to a subpoena, sometimes officers take items but charges are not filed, the case is dismissed, or an item is lost and found and we need the disposition from an officer to dispose of it and therefore a subpoena was never entered.
The decision was made to use one table to feed both forms as dispositions are needed for both court cases and non-court case incidents and we want to make request to the officers for a disposal within five days after their court date.
The fields on the property disposal track form of importance are officer's name which is combo 172, a lookup field from our employees table and the five TPD case number fields. My hope is to create code should an officer's name be selected and a case number entered in any one of the five TPD case number fields that match a previous record in the subpoena table we are notified this is a duplicate entry and then cancels the event undoing any entered data leaving the form blank ready for the next entry.
What we found because the system allowed duplicates the officers would turn in the completed forms and we would mark them as such however that indicator of complete was only on the one record leaving three or four others as incomplete therefore displayed in a report showing the pending requests as incomplete.
Historically I have used the code below which I stole from the Internet to notify us of duplicates on an unrelated form and table when a duplicate case number is entered. However this current event I need to match on the name and any one of the five case numbers as the order the case numbers were entered into may not be the same as duplicate entries are accidentally made.
I can provide photos or screenshots if you are willing to provide the mechanism for transfer. I have created a fine duplicates query but prefer not to manually hunt these things down for the rest of my career. Again I truly appreciate your help and hope this problem can be solved without having to restructure everything.
Truly,
Mark Grayson
Code not related to this event but used to find duplicate case numbers when they are entered into another part of our system
Private Sub CaseNumber_BeforeUpdate(Cancel As Integer)
Dim answer As Variant
answer = DLookup("[casenumber]", "recordsprocessing", "[casenumber] = '" & Me.Casenumber & "'")
If Not isnull(answer) Then
MsgBox "Duplicate Case Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True
Me.Casenumber.Undo
DoCmd.Close
DoCmd.OpenForm "recordsprocessingform", acNormal, , , acFormAdd
End If
End Sub
Reply With Quote
Originally Posted by pbaldy
thank you so much for the help
