Delete of a record from a list of records in a subform deletes the wrong record

[livr]

Hello,



We have an Access programming problem.
When we browse through records in a subform we store the records in the database.
When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.

I have included the code below.

Is there an access/vba expert who can help me?
We are desperate at the moment...

Option Compare Database
Dim FocusBln As Boolean

Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
    If Screen.ActiveForm.Name = "Voortgangsrapportage" Then
        Me.[Datum Aangemaakt].Value = Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.[Datum]
    ElseIf Screen.ActiveForm.Name = "Intake2" Then
        If Forms![Intake2].[Datum Intake] = "" Then
            Me.[Datum Aangemaakt].Value = Now()
        Else
            Me.[Datum Aangemaakt].Value = Forms![Intake2].[Datum Intake]
        End If
    ElseIf Screen.ActiveForm.Name = "Eindresultaat Onderhoud Zoeken" Then
        Me.[Datum Aangemaakt].Value = Forms![Eindresultaat Onderhoud Zoeken].Form.[Eindresultaat Onderhoud Zoeken Subformulier].Form.[Eindresultaat Onderhoud].Form.[Datum afsluiting]
    ElseIf Screen.ActiveForm.Name = "Beginsituatie" Then
        Me.[Datum Aangemaakt].Value = Forms![Beginsituatie].Form.[Subformulier Datum].Form.[Datum Intake]
    End If
End If
Me.VoortgangText.Visible = True
Me.VoortgangText.SetFocus
If Me.VoortgangText.Text = "" Then
    If Screen.ActiveForm.Name = "Voortgangsrapportage" Then
        Me.VoortgangText.Value = Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.[VoortgangID]
    ElseIf Screen.ActiveForm.Name = "Aanvraag Kiezen" Or Screen.ActiveForm.Name = "Aanvraag Opzoeken" Or Screen.ActiveForm.Name = "Intake2" Then
        Me.VoortgangText.Value = 0
    Else
        Me.VoortgangText.Value = 0
    End If
End If
Me.Subformulier_Hulpvraag_Tabel.Requery
Me.Hulpvraag.SetFocus
Me.[Datum Aangemaakt].Visible = False
Me.VoortgangText.Visible = False
End Sub

Private Sub Form_Current()
'FocusBln = False
'Call Identificeer
'FocusBln = True
End Sub


Private Sub Form_Load()
FocusBln = True
End Sub

Private Sub Hulpvraag_GotFocus()
If FocusBln = True Then
    FocusBln = False
    Call Identificeer
    FocusBln = True
End If
End Sub

Private Sub Keuzelijst_met_invoervak2_KeyPress(KeyAscii As Integer)
'KeyAscii voor hoofdletters zijn 65 t/m 90, en 97 t/m 122 voor kleine letters.
'Nummers = 48 t/m 57
'Tab = 9, Backspace = 8, Enter = 13
If KeyAscii <> 9 And KeyAscii <> 13 Then
Beep
KeyAscii = 0
End If
End Sub

Private Sub Knop27_Click()
On Error GoTo Fout



'If IsNull([Hulpvraag]) Or IsNull([Categorie]) Then
    'MsgBox ("Hulpvraag en categorie zijn verplicht.")
    'Exit Sub
'End If

If Screen.ActiveForm.Name = "Voortgangsrapportage" Then
    If Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.Datum.Text = "" Then
        Beep
    Else
        DoCmd.GoToRecord , , acPrevious
        Me.Refresh
        Me.Subformulier_Hulpvraag_Tabel.Form.Refresh
    End If
Else
    Me.Hulpvraag.SetFocus
    If Me.Hulpvraag.Text = "" And Len(Me.Categorie & vbNullString) = 0 Then
        DoCmd.RunCommand acCmdSaveRecord
        'Me.Recordset.Delete
        Me.Recordset.MovePrevious
        Me.Refresh
    Else
        DoCmd.GoToRecord , , acPrevious
        Me.Refresh
        Me.Subformulier_Hulpvraag_Tabel.Form.Refresh
    End If
End If
Exit Sub

Fout:
Beep
End Sub

Private Sub Knop28_Click()
On Error GoTo Fout
If IsNull([Hulpvraag]) Or IsNull([Categorie]) Then
    MsgBox ("Hulpvraag en categorie zijn verplicht.")
    Exit Sub
End If
If Screen.ActiveForm.Name = "Voortgangsrapportage" Then
    If Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.Datum.Text = "" Then
        Beep
    Else
        Me.Subformulier_Hulpvraag_Tabel.Form.Refresh
        Me.Refresh
        DoCmd.GoToRecord , , acNext
    End If
Else
    Me.Subformulier_Hulpvraag_Tabel.Form.Refresh
    Me.Refresh
    DoCmd.GoToRecord , , acNext
    Call Identificeer
End If
Exit Sub

Fout:
Beep
End Sub

Private Sub NieuwKnop_Click()
On Error GoTo Fout

