Delete Button

[yrstruly]

Im trying to implement a delete button. Current button for delete is only deleting the records displaying on the form. I need to be able to press delete and insert the row id to delete that row.


Sample Data in Table:

SampleID	ClientID	SampleType	Cultivar	Block	Virus	SampleReceivedBy	Retest	Comments1	{1549E7C7-EF2C-4966-A291-B05DDBBC2474}						0	2	{9E587E7C-3C6B-11D0-B8BF-444553540000}						0	3	{939E41F1-5C2F-11D2-BE66-0000E8D5A293}						0	4	{77A26E81-842E-4A98-9CC8-F3AD9EA1FE72}	Root	Almond		PDV		0	5	{1549E7C7-EF2C-4966-A291-B05DDBBC2474}	Leaf	AR0512	B	PDV		0	test6	{787572EF-4BCC-4480-9D8F-28CE4475F034}	Bud	GR0286	250A	PDV	Admin	0	nh7	{AFF4CE04-E0B8-46B1-97CF-919C041BFC69}	Fruit	GR0482	41	ApMV	Michelle R	0	b8	{939E41F1-5C2F-11D2-BE66-0000E8D5A293}	Fruit	GR0669	B	ApMV		0

Private Sub cmdDelete_Click()    Dim strSQL As String
    
    ' Check if SampleID is entered
    If IsNull(Me.SampleID) Or Me.SampleID = "" Then
        MsgBox "No SampleID provided. Please specify a record to delete.", vbExclamation
        Exit Sub
    End If
    
    ' Single-line MsgBox for confirmation:
    If MsgBox("Are you sure you want to delete record with SampleID = " & Me.SampleID & "?", _
              vbYesNo + vbQuestion, "Confirm Delete") = vbYes Then
              
        ' If SampleID is numeric, do NOT use quotes:
        strSQL = "DELETE FROM tblSamples_ELISA_KERNVRUGTE WHERE SampleID = " & Me.SampleID
        
        ' If SampleID is text, you MUST use quotes:
        ' strSQL = "DELETE FROM tblSamples_ELISA_KERNVRUGTE WHERE SampleID = '" & Me.SampleID & "'"
        
        CurrentDb.Execute strSQL, dbFailOnError
        
        MsgBox "Record deleted successfully.", vbInformation
        
        ' Clear the SampleID and requery
        Me.SampleID = Null
        Me.Requery
    End If
End Sub

This code does not even open anything:

Private Sub cmdDelete_Click()    Dim strSQL As String
    Dim lngRecordsAffected As Long
    
    If IsNull(Me.txtDeleteID) Or Me.txtDeleteID = "" Then
        MsgBox "No SampleID provided. Please specify a record to delete.", vbExclamation
        Exit Sub
    End If
    
    If MsgBox("Are you sure you want to delete record with SampleID = " & Me.txtDeleteID & "?", vbYesNo + vbQuestion, "Confirm Delete") = vbYes Then
        
        ' If SampleID is numeric:
        strSQL = "DELETE FROM tblSamples_ELISA_KERNVRUGTE WHERE SampleID = " & Me.txtDeleteID
        
        ' If SampleID is text, comment the above and use:
        ' strSQL = "DELETE FROM tblSamples_ELISA_KERNVRUGTE WHERE SampleID = '" & Me.txtDeleteID & "'"
        
        CurrentDb.Execute strSQL, dbFailOnError
        lngRecordsAffected = CurrentDb.RecordsAffected
        
        If lngRecordsAffected > 0 Then
            MsgBox "Record deleted successfully.", vbInformation
        Else
            MsgBox "No record found with SampleID = " & Me.txtDeleteID, vbExclamation
        End If
        
        Me.txtDeleteID = Null
        Me.Requery
    End If
End Sub

[Welshgasman]

Have you looked up Delete sql syntax?
Have you tried that sql in the immediate window?
Have you bothered to walk your code, line by line?

[ranman256]

in form design, select button object, put button on form
select record operations
select delete record
done.

[Bulzie]

Looks like you want them to enter the ID and then have it delete it on the form? In your delete line, your variable starts with "txt" txtDeleteID, so is it text or numeric ID?

strSQL = "DELETE FROM tblSamples_ELISA_KERNVRUGTE WHERE SampleID = " & Me.txtDeleteID

Might need for text:
strSQL = "DELETE FROM tblSamples_ELISA_KERNVRUGTE WHERE SampleID = """ & Me.txtDeleteID & """" (3 " before, 4 after)

[Welshgasman]

I would use a txt pefix to indicate a textbox control. Having an ID in the name would be the autonumber field.