Filtering Records in Form changing data in Table

[sid78669]

I have two tables, Episodes and Seizures, with their respective Forms. On Episodes, I have a button the user clicks to open the seizure Form and its internal working is below. The problem is when the form is opened and filtered by parent form, the seizure form instead of filtering data, overwrites the first available record's incident number with the one that is passed. When I have the seizure table set to make sure Incident num is unique, it throws duplicate data error, and if it allows duplicate (Which is what i need), it creates new records. Suggestions?

Private Sub btnSeizure_Click()
    If SeizureFormIsOpen() Then
        CloseSeizureForm
    Else
        OpenSeizureForm
        FilterSeizureForm
    End If
End Sub

Private Sub FilterSeizureForm()
    If Me.NewRecord Then
        Forms![Seizures].DataEntry = True
    Else 'Problem Code
        Forms![Seizures].Filter = "[IncidentNum] = '" & Me.[incidentNum] & "' And [SourceTable] = 'Episode'"
        Forms![Seizures].FilterOn = True
    End If
End Sub

Private Sub OpenSeizureForm()

    DoCmd.OpenForm "Seizures", acNormal, , , , acWindowNormal, Me.incidentNum & "&Episode"

End Sub

Private Sub CloseSeizureForm()

    DoCmd.Close acForm, "Seizures"

End Sub

Private Function SeizureFormIsOpen()

    SeizureFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "Seizures") And acObjStateOpen) <> False

End Function

The Seizure Form Has this for on load:

Private Sub Form_Load()
    Dim argSplit() As String
    argSplit = Split(CStr(Me.OpenArgs), "&")
    
    'These are just Text boxes
    Me.txtIncidentNum.Value = CStr(argSplit(0))
    Me.sourceTable.Value = CStr(argSplit(1))
End Sub

[ChrisPadgham]

I am not sure why you are doing the filtering in a second step

Private Sub OpenSeizureForm() 
   dim stFilter as String
   If Me.NewRecord Then  
      'Open form in dataentry mode
      DoCmd.OpenForm "Seizures", acNormal, , , acFormAdd, acWindowNormal, Me.incidentNum & "&Episode"

 Else 
      ' open and filter form
      stFilter = "[IncidentNum] = '" & Me.[incidentNum] & "' And [SourceTable] = 'Episode'"
      DoCmd.OpenForm "Seizures", acNormal, ,  stFilter, , acWindowNormal, Me.incidentNum & "&Episode"

End If
End Sub

Private Sub Form_Load
    Dim argSplit() As String
    argSplit = Split(CStr(Me.OpenArgs), "&")
    
    'Set the DefaultValue which means new records will have these values 
    Me.txtIncidentNum.DefaultValue = CStr(argSplit(0))
    Me.sourceTable.DefaultValue = CStr(argSplit(1))
End Sub

[sid78669]

Thanks Chris! It did it. I had copied the code from Microsoft's site as this is the first time I've worked with VB and Access.