If IsNull([Hulpvraag]) Or IsNull([Categorie]) Then
    MsgBox ("Hulpvraag en categorie zijn verplicht.")
    Exit Sub
End If

If Screen.ActiveForm.Name = "Voortgangsrapportage" Then
    If Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.Datum.Text = "" Then
        Beep
    Else
        DoCmd.GoToRecord , , acNewRec
        Me.Refresh
    End If
Else
    DoCmd.GoToRecord , , acNewRec
    Call Identificeer
    Me.Refresh
End If
Exit Sub

Fout:
Beep
End Sub


Private Sub OpgelostCheck_Click()
Me.[Datum Opgelost].Visible = True
If Me.OpgelostCheck.Value = True Then
    If Screen.ActiveForm.Name = "Voortgangsrapportage" Then
        Me.[Datum Opgelost].Value = Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.[Datum]
        [Voortgang Opgelost] = Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.[VoortgangID]
    ElseIf Screen.ActiveForm.Name = "Eindresultaat Onderhoud Zoeken" Then
        Me.[Datum Opgelost].Value = Forms![Eindresultaat Onderhoud Zoeken].Form.[Eindresultaat Onderhoud Zoeken Subformulier].Form.[Eindresultaat Onderhoud].Form.[Datum afsluiting]
    ElseIf Screen.ActiveForm.Name = "Beginsituatie" Then
        Me.[Datum Opgelost].Value = Forms![Beginsituatie].Form.[Subformulier Datum].Form.[Datum Intake]
    End If
ElseIf Me.OpgelostCheck.Value = False Then
    Me.[Datum Opgelost].SetFocus
    Me.[Datum Opgelost].Value = Null
End If
Me.OpgelostCheck.SetFocus
Me.[Datum Opgelost].Visible = False
Me.Refresh
End Sub

Private Sub VerwijderKnop_Click()
If Screen.ActiveForm.Name = "Intake2" Then
    DoCmd.RunCommand acCmdSaveRecord
    Me.Recordset.Delete
    Me.Recordset.MovePrevious
    Me.Refresh
ElseIf Screen.ActiveForm.Name = "Voortgangsrapportage" Then
    If Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.Datum.Text = "" Then
        Beep
    Else
        If [VoortgangID] = Forms![Voortgangsrapportage].Form.[Voortgangsrapportage Subformulier].Form.[VoortgangID] Then
            DoCmd.RunCommand acCmdSaveRecord
            Me.Recordset.Delete
            Me.Recordset.MovePrevious
            Me.Refresh
        Else
            MsgBox ("Alleen hulpvragen die je voor deze voortgang hebt aangemaakt mogen worden verwijderd.")
        End If
    End If
ElseIf Screen.ActiveForm.Name = "Eindresultaat Onderhoud Zoeken" Then
    If [Datum Aangemaakt] = Forms![Eindresultaat Onderhoud Zoeken].Form.[Eindresultaat Onderhoud Zoeken Subformulier].Form.[Eindresultaat Onderhoud].Form.[Datum afsluiting] Then
        DoCmd.RunCommand acCmdSaveRecord
        Me.Recordset.Delete
        Me.Recordset.MovePrevious
        Me.Refresh
    Else
        MsgBox ("Alleen hulpvragen die je voor deze eindsituatie hebt aangemaakt mogen worden verwijderd.")
    End If
ElseIf Screen.ActiveForm.Name = "Beginsituatie" Then
    If [Datum Aangemaakt] = Forms![Beginsituatie].Form.[Subformulier Datum].Form.[Datum Intake] Then
        DoCmd.RunCommand acCmdSaveRecord
        Me.Recordset.Delete
        Me.Recordset.MovePrevious
        Me.Refresh
    Else
        MsgBox ("Alleen hulpvragen die je voor deze beginsituatie hebt aangemaakt mogen worden verwijderd.")
    End If
End If
End Sub

[rpeare]

can you post a screen shot of your form and identify which record you're trying to delete by modifying the picture?

[vincent-leeway]

You can delete the record, if you do it by the value of the primary key/unique constraint. Are you storing value of id some where in your form along with every row?

[Elvli]

can you post a screen shot of your form and identify which record you're trying to delete by modifying the picture?

The screen shot below:


Do you know a solution other than the one suggested in the other reply?

[rpeare]

So to restate the problem, if you are on the record that has a blank line you want to be able to delete that record.

With your setup you can do it one of two ways.

Select the record by clicking the little gray box on the far left then just hitting your delete key.

Another way is to put a yes/no field in your table that's basically indicates whether the record should be voided or not, then when you open your form only show records that ARE NOT flagged as a void. I prefer this method because if you accidentally delete something it's gone for good, whereas if you create a void system you can always find the record again and recover it.

Third, if you are determined to actually delete the record you would probably need the delete button to be in the subform, NOT the main form because as soon as you navigate away from the subform your code is going to have no idea which record in the subform you're trying to delete and, as you've discovered, tends to delete the first record it encounters.