用户窗体删除指定记录后重复最后一条记录的技术求助

Hey there! Let’s dig into that annoying duplicate record issue you’re hitting when deleting entries from your User Form. I’ve dealt with this exact problem a bunch of times, and it almost always boils down to not properly syncing your ComboBox (c_01) with the actual data source, or missing a step when refreshing your table after deletion.

First, let’s start by addressing what’s likely going wrong. From your description, it sounds like you’re only removing the selected item from the c_01 ComboBox but not actually deleting the record from where it’s stored (like a worksheet, Access table, etc.). Then when you refresh your table, the old record is still there, and it gets appended to the end—creating that duplicate. Or maybe you are deleting the source record, but you’re not clearing the ComboBox/table before reloading data, so leftover items stick around.

Here’s a step-by-step fix tailored to your setup:

First, we need to make sure we delete the record from its underlying storage using the unique ID from c_01. Let’s assume your data is stored in a worksheet named UserData with the unique IDs in column A (adjust this to match your actual setup):

Private Sub B_02_Click() ' Delete
    Dim deleteID As String
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Make sure a record is selected
    If c_01.ListIndex = -1 Then
        MsgBox "Please pick a record to delete first!", vbExclamation
        Exit Sub
    End If
    
    ' Grab the unique ID from the selected ComboBox item
    deleteID = c_01.Value
    
    ' Connect to your data worksheet
    Set ws = ThisWorkbook.Worksheets("UserData")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Delete the matching record from the worksheet (loop backwards to avoid skipping rows)
    For i = lastRow To 2 Step -1 ' Skip row 1 if it's a header
        If ws.Cells(i, "A").Value = deleteID Then
            ws.Rows(i).Delete
            Exit For ' Exit once we find and delete the matching record
        End If
    Next i
    
    ' Refresh the ComboBox and table to show the updated data
    RefreshUserDataDisplay
End Sub

Next, we’ll make a dedicated sub to clear out old data and reload fresh, updated records. This ensures no leftover items cause duplicates:

Private Sub RefreshUserDataDisplay()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    Set ws = ThisWorkbook.Worksheets("UserData")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Clear existing items in c_01 first
    c_01.Clear
    
    ' Reload the ComboBox with fresh unique IDs
    If lastRow >= 2 Then ' Only load if there are records beyond the header
        c_01.List = ws.Range("A2:A" & lastRow).Value
    End If
    
    ' Refresh your table control (adjust this to match your table/ListBox name)
    ' Example: If your table is a ListBox named lstUserData
    lstUserData.Clear
    If lastRow >= 2 Then
        ' Replace "A2:D" with your actual data columns
        lstUserData.List = ws.Range("A2:D" & lastRow).Value
    End If
End Sub

• Delete from the source first: We’re not just removing an item from the ComboBox—we’re erasing the actual record from where it’s stored, so it can’t reappear.
• Clear before reloading: Using c_01.Clear and lstUserData.Clear ensures we start with a blank slate, so no old items mix with fresh data.
• Loop backwards when deleting rows: When deleting worksheet rows, looping from bottom to top prevents rows from shifting up and causing us to skip records accidentally.

• Double-check that your c_01 values are truly unique—if there are duplicate IDs, the delete logic might target the wrong row.
• If you’re using a bound ComboBox (with .RowSource set), avoid mixing bound and manual operations—stick to either updating the RowSource or using the clear/reload method above.
• Make sure you’re calling RefreshUserDataDisplay every time you add, update, or delete a record to keep everything in sync.

内容的提问来源于stack exchange，提问作者isic5