Adding new record when trying to update thru 'update command button' in userform

[Vivek Roshan]

Hello Everybody,

I have created a USERFORM which has command buttons namely CLEAR, ADD, SEARCH, UPDATE, DELETE, PRINT, CLOSE. All commands are functioning except UPDATE BUTTON. When I am searching a record through combo box, search result is perfect. But after making some changes in the record when I am updating the record through UPDATE COMMAND BUTTON, a new record is being added instead of update on the same record. Code is mentioned below. Any help to resolve this problem will be highly appreciated.

Thanks with regards,
Vivek
------------------------

Private Sub Cmd_Update_Click()


Dim Fvalue As Range
    Set Fvalue = Sheet2.Range("A:A").Find(What:=Me.ComboBox1.Value, LookIn:=xlFormulas, LookAt:=xlWhole)

    If Not Fvalue Is Nothing Then
         Fvalue.Value = Me.TextBox5.Value


        Fvalue.Offset(0, 1).Value = Me.TextBox1.Value
        Fvalue.Offset(0, 2).Value = Me.TextBox2.Value
        Fvalue.Offset(0, 3).Value = Me.TextBox3.Value
        Fvalue.Offset(0, 4).Value = Me.TextBox4.Value
        Fvalue.Offset(0, 5).Value = Me.TextBox6.Value
        Fvalue.Offset(0, 6).Value = Me.ListBox1.Value
        Fvalue.Offset(0, 7).Value = Me.ListBox2.Value


    If OptionButton1.Value Then
        Fvalue.Offset(0, 8).Value = "YES"
    Else
        If OptionButton2.Value Then
          Fvalue.Offset(0, 8).Value = "NO"
        End If
    End If
    If OptionButton3.Value Then
        Fvalue.Offset(0, 8).Value = "PENDING"
    End If
 End If
 
    Fvalue.Offset(0, 9).Value = Me.TextBox7.Value
    Fvalue.Offset(0, 10).Value = Me.TextBox8.Value
    
    Cmd_Clear_Click
 
    MsgBox "Update successfully...!", vbInformation, "Data Updated."
   
End Sub

 

[Dryver14]

try

If Not Fvalue Is Nothing Then
Fvalue.select
Fvalue.Value = Me.TextBox5.Value

 

[Vivek Roshan]

try

If Not Fvalue Is Nothing Then
Fvalue.select
Fvalue.Value = Me.TextBox5.Value

Hi, I tried the above code. It shows the error mesg. as :

Runtime error 1004
Selected method of range class failed

 

[Dryver14]

I think you need to be on the active sheet for this to work, if not maybe you need to select sheet 2 before you run it.

Someone else probably knows netter

 

[Vivek Roshan]

Hi Dryver14,

If I send the code for SEARCH COMMAND and USERFORM INITIALIZE, Will it be helpful for you to find the solution?

Thanks,

 

[Dryver14]

I dont think it will make any difference as you say they are working, the issue is in selecting the fvalue cell

If you are running the code from a different worksheet then you cannot just select fvalue.

also I would change this line

Set Fvalue = Sheets(Sheet2).Range("A:A").Find(What:=Me.ComboBox1.Value, LookIn:=xlFormulas, LookAt:=xlWhole)

 

[Vivek Roshan]

Hi Dryver14,

Here I am mentioning SEARCH COMMAND code which is working perfectly. May be it will help to find solution:

Private Sub CmdSearch_Click()


    Dim Findvalue As Range
    Set Findvalue = Sheet2.Range("A:A").Find(What:=Me.ComboBox1.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not Findvalue Is Nothing Then
        Me.TextBox5.Value = Findvalue.Value
        Me.TextBox1.Value = Findvalue.Offset(0, 1).Value
        Me.TextBox2.Value = Findvalue.Offset(0, 2).Value
        Me.TextBox3.Value = Findvalue.Offset(0, 3).Value
        Me.TextBox4.Value = Findvalue.Offset(0, 4).Value
        Me.TextBox6.Value = Findvalue.Offset(0, 5).Value
        Me.ListBox1.Value = Findvalue.Offset(0, 6).Value
        Me.ListBox2.Value = Findvalue.Offset(0, 7).Value
        If OptionButton1.Value = "YES" Then
            Me.OptionButton1.Value = Findvalue.Offset(0, 8).Value
        Else
            OptionButton2.Value = "NO"
            Me.OptionButton2.Value = Findvalue.Offset(0, 8).Value
        End If
        If OptionButton3.Value = "PENDING" Then
           Me.OptionButton3.Value = Findvalue.Offset(0, 8).Value
        End If
        
        Me.TextBox7.Value = Findvalue.Offset(0, 9).Value
        Me.TextBox8.Value = Findvalue.Offset(0, 10).Value
    End If
    
    Me.ComboBox1.Value = ""
          
End Sub

--------------------
'USERFORM INITIALIZE CODE:

Private Sub UserForm_Initialize()


  Dim lrow As Long
   lrow = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1
   Me.ComboBox1.RowSource = "ICC_Data!A2:A" & lrow
   
End Sub

 

[Vivek Roshan]

Hi, I tried with revised code you sent. It is adding a new record.

 

[Dryver14]

what is combobox1.value?

 

[Vivek Roshan]

Hi Dryver14,

I have solved the problem. It's working fine now. Code is mentioned below for your information.

Dim Fvalue As Range
Set Fvalue = Sheet2.Range("A:A").Find(What:=Me.ComboBox1.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not Fvalue Is Nothing Then
'Fvalue.Select
'Fvalue.Value = Me.TextBox5.Value
Fvalue.Offset(0, 0) = Me.TextBox5.Value

Many thanks for your effort,