Userform - move next and update record

[jshutlz]

Hello,

I've created a userform in order to input data into a database; and am wanting to use the same userform to find records and make changes to those records. I'm creating this listing to help keep track of business contacts; this is something that several people will be using. The form works fine for inputting the original records. And the code below pulls the first record in the database and populates the form (searching by an employee's Name); however, if one employee has several entries and they want to look through them all I don't know how to go to the next record (so it populates the form). Likewise, if they make a change to one of the fields I would like for that change to be caputred in the database.

Any help?

Here's my code for finding a record in the database and populating the userform:

Dim ws As Worksheet
    Dim lRepNum As String
    Dim rngFoundRep As Range
    
    Set ws = Worksheets("Database")
    lRepNum = Me.txtHTName.Value
    
    'Search the report number column using the Range.Find method
    Set rngFoundRep = ws.Range("A:A").Find(what:=lRepNum, lookat:=xlWhole, LookIn:=xlValues)
    
    If rngFoundRep Is Nothing Then
        MsgBox "Report not found!"
    Else
        'Populate form controls
        Me.txtHTName.Value = rngFoundRep.Offset(0, 0).Value
        Me.DTPicker1.Value = rngFoundRep.Offset(0, 1).Value
        Me.txtName.Value = rngFoundRep.Offset(0, 2).Value
        Me.txtTitle.Value = rngFoundRep.Offset(0, 3).Value
        Me.txtPhone.Value = rngFoundRep.Offset(0, 4).Value
        Me.txtFax.Value = rngFoundRep.Offset(0, 5).Value
        Me.txtEmail.Value = rngFoundRep.Offset(0, 6).Value
        Me.txtCompanyName.Value = rngFoundRep.Offset(0, 7).Value
        Me.txtParent.Value = rngFoundRep.Offset(0, 8).Value
        Me.txtAddress.Value = rngFoundRep.Offset(0, 9).Value
        Me.txtCity.Value = rngFoundRep.Offset(0, 10).Value
        Me.txtState.Value = rngFoundRep.Offset(0, 11).Value
        Me.txtWeb.Value = rngFoundRep.Offset(0, 12).Value
        Me.txtIndustry.Value = rngFoundRep.Offset(0, 13).Value
        Me.txtRev.Value = rngFoundRep.Offset(0, 14).Value
        Me.txtEmployee.Value = rngFoundRep.Offset(0, 15).Value
        Me.txtSource.Value = rngFoundRep.Offset(0, 16).Value
        Me.txtSourceName.Value = rngFoundRep.Offset(0, 17).Value
        Me.txtRecentActivity.Value = rngFoundRep.Offset(0, 18).Value
        Me.DTPicker2.Value = rngFoundRep.Offset(0, 19).Value
        Me.txtNextActivity.Value = rngFoundRep.Offset(0, 20).Value
        Me.DTPicker3.Value = rngFoundRep.Offset(0, 21).Value
        Me.txtTarget.Value = rngFoundRep.Offset(0, 22).Value
        Me.txtTargetFee.Value = rngFoundRep.Offset(0, 23).Value
        Me.txtTarget2.Value = rngFoundRep.Offset(0, 24).Value
        Me.txtTargetFee2.Value = rngFoundRep.Offset(0, 25).Value
        Me.txtProvider.Value = rngFoundRep.Offset(0, 26).Value
        Me.txtProb.Value = rngFoundRep.Offset(0, 27).Value
        Me.txtGeneral.Value = rngFoundRep.Offset(0, 28).Value
        
    End If

End Sub

 

[jshutlz]

Any help would be greatly appreciated - I've seached around on google as well as the postings on this site (found a little help, but nothing that works).

This is driving me crazy!

 

[Norie]

Have you considered using a database?

I think it might just make things easier - Excel is a spreadsheet not a database.

All the functionality you seem to want is standard in a database like Access.

 

[jshutlz]

Yes, i've looked into that - the only problem is that all of the people who will be using this do not have a license to Access. Will this type of functionality not work well in excel?

 

[Norie]

It is possible in Excel but it would probably take quite a bit of code and some time.

Personally I wouldn't recommend doing it, like I said a database like Access has this sort of functionality inbuilt.