locked
Update records using a Userform RRS feed

  • Question

  • I have a Userform (NLIM) which enables me to add data to my worksheet (NLIM_Data). I have also added a search combobox to this userform (cboSearch1) which you can select the NLIM number (column1 of the worksheet). This then populates the other boxes of the Userform so you can read, update or delete the record.

    I have the code for Adding new records to the Worksheet and what I thought was the code for updating records, but for some reason it is not working.

    I use a "Search" combobox to select the record row which needs updating, this brings the required data into the Userform cells, but when i press the "Update" cmdbutton the following error appears.

    I get the error Rub-time error '1004':

    Application-defined or object-defined error

    When i debug it takes me to my update code

    Private Sub UpdateClose_Click()

    Cells(currentrow, 1) = txtURN.Value
    Cells(currentrow, 2) = txtIncident_Title.Value
    Cells(currentrow, 3) = txtDate.Value
    Cells(currentrow, 4) = txtUnit_Title.Value
    Cells(currentrow, 5) = cboIncident_Severity.Value
    Cells(currentrow, 6) = cboIncident_Status.Value
    Cells(currentrow, 7) = cboIncident_Type.Value
    Cells(currentrow, 8) = txtWeek_Commence.Value
    Cells(currentrow, 9) = txtUpdate_Detail.Value

    End Sub

    I have the Dim currentrow as long defined at the top of the page.

    Any ideas, 

    Thanks Steve

    Thursday, December 28, 2017 2:35 PM

Answers

  • Try declaring an extra variable

    Public ThisRow As Long

    at the top of the module.

    In cboSearch_Change, add the following line just above End If:

                ThisRow = currentrow

    In UpdateClose_Click, use ThisRow instead of currentrow.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Steve MW Friday, December 29, 2017 5:04 PM
    Friday, December 29, 2017 4:27 PM

