Using filter to populate form

[wpryan]

I am developing a user form for data entry, the output of which will go into an Access database. Users complete the form, which is then sent to the worksheet formatted for the input into Access. However, on observing how people want to use the form, is that they want to be able to send some information to the worksheet, and then be able to call the information back into the form for completion of the data entry. The usage is for data analysis of a medical treatment. We have a patient ID, various settings of a medical device, and observations about the treatment. Many times there will be a list of 10 patients or so, and so the user of the form will want to put as much data as possible into the worksheet prior to the treatments. Then they can populate the form again with the data that was entered already, make any corrections and send back to the form. The data, when entered into the worksheet, is going into a single row, so I was thinking that the "simplest way" would be to use a filter to find the row, copy the data into the form, delete the row (to prevent duplicate data) and then send again using the current code.
I hope I explained my situation clearly enough and there is a relatively simple solution...

 

[wpryan]

OK so I got it mostly working with this code:

Private Sub cmdOK_Click()
 Dim SearchRange As Range    Dim TargetRow As Range
    Set SearchRange = Range("I3", Range("A65536").End(xlUp))
    Set TargetRow = SearchRange.Find(Me.cboPtID.Value, LookIn:=xlValues, lookat:=xlWhole)
    
    UserForm1.Show
    'MsgBox TargetRow.Row


        'Clinic data
        UserForm1.txtClinic = TargetRow.Cells(0, -6).Value
        UserForm1.txtSurgeon = TargetRow.Cells(, -5).Value
        ...blablabla

Now the last step is to delete the row with the data which has been copied. The message box above properly displays the row which contains the data I want to delete, but depending on the syntax I use, I either get invalid qualifiers, range does not support etc...