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

[Lili1964]

[FONT=&quot]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...


[/FONT]

[FONT=&quot]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[/FONT]

 

[missinglinq]

What kind of Form is being used for the Subform?

Are we talking about a custom Command Button (one you've created) or the native-Access delete button?

If a custom Command Button where, exactly, is it located?

If a custom Command Button, please post just that code, not the entire code module.

Linq ;0)>

 

[Pat Hartman]

When you are OUTSIDE of a form/subform, the ONLY record you can directly reference is the first one. There is no way to reference the third record of a subform's recordSource.

To avoid this issue, I put the delete button on the subform record. That way when I press the button, the record I want to delete is the Current record and so that is the record that gets deleted.

To delete a record in a subform from a button on the main form, takes two clicks. One to click into the record you want to delete and a second to click on the delete button. In addition, to make certain you are deleting the correct record, the current event of the subform should post it's primary key ID to a hidden field on the main form. Then your delete code can use that stored ID to delete the correct record. I have to tell you that I dislike this immensely since it is more likely to result in user errors since the record they are deleting may not even be visible if they have scrolled away. And if they forget to click into the record to force the Current event to fire, there won't be a value in the hidden field. Also, in the Main form's Current event, you would have to clear the hidden field. If you don't, the user could be deleting a subform record that belonged to a totally different main form record.

Best solution - but the delete button on the subform record itself, or simply rely on the Right-click menu or use the Record Selector (if you show it) to delete the record. If I allow deletes in my subforms, I almost always show the recordSelector and instruct users on how to use it to delete records. In rare cases, I need to add a specific delete button or in the case of a subform in datasheet view, I would add an unbound column I could use in place of the delete button.

 

[missinglinq]

I've grown fond of using the Double-Click event of a Textbox to trigger deletes on Datasheet Subforms, backing it, of course, with a Messagebox to ensure that the user does, in fact, want to delete the Record. Has worked quite well with a number of diverse user groups.

Of course, we're both making bricks with no stray, more or less, as the OP hasn't responded (not at work,maybe?) and don't know about you, Pat, but I don't read/understand Dutch!

Linq ;0)>

 

[Pat Hartman]

I usually use dbl-click to drill down to a more detailled form. But, I have used it also for delete. I just added an unbound control that said "Del" in red and used that to trigger the delete.

I actually did understand some of the terms in the code. I think English and German and probably Dutch are all in the same language family so there are similar roots. I studied both French and Spanish in high school and can still read some. I was in Montreal for a week at a bridge tournament a few years ago and by the end of the week, I sort of understood a little of the French people were speaking to me a the table (bridge is a limited language). My problem was, I answered in Spanish

 

[missinglinq]

Truth be told, I seldom actually allow any records to be deleted; just mark them as inactive and hide them!

Linq ;0)>

 

[Mihail]

Truth be told, I seldom actually allow any records to be deleted; just mark them as inactive and hide them!

So, every table has an extra field used for mark ?
And every query show only unmarked records ?
I understand well or you have another approach ?
Thank you.

 

[missinglinq]

Exactly! Much of my work has been in the medical field and archiving records is very important. I also usually have a form that only shows the 'marked,' or inactive records, as they frequently need to look at something from the past.

Linq ;0)>

 

[Lili1964]

I have attached a screenshot of the form.

The delete button we programmed ourselves.
It should delete the current record. In this example the empty record.

However it deletes the first record.

Does anyone know a solution?

 

[Pat Hartman]

Try reading my post # 3 again. It tells you the solution.

 

[Lili1964]

This is not the solution.
A delete button is already placed in the subform.

What is the VBA code for deleting a specific record when I know the ID of the record?
Instead of Me.Recordset.Delete?

Like this?

DoCmd.RunCommand acCmdDeleteRecord

or

Dim strSQL As String

strSQL = "DELETE FROM [NameOfTable] " & _
"WHERE [NameOfField] = " & ID
CurrentDb.Execute strSQL, dbFailOnError

 

[Pat Hartman]

DoCmd.RunCommand acCmdDeleteRecord

is correct when the button is on the form that is showing the record you want to delete --- BUT ---
Based on the picture, the button is NOT on the subform. It is on the main form. You cannot use it to delete a specific subform record as I described in post #3. When you are deleting something other than the current record of the form that is running the code, you need to run a delete query. But as I said in my earlier post, once you move focus away from the subform to press the delete button on the main form you have LOST the id of the record the user last touched in the subform. From outside the subform, the ONLY record you can reference is the FIRST record and that is your problem.