How to edit data through VBA?

[Hakim Ullah]

I have created the Interface. I have entered the 'Edit Record' button in the 'Interface' worksheet. I want use this to edit the data in the sheet2"Details". I have put the codes. But when I give the registration number in the 'editstudent1' textbox, the data in the other textboxes doesn't load and doesn't edit the data in the worksheet when i fill all the textboxes. Please if someone help me. The user form I have inserted for this is named as"frmeditstudent".

 

[Smallman]

Hi Hakim

It is quite refreshing to see a form laid out well. You have thought about the order of your text boxes in line with the data in your database. Well done. I don’t often see this clear logic.

I have changed a bit in your form. You appear to be wanting the Edit procedure to fire after EditStudent1 has been changed. If you hit the edit button type 125 and hit ENTER.
You will see the form now auto populates. Here is the code.

Private Sub editstudent1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Findit
    End If
End Sub
 
 
Private Sub Findit() 'Find and populate the records
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
 
Set sh = Sheet2
Search = editstudent1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)
 
    If fnd Is Nothing Then
        MsgBox "No Student Found", , "Error"
        editstudent1.Text = Null
    Else
        For i = 2 To 13
            frmeditrecord.Controls("editstudent" & i).Text = sh.Cells(fnd.Row, i).Value
        Next i
    End If
End Sub

Now if you change something in the form and hit UPDATE. You will see this change reflected in the Details tab.

I think this is what you were looking for.

I would use the changes I have made to your coding and update the rest of your procedures to reflect this. I only updated the EDIT form.

File attached to show workings.

Take care

Smallman

 

[Hakim Ullah]

Hi Hakim

It is quite refreshing to see a form laid out well. You have thought about the order of your text boxes in line with the data in your database. Well done. I don’t often see this clear logic.

I have changed a bit in your form. You appear to be wanting the Edit procedure to fire after EditStudent1 has been changed. If you hit the edit button type 125 and hit ENTER.
You will see the form now auto populates. Here is the code.

Private Sub editstudent1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Findit
    End If
End Sub


Private Sub Findit() 'Find and populate the records
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer

Set sh = Sheet2
Search = editstudent1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)

    If fnd Is Nothing Then
        MsgBox "No Student Found", , "Error"
        editstudent1.Text = Null
    Else
        For i = 2 To 13
            frmeditrecord.Controls("editstudent" & i).Text = sh.Cells(fnd.Row, i).Value
        Next i
    End If
End Sub

Now if you change something in the form and hit UPDATE. You will see this change reflected in the Details tab.

I think this is what you were looking for.

I would use the changes I have made to your coding and update the rest of your procedures to reflect this. I only updated the EDIT form.

File attached to show workings.

Take care

Smallman

Thank you so much Mr.Smallman. Surely this was what I was looking for. That works very well. Since, I am new to this blog, just joined yesterday. This is really very interesting and of course very helpful. I really love this. And thanks to the owner of this blog who created 'Chandoo.org' to help others. I will be a regular user of this INSHAA'ALLAH!

 

[Smallman]

Hi Hakim

You are welcome and good luck with the rest of your project.

Take care

Smallman