Userform - move next and update record

jshutlz

New Member
Joined
Mar 10, 2010
Messages
12
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:

Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jshutlz

New Member
Joined
Mar 10, 2010
Messages
12
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 10, 2010
Messages
12
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
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.:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,856
Messages
5,766,786
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top