Odtnhj

i'm relatively new to VBA and have muddled my way through to almost achieving the system i set out to produce.

I have now reached a brick wall and have been smashing my head against it for some time, help with my paradigm (tome only i'm sure) would be very much appreciated!!

So i am able to search for records and populate my user form from that, what i would like to do is update the found record through the same form when clicking the update button which is present on said user form.

Private Sub btsearch_Click()
Dim totrows As Long
totrows = Worksheets("Report").Range("A1").CurrentRegion.Rows.Count
If Txtforename.Text = "" Then
MsgBox "Please enter guest name!!"
End If

For i = 2 To totrows
If Trim(Report.Cells(i, 1)) <> Trim(Txtforename.Text) And i = totrows Then
MsgBox "Guest Not Found"
End If
If Trim(Report.Cells(i, 1)) = Trim(Txtforename.Text) Then
Txtforename.Text = Report.Cells(i, 1)
Txtsurename.Text = Report.Cells(i, 2)
Cboidtype.Text = Report.Cells(i, 3)
txtidnumber.Text = Report.Cells(i, 4)
Cboroomno.Text = Report.Cells(i, 5)
txtcheckin.Text = Report.Cells(i, 6)
txtcheckout.Text = Report.Cells(i, 7)
Cbopaymenttype.Text = Report.Cells(i, 9)
Txttotalpayment.Text = Report.Cells(i, 10)
cmbouser.Text = Report.Cells(i, 11)
Exit For
End If
Next i

End Sub

Private Sub btnupdate_Click()

answer = MsgBox("Would you like to update guest details?", vbYesNo + 
vbQuestion, "Update Record")
If answer = vbYes Then
Cells(currentrow, 1) = Txtforename.Text
Cells(currentrow, 2) = Txtsurename.Text
Cells(currentrow, 3) = Cboidtype.Text
Cells(currentrow, 4) = txtidnumber.Text
Cells(currentrow, 5) = Cboroomno.Text
Cells(currentrow, 6) = txtcheckin.Text
Cells(currentrow, 7) = txtcheckout.Text
Cells(currentrow, 9) = Cbopaymenttype.Text
Cells(currentrow, 10) = Txttotalpayment.Text
Cells(currentrow, 11) = cmbouser.Text
End If
End Sub

Something like this should work (untested). The matched row is stored in a global variable so you can update it when you're finished editing

Dim CurrentRow As Range 'to store the matched row

Private Sub btsearch_Click()
 Dim totrows As Long, i As Long, fName

 fName = Trim(Txtforename.Text)
 If Len(fName) = 0 Then
 MsgBox "Please enter guest name!!"
 Exit Sub
 End If

 totrows = Report.Range("A1").CurrentRegion.Rows.Count
 Set CurrentRow = Nothing 'clear any previous row

 For i = 2 To totrows
 If Trim(Report.Cells(i, 1)) = fName Then
 Set CurrentRow = Report.Rows(i)
 LoadRow CurrentRow '<< save the matched row
 Exit For
 End If
 Next i

 If CurrentRow Is Nothing Then
 MsgBox "not found!"
 End If

End Sub

Private Sub btnupdate_Click()

 If MsgBox("Would you like to update guest details?", _
 vbYesNo + vbQuestion, "Update Record") = vbYes Then

 SaveRow CurrentRow

 End If
End Sub

'load a row of data into the userform
Sub LoadRow(rw As Range)
 With rw
 Txtforename.Text = .Cells(1).Value
 Txtsurename.Text = .Cells(2).Value
 Cboidtype.Text = .Cells(3).Value
 'etc etc
 End With
End Sub

'save the userform data back to the sheet
Sub SaveRow(rw As Range)
 With rw
 .Cells(1).Value = Txtforename.Text
 .Cells(2).Value = Txtsurename.Text
 .Cells(3).Value = Cboidtype.Text
 'etc etc
 End With
End Sub

First thing I would check is the result value of the MsgBox. MsgBox always returns an integer, and answer is a variant.

Secondly I would declare answer as an integer. Together it could become this :

. . .
 Dim answer As Integer
 answer = Msgbox("Would you ... ecord")
 Debug.Print "' answer := " & answer ' display result in immediate-window
 MsgBox "' answer := " & answer ' or display the result with a msgbox
 If answer = vbYes Then
 Debug.Print "' then"
 ' while you are busy debugging, display this value too
 Debug.Print "' currentrow := " & currentrow
 . . . 
 Else
 Debug.Print "' else"
 End If
. . .