All replies

  • Do you assign a value to currentrow somewhere before clicking the button? No value is assigned in UpdateClose_Click, so if you haven't done so before, currentrow will be 0, which is not a valid row number of course.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 28, 2017 3:11 PM
  • I havent assigned a value to it, but since you have mentioned it I have been trying to, but cant seem to get anything to work.

    how would i go about it?

    Steve

    Thursday, December 28, 2017 5:58 PM
  • How can we determine which row you want to update?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 28, 2017 7:34 PM
  • I use a combobox "Search" to select a set of data from a worksheet "NLIM_Data" which then populates a series of text and comboboxes in a Userform.

    If i change the data which has been selected from the Search combobox and press "Update" cmd button this row on the worksheet should update.

    Steve

    Thursday, December 28, 2017 8:02 PM
  • So I have now managed to assign a value to the currentrow, this I managed by looking at some code from the net and adding currentrow to other buttons.

    The code now runs ok and the data updates on the Userform, but does not update the worksheet (NLIM_Data)

    The full code is:-

    Dim currentrow As Long

    Private Sub cboSearch_Change()
    Dim lastrow
    Dim Search As String

    lastrow = Sheets("NLIM_Data").Range("A" & Rows.Count).End(xlUp).Row
    Search = cboSearch.Text
    For currentrow = 2 To lastrow

    If Cells(currentrow, 1).Text = Search Then

    txtURN.Text = Cells(currentrow, "A").Text
    txtIncident_Title.Text = Cells(currentrow, "B").Text
    txtNLIMDate.Text = Cells(currentrow, "C").Text
    txtUnit_Title.Text = Cells(currentrow, "D").Text
    cboIncident_Severity.Text = Cells(currentrow, "E").Text
    cboIncident_Status.Text = Cells(currentrow, "F").Text
    cboIncident_Type.Text = Cells(currentrow, "G").Text
    txtWeek_Commence.Text = Cells(currentrow, "H").Text
    txtUpdate_Detail.Text = Cells(currentrow, "I").Text

    End If

    Next currentrow
    cboSearch = ""
    txtURN.SetFocus


    End Sub

    Private Sub cmdAdd_Click()
    Dim lastrow As Long
    lastrow = Sheets("NLIM_Data").Range("A" & Rows.Count).End(xlUp).Row
    Cells(lastrow + 1, 1).Value = txtURN.Text
    Cells(lastrow + 1, 2).Value = txtIncident_Title.Text
    Cells(lastrow + 1, 3).Value = txtNLIMDate.Text
    Cells(lastrow + 1, 4).Value = txtUnit_Title.Text
    Cells(lastrow + 1, 5).Value = cboIncident_Severity.Text
    Cells(lastrow + 1, 6).Value = cboIncident_Status.Text
    Cells(lastrow + 1, 7).Value = cboIncident_Type.Text
    Cells(lastrow + 1, 8).Value = txtWeek_Commence.Text
    Cells(lastrow + 1, 9).Value = txtUpdate_Detail.Text

    End Sub

    Private Sub cmdClear_Click()

    txtURN.Text = ""
    txtIncident_Title.Text = ""
    txtNLIMDate.Text = ""
    txtUnit_Title.Text = ""
    cboIncident_Severity.Text = ""
    cboIncident_Status.Text = ""
    cboIncident_Type.Text = ""
    txtWeek_Commence.Text = ""
    txtUpdate_Detail.Text = ""

    End Sub

    Private Sub cmdFindNext_Click()
    Dim lastrow
    Dim URN As String

    lastrow = Sheets("NLIM_Data").Range("A" & Rows.Count).End(xlUp).Row
    URN = txtURN.Text
    For currentrow = 2 To lastrow
    If Cells(currentrow, 1).Text = URN Then

    txtURN.Text = Cells(currentrow, 1).Text
    txtIncident_Title.Text = Cells(currentrow, 2)
    txtNLIMDate.Text = Cells(currentrow, 3)
    txtUnit_Title.Text = Cells(currentrow, 4)
    cboIncident_Severity.Text = Cells(currentrow, 5)
    cboIncident_Status.Text = Cells(currentrow, 6)
    cboIncident_Type.Text = Cells(currentrow, 7)
    txtWeek_Commence.Text = Cells(currentrow, 8)
    txtUpdate_Detail.Text = Cells(currentrow, 9)

    End If
    Next currentrow
    txtURN.SetFocus
    End Sub

    Private Sub cmdFindPrevious_Click()
    Dim lastrow
    Dim URN As String

    lastrow = Sheets("NLIM_Data").Range("A" & Rows.Count).End(xlUp).Row
    URN = txtURN.Text

    For currentrow = lastrow To 2 Step -1
    If Cells(currentrow, 1).Text = URN Then

    txtURN.Text = Cells(currentrow, 1).Text
    txtIncident_Title.Text = Cells(currentrow, 2)
    txtNLIMDate.Text = Cells(currentrow, 3)
    txtUnit_Title.Text = Cells(currentrow, 4)
    cboIncident_Severity.Text = Cells(currentrow, 5)
    cboIncident_Status.Text = Cells(currentrow, 6)
    cboIncident_Type.Text = Cells(currentrow, 7)
    txtWeek_Commence.Text = Cells(currentrow, 8)
    txtUpdate_Detail.Text = Cells(currentrow, 9)

    End If
    Next currentrow
    txtFname.SetFocus
    End Sub

    Private Sub cmdNext_Click()

    lastrow = Sheets("NLIM_Data").Range("A" & Rows.Count).End(xlUp).Row
    currentrow = currentrow + 1
    If currentrow = lastrow + 1 Then
    MsgBox ("You have reached the last row of data!")
    currentrow = lastrow
    End If

    txtURN.Text = Cells(currentrow, 1).Text
    txtIncident_Title.Text = Cells(currentrow, 2).Text
    txtNLIMDate.Text = Cells(currentrow, 3).Text
    txtUnit_Title.Text = Cells(currentrow, 4).Text
    cboIncident_Severity.Text = Cells(currentrow, 5).Text
    cboIncident_Status.Text = Cells(currentrow, 6).Text
    cboIncident_Type.Text = Cells(currentrow, 7).Text
    txtWeek_Commence.Text = Cells(currentrow, 8).Text
    txtUpdate_Detail.Text = Cells(currentrow, 9).Text

    End Sub

    Private Sub cmdPrevious_Click()

    currentrow = currentrow - 1
    If currentrow > 1 Then

    txtURN.Text = Cells(currentrow, 1).Text
    txtIncident_Title.Text = Cells(currentrow, 2).Text
    txtNLIMDate.Text = Cells(currentrow, 3).Text
    txtUnit_Title.Text = Cells(currentrow, 4).Text
    cboIncident_Severity.Text = Cells(currentrow, 5).Text
    cboIncident_Status.Text = Cells(currentrow, 6).Text
    cboIncident_Type.Text = Cells(currentrow, 7).Text
    txtWeek_Commence.Text = Cells(currentrow, 8).Text
    txtUpdate_Detail.Text = Cells(currentrow, 9).Text

    ElseIf currentrow = 1 Then
    MsgBox "Now you are in the header row!"
    currentrow = currentrow + 1

    End If

    End Sub

    Private Sub cmdQuit_Click()
    Unload New_NLIM
    End Sub

    Private Sub cmdUpdate_Click()
    Dim URN As String
    Dim Incident_Title As String
    Dim NLIMDate As String
    Dim Unit_Title As String
    Dim Incident_Severity As String
    Dim Incident_Status As String
    Dim Incident_Type As String
    Dim Week_Commence As String
    Dim Update_Detail As String

    URN = txtURN.Text
    Cells(currentrow, 1).Value = URN
    Incident_Title = txtIncident_Title.Text
    Cells(currentrow, 2).Value = Incident_Title
    NLIMDate = txtNLIMDate.Text
    Cells(currentrow, 3).Value = NLIMDate
    Unit_Title = txtUnit_Title.Text
    Cells(currentrow, 4).Value = Unit_Title
    Incident_Severity = cboIncident_Severity.Text
    Cells(currentrow, 5).Value = Incident_Severity
    Incident_Status = cboIncident_Status.Text
    Cells(currentrow, 6).Value = Incident_Status
    Incident_Type = cboIncident_Type.Text
    Cells(currentrow, 7).Value = Incident_Type
    Week_Commence = txtWeek_Commence.Text
    Cells(currentrow, 8).Value = Week_Commence
    Update_Detail = txtUpdate_Detail.Text
    Cells(currentrow, 9).Value = Update_Detail


    End Sub

    Private Sub New_NLIM_Initialize()

    currentrow = 2

    txtURN.Text = Cells(currentrow, 1).Text
    txtIncident_Title.Text = Cells(currentrow, 2).Text
    txtNLIMDate.Text = Cells(currentrow, 3).Text
    txtUnit_Title.Text = Cells(currentrow, 4).Text
    cboIncident_Severity.Text = Cells(currentrow, 5).Text
    cboIncident_Status.Text = Cells(currentrow, 6).Text
    cboIncident_Type.Text = Cells(currentrow, 7).Text
    txtWeek_Commence.Text = Cells(currentrow, 8).Text
    txtUpdate_Detail.Text = Cells(currentrow, 9).Text


    End Sub

    Private Sub cmdDelete_Click()
    Dim lastrow
    Dim URN As String, Incident_Title As String, NLIMDate As String, Unit_Title As String
    Dim Incident_Severity As String, Incident_Status As String, Incident_Type As String
    Dim Week_Commence As String, Update_Detail As String

    lastrow = Sheets("NLIM_Data").Range("A" & Rows.Count).End(xlUp).Row
    URN = txtURN.Text
    For currentrow = 2 To lastrow
    If Cells(currentrow, "A").Text = URN Then

    'MsgBox ("Are you sure you want to delete this record??",vbYesNo,-,-,-,-)

    Cells(currentrow, 1).EntireRow.Delete

    End If
    Next currentrow
    txtURN.SetFocus
    End Sub

    Any ideas?

    Steve

    Thursday, December 28, 2017 9:58 PM
  • You have several loops

        For currentrow = 2 To lastrow
            If Cells(currentrow, 1).Text = ... Then
                ...
            End If
        Next currentrow

    You should insert a line

                Exit For

    just above End If, so that currentrow remains set to the row where the value was found.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 28, 2017 10:17 PM
  • Thanks Hans, it didnt seem to work, however; I think that may be because of my code and not your solution.

    It appears that when I populate the Userform by using the Search combobox, change any data and then press the cmdUpdate button it adds the data as a new row at the bottom of the worksheet rather than update the current row.

    Could this be due to how I have coded the Search combobox?

    Steve

    Friday, December 29, 2017 3:37 PM
  • Try declaring an extra variable

    Public ThisRow As Long

    at the top of the module.

    In cboSearch_Change, add the following line just above End If:

                ThisRow = currentrow

    In UpdateClose_Click, use ThisRow instead of currentrow.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Steve MW Friday, December 29, 2017 5:04 PM
    Friday, December 29, 2017 4:27 PM
  • Fantastic, works a treat, thank you so much.

    Steve

    Friday, December 29, 2017 5:04 PM