Hello Everyone,

Basically I have learnt vba from scratch by piecing various things together and somehow I have managed to create what I need.

I have a spreadsheet which is populated by a userform. I have the next, previous, first and last record buttons so i can scroll through existing records.

However, I cannot find how to do the following and it is becoming very frustrating:

1. I need to be able to find a record by putting search terms in a text box
2. When I find the record, I need to be able to update it, delete some things if need be and then save the overwritten record on the same row using an update/amend command button. At present I can only save any overwritten records to a new line which is not what I want.

I have scanned the forum but cannot find a solution that does not add an additional row

Unfortunately because of restrictions on my (work) computer I am unable to attach a file. However I am very good at following examples so if someone has something similar that I can use then I would be very grateful. I can give a sanitized example of some of the code I am using though.

Your help would be much appreciated

The following is the code I use to populate the spreadsheet and add another record

Private Sub saveandadd_Click()

    Dim Nextrow As Long
       
    Sheets("XSeries").Activate
    
    Nextrow = Application.WorksheetFunction.CountA(Range("C:C")) + 2
            
    Cells(Nextrow, 2) = Initials.Text
      
    Cells(Nextrow, 3) = CDate(EntryDate.Value)

    Cells(Nextrow, 4) = Description.Text

    Cells(Nextrow, 5) = CDate(Firstgame.Value)

    Cells(Nextrow, 6) = CDate(Lastgame.Value)

    Cells(Nextrow, 7) = Noofgames.Text

    Cells(Nextrow, 83) = OtherProperty.Text

    Call UserForm_Initialize

End Sub
The following code occurs when the form is intialized

Private Sub UserForm_Initialize()
  
'   Fullscreen the userform       
     Call fullscreen_Click
     
     Dim Nextrow As Long
     Dim lastrow As Long

'   Activate the correct sheet  
    Sheets("XSeries").Activate

    Nextrow = Application.WorksheetFunction.CountA(Range("C:C")) + 2
      
'   Obtain pre-assigned reference
    txtRef.Text = Cells(Nextrow, 1)
    
    lastrow = Sheets("XSeries").Cells(Rows.Count, "C").End(xlUp).row
    Me.txt_RecNo.Text = lastrow
    Me.txt_RecNo.Tag = lastrow
    
'   Reset the fields
   Description.Value = " "
   EntryDate.Value = "01/01/2001"
    Firstgame.Value = "01/01/2001"
    Lastgame.Value = "01/01/2001"
    Noofgames.Value = "3"
    OtherProperty.Value = " "
End Sub
The following is the code I use to move along records

Private Sub cmd_goFirstRec_Click()
   Call retrieveRec(1)
End Sub

Private Sub cmd_goLastRec_Click()
   Dim lastRec As Long
   
   lastRec = Sheets("XSeries").Cells(Rows.Count, "B").End(xlUp).row - 1
   Call retrieveRec(lastRec)
End Sub

Private Sub cmd_goNextREc_Click()
   Dim currRec As Long
   Dim lastRec As Long
   
   lastRec = Sheets("XSeries").Cells(Rows.Count, "B").End(xlUp).row
   currRec = Val(Me.txt_RecNo)
   
   If currRec < lastRec Then
      currRec = currRec + 1
      Call retrieveRec(currRec)
   End If

End Sub

Private Sub cmd_goPrevRec_Click()
   Dim currRec As Long
   currRec = Val(Me.txt_RecNo)
   If currRec > 1 Then
      currRec = currRec - 1
      Call retrieveRec(currRec)
   End If
End Sub


Private Sub txt_RecNo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   Dim lastRec As Long
   Dim currRec As String
   
   If KeyCode = 13 Then
      lastRec = Sheets("XSeries").Cells(Rows.Count, "B").End(xlUp).row
      currRec = Val(Me.txt_RecNo.Value)
      If currRec <> 0 And currRec <= lastRec Then
         Call retrieveRec(currRec)
      Else
         Me.txt_RecNo.Text = Me.txt_RecNo.Tag
      End If
   End If

End Sub
Sub retrieveRec(recNo)
    With Sheets("XSeries")
txtRef.Text = Cells(recNo + 2, 1)
Initials.Text = Cells(recNo + 2, 2)
EntryDate.Value = Cells(recNo + 2, 3)
Description.Text = Cells(recNo + 2, 4)
Firstgame.Value = Cells(recNo + 2, 5)
Lastgame.Value = Cells(recNo + 2, 6)
Noofgames.Text = Cells(recNo + 2, 7)
OtherProperty.Text = Cells(recNo + 2, 83)
txtRef.SetFocus

txtRef.SetFocus
     End With
    
    Me.txt_RecNo.Text = recNo
    Me.txt_RecNo.Tag = recNo
End Sub
I hope this all helps