Adding a Delete Record button to my UserForm?

[jmpatrick]

Here's my UserForm:

Here's the fabulous code that (finally) makes it work and quite possibly saved my job:

Private Sub cmdSearchSubCode_Click()
Dim SubCode_id As String
SubCode = Trim(SubCode.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 3).Value = SubCode Then
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub

Here's the code I have to delete the entire row of the currently displayed record from the DataCenter sheet:

Private Sub cmdDeleteSub_Click()
Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbyeYesNo + vbQuestion, "Delete Record?")
If Answer = vbYes Then
   Cells(currentrow, 1).EntireRow.Delete
End If
End Sub

It doesn't work. I'm thinking the problem is that despite the information displaying on the UserForm, it isn't selected on the database sheet. Am I close?

 

[jmpatrick]

you will need to publish all code behind your form to help understand what issue may be or better, place copy of workbook with dummy data in file sharing site like dropbox & provide a link to it

Dave

Here's the code from the relevant form:

Dim i As Long
Dim wsDataCenter As Worksheet
Private Sub UserForm_Initialize()
    SubName.List = [DataCenter!SubName].Value
    FieldManager.List = [FieldManagers!FMNames].Value
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    Me.cmdDeleteSub.Enabled = False
End Sub
Private Sub cmdCloseSubMaintenance_Click()
Unload frmSubMaintenance
End Sub
Private Sub cmdSearchSubName_Click()
Dim SubName_id As String
SubName = Trim(SubName.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 5).Value = SubName Then
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub
Private Sub cmdSearchSubCode_Click()
    Dim lastrow     As Long
 
    SubCode = Trim(SubCode.Text)
    If Len(SubCode.Text) = 0 Then Exit Sub
 
    With wsDataCenter
        lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
        For i = 1 To lastrow
            If .Cells(i, 3).Value = SubCode Then
                SubCode.Text = .Cells(i, 2).Value
                SubName.Text = .Cells(i, 5).Value
                SubInitials.Text = .Cells(i, 4).Value
                FieldManager.Text = .Cells(i, 7).Value
    
             Me.cmdDeleteSub.Enabled = True
            Exit For
        End If
    Next
 End With
End Sub
Private Sub cmdSearchSubInitials_Click()
Dim SubCode_id As String
SubInitials = Trim(SubInitials.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 4).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 4).Value = SubInitials Then
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
End If
Next
End Sub
Private Sub cmdUpdateSub_Click()
Dim SubName_id As String
SubName = Trim(SubName.Text)
lastrow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To lastrow
If Worksheets("DataCenter").Cells(i, 5).Value = SubName Then
   Worksheets("DataCenter").Cells(i, 2).Value = SubCode.Text
   Worksheets("DataCenter").Cells(i, 4).Value = SubInitials.Text
   Worksheets("DataCenter").Cells(i, 7).Value = FieldManager.Text
End If
Next
End Sub
Private Sub cmdDeleteSub_Click()

    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
    
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete

With Me
        .SubName.Clear
        .SubCode.Value = ""
        .SubInitials.Value = ""
        .FieldManager.Clear
End With
MsgBox ("Subdivision Successfully Deleted!")
Call UserForm_Initialize
End Sub
Private Sub SubInitials_Change()
    SubInitials.Text = UCase(SubInitials.Text)
End Sub
Private Sub SubCode_Change()
    SubCode.Text = UCase(SubCode.Text)
End Sub
Private Sub SubInitials_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubInitials.Value) = "*" Then
                Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubInitials_Click
        End If
        SubInitials.SetFocus
End Sub
Private Sub SubName_Change()
     Call cmdSearchSubName_Click
End Sub
Private Sub SubCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubCode.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubCode_Click
             SubCode.SetFocus
        End If
End Sub

 

[dmt32]

I should have guessed that you are using variable i in other controls that are doing the same thing, so when they get populated from the search, those controls change events likely to conflict with the variables value.

I do not have time to create your form & controls but if can place a copy on a file sharing site, will have a look at it for you.

Dave

 

[jmpatrick]

I should have guessed that you are using variable i in other controls that are doing the same thing, so when they get populated from the search, those controls change events likely to conflict with the variables value.

I do not have time to create your form & controls but if can place a copy on a file sharing site, will have a look at it for you.

Dave

Interesting discovery! With a blank form, if I manually add a SubCode and hit enter I only get the SubName. However, if I hit enter a second time it populates the rest of the data.

 

[jmpatrick]

Ha! I duplicated a line of code to simulate 2 enter key hits and it works:

Private Sub SubCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubCode.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubCode_Click
             cmdSearchSubCode_Click
             SubCode.SetFocus
        End If
End Sub

It's probably a wonky way to solve the problem.

 

[dmt32]

Ha! I duplicated a line of code to simulate 2 enter key hits and it works:

It's probably a wonky way to solve the problem.

Looks like you have a conflict between events but if have a resolution all good

Dave