Returning multiple rows of data from database to userform to edit.

[dellehurley]

Hello MrExcel members
I need help...again. I have figured out how to get the userform to paste the information into several rows. Now I am trying to get the edit function to work, therefore the rows of data need to be added back into the userform. This is the vba for the edit button that I currently have. It only brings up ONE entry.

Private Sub cmbEdit_Click()
'Code to update the value to respective controls
    With Me.lstDatabase
        Me.txtFileNo.Value = .List(.ListIndex, 1)
        Me.cmbType.Value = .List(.ListIndex, 2)
        Me.cmbEvent.Value = .List(.ListIndex, 3)
        Me.cmbExt.Value = .List(.ListIndex, 4)
        Me.txtRIN1.Value = .List(.ListIndex, 5)
        Me.cmbFullName1.Value = .List(.ListIndex, 6)
        Me.txtDate.Value = .List(.ListIndex, 7)
        Me.txtDescription.Value = .List(.ListIndex, 8)
        Me.txtRecs.Value = .List(.ListIndex, 10)
        Me.txt1.Value = .List(.ListIndex, 11)
    End With
    
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
    Me.Tag = "UPDATE"

An individual FileName located in column A is used between 1 to 8 times.

The rows with matching FileName details are the same for each column except for txtRIN1 this is repeats up to txtRIN8, cmbFullName1 this is repeats up to cmbFullName8.
In my attempts to sort this out I added column K & L. Column K contains the number of times a filename repeats (1-8) matches txtRecs, Column L is labelled a,b,c to h. My idea was that txt1 = a, txt2 = b.,
Column J needs to be over written by the code within the save button each time a file is edited.

Any help or advice is appreciated.
File Link

 

[dmt32]

Hi,
try replacing this code

Private Sub lstDatabase_Click()

    Dim rng As range
    Dim rowno As Variant
    Set rng = ThisWorkbook.Sheets("Database").range("A:A")
    With Me.lstDatabase
        rowno = Application.Match(.List(.ListIndex, 0), rng, 0)
    End With
    
        Me.txtRowNumber.Value = IIf(IsError(rowno), 0, rowno)
        Me.cmbEdit.Enabled = Val(Me.txtRowNumber.Value) > 0
        Me.cmbDelete.Enabled = Me.cmbEdit.Enabled
        Set rng = Nothing

End Sub

with this updated version & see if helps you

Private Sub lstDatabase_Click()

    Me.txtRowNumber.Value = Val(Me.lstDatabase.ListIndex + 2)
    Me.cmbEdit.Enabled = Val(Me.txtRowNumber.Value) > 0
    Me.cmbDelete.Enabled = Me.cmbEdit.Enabled
    
End Sub

Dave